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: <[EMAIL PROTECTED]>
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" <[EMAIL PROTECTED]>
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: <[EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
