Re: Free Shared pool memory

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
> <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: Jonathan Lewis
> INET: jonathan_at_jlcomp.demon.co.uk
>
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: tim_at_sagelogix.com

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).