RE: High shared pool usage

From: D'Hooge Freek <Freek.DHooge_at_uptime.be>
Date: Wed, 28 Sep 2011 00:15:09 +0200
Message-ID: <4814386347E41145AAE79139EAA3989817A8BC074E_at_ws03-exch07.iconos.be>

Hi,

I would say your "KGH: NO ACCESS" is excessivily large. This component refers to granules that are in transit (being reassigned from the shared pool to the buffer cache and vice-versa)

There are some bugs know to this. Check following MOS notes:

How To Prevent The Growth Of The Component 'KGH: NO ACCESS' In The Shared Pool When ASMM Is Enabled [ID 451960.1] Common Cause for ORA-4031 in 10gR2, Excess "KGH: NO ACCESS" Memory Allocation [Video] [ID 801787.1]

Also you can check if the sga components are frequently resizing:

set linesize 140
set pages 9999

column initial_mb format 9G999D99
column target_mb format 9G999D99
column final_mb format 9G999D99
column component format a30

select *
from ( select to_char(start_time, 'DD/MM/YYYY HH24:MI:SS') start_time, to_char(end_time, 'DD/MM/YYYY HH24:MI:SS') end_time,

               component, oper_type, oper_mode, initial_size/1024/1024 initial_mb, target_size/1024/1024 target_mb, 
               final_size/1024/1024 final_mb, status
        from v$sga_resize_ops a
        order by a.start_time desc
      )

where rownum <= 40;

regards,  

Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ram Raman Sent: dinsdag 27 september 2011 23:55
To: mm_at_marcusmoennig.de
Cc: ORACLE-L
Subject: Re: High shared pool usage

Thanks Marcus:

POOL         NAME                                                    BYTES


------------ ---------------------------------------- --------------------

shared pool  KGH: NO ACCESS                                  5,131,392,192
shared pool  sql area                                        4,664,912,584
shared pool  free memory                                     4,503,302,200
shared pool  CCursor                                           913,006,592
shared pool  PCursor                                           717,546,600
shared pool  library cache                                     531,817,048
shared pool  gcs resources                                     256,661,792
shared pool  kglsim object batch                               151,151,952

Not very many child cursors:

SQL_ID CHILDCOUNT
------------- ----------

5js8dhamyt3cw          6
96g93hntrzjtr          6
6wh0q2tfwvssz          6
cqv52axncuvzv          6
fuscam04hhdqr          5
grwydz59pu6mc          5
7ng34ruy5awxq          5
260w39duxtz9u          5
15zytu14qzw6p          5
83taa7kaw59c1          5
2q93zsrvbdw48          5
011bkqb0xaxpp          5
at3ryvuft2f40          5
db78fxqxwxt7r          5
4yvsj6a508pgf          4
09vwa8mu0zfqa          4

...

There are about 1300 cursors for which the child count is greater than 1.

--

http://www.freelists.org/webpage/oracle-l Received on Tue Sep 27 2011 - 17:15:09 CDT