Re: High shared pool usage
From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Wed, 28 Sep 2011 01:30:32 +0300
Message-ID: <CAMHX9JLQ6SYA3b_WO4HbRPNayGJD9XG35ONdVS2Vb+kyc--dEw_at_mail.gmail.com>
(overquoted, resending)
If you google for KGH NO ACCESS, you'll see that it's just how ASMM works :)
Date: Wed, 28 Sep 2011 01:30:32 +0300
Message-ID: <CAMHX9JLQ6SYA3b_WO4HbRPNayGJD9XG35ONdVS2Vb+kyc--dEw_at_mail.gmail.com>
(overquoted, resending)
If you google for KGH NO ACCESS, you'll see that it's just how ASMM works :)
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:Received on Tue Sep 27 2011 - 17:30:32 CDT
> 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