RE: v$sgastat Shared Pool records and init.ora parameters

Hi Patrice,

Have you seen my "Oracle Internal Services"? Here is what it says (p83f) ...

"The size of the variable area of the SGA is equal to the large_pool_size, plus the shared_pool_size, plus the size of the permanent memory arrays. The total size of the permanent memory arrays can, in theory, be calculated from the settings of the initialization parameters. However, you need to know the formulae used to derive the array sizes from the parameters, the size of each type of array element in bytes, and the sizes of the array headers where applicable. These all change from release to release, and there are also operating system dependent differences. You also need to be aware that each permanent memory array is aligned on a memory page boundary to optimize memory addressing, and so some space is left unused."

Thus those authors are right; it is just rather difficult to do in practice. There is a fair bit more information on the internals of the shared pool in my book, but not much on the library cache or dictionary cache. The acronym DIANA is explained on my web site. Use the search box.

Regards,
Steve Adams

http://www.ixora.com.au/
http://www.oreilly.com/catalog/orinternals/
http://www.christianity.net.au/


-----Original Message-----
From:	Boivin, Patrice J [SMTP:BoivinP_at_mar.dfo-mpo.gc.ca]
Sent:	Thursday, June 01, 2000 6:04 AM
To:	Multiple recipients of list ORACLE-L
Subject:	v$sgastat Shared Pool records and init.ora parameters

Has anyone been able to match entries in v$sgastat with the values of their init.ora parameters?

Niemec, Rickard J. et al., Oracle Performance Tuning Tips & Techniques: Proven Solutions from the Experts at TUSC, p. 569 says that the 33 or 36 Shared Pool records in v$sgastat make up the Variable Size entry in v$sga.

Alomari, Ahmed, Oracle8 and UNIX High-Performance Tuning, p.196 says that the variable size in v$sga is mostly determined by the init.ora parameters.

Just being curious as usual. <grin>

I still feel I don't understand exactly what happens inside the Shared Pool,
how its size is determined (there always seems to be a lot of free space in there, and the hit ratios vary wildly for each type of object), and what the
different components of it are (e.g. what is the PL/SQL DIANA?! It's not a reference to the Princess of Wales, I 'm sure. There may be some info on this buried in the /rdbms/admin scripts somewhere, I remember DIANA being mentioned in a comment in one of the scripts. Could it be a subcomponent of
the library cache?).

Some of them are pretty obvious, like
enqueue_resources
Library cache
sql area
Processes
db_block_hash_buckets

If anyone can point me toward a white paper or a book that would link the impact of the various init.ora parameters to the Shared Pool structures, that would be wonderful.

TIA
Patrice Boivin
Systems Analyst (Oracle Certified DBA)

Systems Admin & Operations | Admin. et Exploit. des systemes
Technology Services        | Services technologiques
Informatics Branch         | Direction de l'informatique
Maritimes Region, DFO      | Region des Maritimes, MPO

E-Mail: boivinp_at_mar.dfo-mpo.gc.ca <mailto:boivinp_at_mar.dfo-mpo.gc.ca>

--
Author: Boivin, Patrice J
  INET: BoivinP_at_mar.dfo-mpo.gc.ca

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may