Re: High shared pool usage
- From: Marcus Mönnig <mm@xxxxxxxxxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 28 Sep 2011 07:52:10 +0200
Hi Tanel and all!
> 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@xxxxxxxxx>
> 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:
>>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
