Re: Free Shared pool memory

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 02 Jan 2003 07:34:08 -0800
Message-ID: <F001.00525763.20030102073408@fatcity.com>

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

Received on Thu Jan 02 2003 - 09:34:08 CST