Re: Free Shared pool memory

I'm probably telling you guys something you already know, but at least it gives me a chance to write something on the list...

Notice that the show sga command output doesn't contain a line saying "shared pool size", but instead says "variable size". That's because it's the size of the variable part of the SGA, which includes the row/dictionary cache, the library cache (usually together referred to as the shared pool) plus a number of other structures including the hash buckets and the buffer header structures of the buffer cache, the lru/lruw/whatever structures and more. Therefor Variable Size will always be bigger than shared pool size.

I hope this answered somebody's question somewhere ;-)

Mogens

Jonathan Lewis wrote:

>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 <ORACLE-L_at_fatcity.com>
>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: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
  INET: mln_at_miracleas.dk

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: 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
also send the HELP command for other information (like subscribing).