The SHARED_POOL_RESERVED_SIZE is indeed defaulting to 15728640, which is 5% exactly of SHARED_POOL_SIZE...
The reserved area is subtracted from the Shared Pool, so subtracting that amount from the difference still leaves 765,848 bytes. As a number, that doesn't divide by any of the powers-of-2 (i.e. 1024, 512, 256, etc) until you get down to 8, 4, and 2, so it kind of bugs me... Another possible explanation is that SHARED_POOL_SIZE is not the actual size of the Shared Pool, but rather a starting point to which Oracle adds extra space for some reason? The "unlatched data structure" explanation might be a cop-out, but I get suspicious when I see a statistic named "miscellaneous", which in itself is a cop-out for a database engine. Miscellaneous? You've got to be kidding! Life is miscellaneous when you step back... :-) > > I hadn't heard the historic explanation before, > so I'll pass on that. > > As far as the 16MB is concerned - I believe > the free memory includes any free space > left in the shared_pool_reserved_size. > > Since the shared_pool_reserved_size defaults > to 5% of the shared_pool_size (I think) it isn't > necessarily a surprise that you have 16MB > of free memory when your shared_pool size if > 320MB. (On the other hand, is the reserved > size supposed to be extracted from the main > pool, or additional too the main pool) > > The latching thing is always good for a cop-out. > I suspect that v$sgastat would become a major > bottle neck if it were always latched and updated > in real time. So it seems very likely that it would > always be wrong. > > > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Coming soon a new one-day tutorial: > Cost Based Optimisation > (see http://www.jlcomp.demon.co.uk/tutorial.html ) > > Next Seminar dates: > (see http://www.jlcomp.demon.co.uk/seminar.html ) > > ____England______January 21/23 > > > The Co-operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > > > > -----Original Message----- > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> Date: 02 January 2003 15:13 > > > Sorry for being so vague, but sometimes I can't help it... > > It was my understanding in the Oracle7 days that the name > of the statistic "free memory" was actually a verb and a > noun (i.e. as in "free Nelson Mandela" or "free Willy"), > and the number shown alongside this statistic was the > cumulative number of bytes freed in the Shared Pool. In > other words, every time "N" bytes were freed from the > Shared Pool, then the statistic was incremented by "N". > At least, this explanation would have accounted for the > absurdly huge numbers seen in the V$SGASTAT view for this > statistic in those versions and the unreliability in > attempting to add the numbers seen in V$SGASTAT to sum to > SHARED_POOL_SIZE... > Then, sometime in the Oracle8 or Oracle8i timeframe, the > meaning of the statistic was changed so that the term > "free memory" became what everyone had thought it was, an > adjective and a noun (i.e. as in "free beer" or "free > time"). A much more useful statistic, certainly... > Is this true? If not, is it close? > > The sum of the information in V$SGASTAT still does not add > to SHARED_POOL_SIZE, though (query from v8.1.7.4.0 shown > below): > SQL> select name, bytes from v$sgastat > 2 where pool = 'shared pool'; > > NAME BYTES > -------------------------- ---------- > free memory 18208352 > miscellaneous 2378964 > DML locks 120000 > PLS non-lib hp 2096 > trigger inform 944 > PL/SQL MPCODE 1146204 > PL/SQL DIANA 1223360 > PX subheap 123476 > db_block_hash_buckets 1411080 > sessions 377300 > KGK heap 48124 > State objects 267420 > message pool freequeue 124552 > Checkpoint queue 885168 > enqueue_resources 222912 > db_files 370988 > KGFF heap 649844 > KQLS heap 1709904 > dictionary cache 12670280 > table definiti 3228 > transactions 171264 > ksfv subheap 4248 > fixed allocation callback 1280 > library cache 89490788 > simulator trace entries 240000 > sql area 187432036 > table columns 19520 > processes 123380 > partitioning d 152976 > db_block_buffers 10880000 > event statistics per sess 607600 > ---------- > sum 331067288 > > SQL> show parameter shared_pool_size > > NAME TYPE VALUE > ------------------- ------- --------- > shared_pool_size string 314572800 > > I'm curious about the 16,494,488 bytes difference. Is it > possible that V$SGASTAT is another "unlatched" data > structure in memory, allowing errors in the interest of > eliminating contention? There are other similar > structures in the SGA (i.e. the data structure underlying > table MONITORING statistics later flushed to > SYS.TABMOD$)... > Thanks for any and all insight! > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net -- > Author: Jonathan Lewis > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com San Diego, California -- > Mailing list and web hosting services > ---------------------------------------------------------- > ----------- To REMOVE yourself from this mailing list, > send an E-Mail message to: [EMAIL PROTECTED] (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 also > send the HELP command for other information (like > subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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 also send the HELP command for other information (like subscribing).
