Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Free Shared pool memory

Re: Free Shared pool memory

From: Tim Gorman <Tim_at_SageLogix.com>
Date: Sat, 04 Jan 2003 09:03:41 -0800
Message-ID: <F001.005271E6.20030104090341@fatcity.com>


RE: Free Shared pool memoryNope! Like many questions to the list, I hadn't checked metalink first. Thanks!

  Tim,

  I am pretty sure you are aware of Metalink note 100666.1 where it says that the free_memory value is unreliable if shared_pool_reserved_size is a non-zero value. Bug# 370903 .

  BTW This is what it is on 9202 ...

  oraclei_at_elara-NCS1> sys

  SQL*Plus: Release 9.2.0.2.0 - Production on Thu Jan 2 11:22:09 2003

  Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

  Connected.
  SQL> show sga

  Total System Global Area 1244448032 bytes

  Fixed Size                   742688 bytes 
  Variable Size             973078528 bytes 
  Database Buffers          268435456 bytes 
  Redo Buffers                2191360 bytes 
  SQL> SELECT SUM(bytes)/1024/1024 FROM v$sgastat WHERE pool = 'shared pool';

  SUM(BYTES)/1024/1024


                   848 

  SQL> SELECT SUM(bytes) FROM v$sgastat WHERE pool = 'shared pool' ;

  SUM(BYTES)



   889192448

  SQL> show parameter shared

  NAME                                 TYPE        VALUE 
  ------------------------------------ ----------- ------------------------------ 
  hi_shared_memory_address             integer     0 
  max_shared_servers                   integer     20 
  shared_memory_address                integer     0 
  shared_pool_reserved_size            big integer 104857600 
  shared_pool_size                     big integer 771751936 
  shared_server_sessions               integer     0 
  shared_servers                       integer     0 
  SQL> exit
  Disconnected from Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production   With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options   JServer Release 9.2.0.2.0 - Production

  Rajendra Jamadagni MIS, ESPN Inc.   Rajendra dot Jamadagni at ESPN dot com   Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.   QOTD: Any clod can have facts, but having an opinion is an art!

  -----Original Message-----
  From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demonco.uk]   Sent: Thursday, January 02, 2003 10:34 AM   To: Multiple recipients of list ORACLE-L   Subject: Re: Free Shared pool memory

  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: Tim Gorman
  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).
Received on Sat Jan 04 2003 - 11:03:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US