Re: High shared pool usage

> If you google for KGH NO ACCESS, you'll see that it's just how ASMM works :)

True, but if you search in Metalink you'll see that this is not how ASMM should work. ;-)

ML Note 801787.1 states that "...it is normal to periodically see "KGH: NO ACCESS" allocations up to about 64M". I personally set up Nagios checks for "KGH: NO ACCESS" sizes at 128 MB.

Oracle considers anything above 64M a bug, due to too frequent resize operations between the buffer cache and the shared pool. To verify this, they give this query:

select START_TIME, component, oper_type, oper_mode, initial_size/1024/1024 "INITIAL", FINAL_SIZE/1024/1024 "FINAL", END_TIME
from v$sga_resize_ops
where component in ('DEFAULT buffer cache', 'shared pool') and status = 'COMPLETE'
order by start_time, component;

My personal feeling and believe is that the rising allocations for this pool comes from dead, unusuable memory in there. I have seen this on 32 bit system where KGH NA was getting higher and higher over time and we ultimatley ran into ORA-04031 errors. I also have a 64 bit machine at hands with SGA_TARGET set to 5GB where buffer cache is around 2 GB and KGH NA is at 1.5GB and V$DB_CACHE_ADVICE states that the instance could use another 1 GB of memory. This is after three month uptime, but V$DB_CACHE_ADVICE only asks for more memory after KGH NA gots higher and higher.

For me, this is a memory leak, but I can't proove it. Unfortunetly I miss the skills to dump the shared pool and inspect the bits one by one. :-)

All this is on 10.2. While there are similar bugs availabel in ML for 11.*, I've never seen them personally.

Cheers,
Marcus

> http://blog.tanelpoder.com/2009/09/09/kgh-no-access-allocations-in-vsgastat-buffer-cache-within-shared-pool/
> It's the mixed granules where buffer cache is placed within shared pool
> (yes, I just did say that!).
> So, that 5 GB is buffer cache. Your shared pool has been big as probably
> during some time there was more shared pool loading activity than physical
> IO activity and the ASMM manager decided to increase the shared pool. Once
> more buffer cache was needed, it could not de-allocate complete granules
> anymore and just made them "composite granules"
>
> --
> Tanel Poder
> Expert Oracle Exadata book:
> http://www.apress.com/9781430233923
>
> On Wed, Sep 28, 2011 at 1:15 AM, D'Hooge Freek <Freek.DHooge_at_uptime.be>
> wrote:
>>
>> 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:
>>
>

--
http://www.freelists.org/webpage/oracle-l