Re: shared_Pool_size

Arup ,
wow I am so glad that you answered . You always come up with the real mccoy answers. However I have a few questions stemming from what you said:

1. I tried running select * from v$sgastat where pool = 'shared pool' order by 3; on my 9.2.0.1.0 enterprise edition. I was unable to get any row containing name= 'db_block_buffers' and pool='shared pool' Which version did you run this on ?
2. If I allocate x MB to my buffer cache, does this mean that Oracle allocates that x mb for the buffer cache , and an additional x mb for managing the buffer cache and places it in the shared pool ?

I did select sum(bytes) from v$sgastat where pool='shared pool;
>From this I subtracted the value of shared_pool_size. The difference is
exactly equal to my db_cache_size.

3. What is the size of your buffer cache ?

I wish Oracle had clearly defined all its memory needs in big big bold letters for novices like me.

• Original Message ----- To: <bulbultyagi_at_now-india.net.in> Sent: Friday, October 03, 2003 9:16 PM

Bulbul,

I apologize for getting back to you late on this issue. As you can probably tell, I hardly find time to look at the Oracle-L messages.

Anyway, your question is valid; actually it's an aberration of understanding the pool management in Oracle. When you define the shared_pool_size parameter in initialization parameter, that is not really the *whole* share pool, but only for part of it. The shared pool contains several other types of objects, such as PL/SQL Dianas, etc. Here is the output for my case.

SQL> show parameter shared_pool_size

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
shared_pool_size                     string  36000000

SQL> select sum(bytes) from v$sgastat where pool = 'shared pool';

      SUM(BYTES)

      81,344,064

As you can see, the defined shared pool is only 36 MB whereas the actual allocated shared pool is about 80 MB. Where does the extra 44 MB come from? To find out we have to break down the shared pool.

SQL> l
  1 select * from v$sgastat where pool = 'shared pool' order by 3;

POOL        NAME                                  BYTES
----------- -------------------------- ----------------
shared pool trigger inform                          408
shared pool fixed allocation callback             1,904
shared pool PLS non-lib hp                        2,136
shared pool trigger source                        2,280
shared pool table definiti                        3,664
shared pool temporary tabl                        6,040
shared pool trigger defini                        8,312
shared pool KGK heap                              9,944
shared pool table columns                        19,752
shared pool KGFF heap                            43,728
shared pool log_buffer                           98,304
shared pool SYSTEM PARAMETERS                   107,920
shared pool long op statistics array            124,000
shared pool enqueue_resources                   151,008
shared pool network connections                 158,096
shared pool ktlbk state objects                 165,088
shared pool message pool freequeue              191,192
shared pool DML locks                           206,976
shared pool db_handles                          220,000
shared pool processes                           268,000
shared pool PL/SQL DIANA                        413,072
shared pool transactions                        468,160
shared pool State objects                       539,680
shared pool sessions                            680,960
shared pool KQLS heap                           829,752
shared pool event statistics per sess           972,160
shared pool PL/SQL MPCODE                     1,080,168
shared pool dictionary cache                  1,533,464
shared pool miscellaneous                     1,996,984
shared pool db_block_hash_buckets             5,253,744
shared pool sql area                          8,864,600
shared pool library cache                    10,415,712
shared pool free memory                      11,150,056
shared pool db_block_buffers                 35,356,800

Note the last value, db_block_buffers: 35,356,800. Wait! shouldn't the db block buffers be in db_block_buffers? What are they doing in shared pool?

Even though the actual buffers are defined by the parameter db_block_buffers, and the bufferes are created there, the actual management of the buffers, i.e. which one is free, which one is at which end of the Least Recently Used (LRU) list, etc. are maintained in the shared pool. Theefore the 35 MB area you see is allocated to the management of the db block buffers, outside the 36 MB I have defined.

The 36 MB I defined goes into the sql area (8 MB), library cache (10 MB), db block hash buckets (5 MB) and dictionary cache (1 MB) and about 11 MB is free = 36 MB. The rest are all outside the defined shared pool. Hence you see a different number.

Hope this helps.

Arup Nanda
www.proligence.com

• Original Message ----- To: "Arup Nanda" <orarup_at_hotmail.com> Sent: Friday, October 03, 2003 5:42 AM

> Arup, sorry to trouble you but I was unable to get an answer about the
> following. Could you please help me ?
>
> will the following two queries give the same value for the
> shared_Pool_size ?
> select sum ( bytes) / (1024*1024) from v$sgastat where pool = 'shared
pool';
>
>
> and
>
>
> show parameter shared_pool_size
>
>
> I always get a difference , the first one gives a value greater than the
> second by 12MB
> I tried with different values of shared_pool_size in Oracle 9.2.0.1.0
> enterprise edition on windows.
> What am I doing wrong here ?
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <bulbultyagi_at_now-india.net.in
  INET: bulbultyagi_at_now-india.net.in

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