Re: High shared pool usage
From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Thu, 29 Sep 2011 21:53:21 -0700
Message-ID: <CAMHX9J+HadTn-iNY_jBVuVNhTsVZ45TTmQV=e-TYRZhqn6ZwVg_at_mail.gmail.com>
You can have large KGH NO ACCESS allocations also when you have an one-time spike in shared pool utilization (common in 11g with library cache related issues where lots of child cursors are loaded for one statement or just with occasionally running hard parsing loops in application code). So, the shared pool usage might grow really high and this is not related to ASMM really, it's more related to the high demand. If you thoroughly read that note you quoted you'll actually see that Oracle guys also say this: *"**The exception is when the database needs to make large changes i.e. when changing memory after a heavy load".*
So, when you see KGH NO ACCESS in gigabytes, it does not automatically mean you're hitting these shared pool bugs (which can be the case though), but it may also mean that you just had abnormally high demand for shared pool due to bad application code or some other bug higher in the stack (like library cache cursor sharing code).
Date: Thu, 29 Sep 2011 21:53:21 -0700
Message-ID: <CAMHX9J+HadTn-iNY_jBVuVNhTsVZ45TTmQV=e-TYRZhqn6ZwVg_at_mail.gmail.com>
You can have large KGH NO ACCESS allocations also when you have an one-time spike in shared pool utilization (common in 11g with library cache related issues where lots of child cursors are loaded for one statement or just with occasionally running hard parsing loops in application code). So, the shared pool usage might grow really high and this is not related to ASMM really, it's more related to the high demand. If you thoroughly read that note you quoted you'll actually see that Oracle guys also say this: *"**The exception is when the database needs to make large changes i.e. when changing memory after a heavy load".*
So, when you see KGH NO ACCESS in gigabytes, it does not automatically mean you're hitting these shared pool bugs (which can be the case though), but it may also mean that you just had abnormally high demand for shared pool due to bad application code or some other bug higher in the stack (like library cache cursor sharing code).
This memory in KGH NO ACCESS is not "dead, unusable" - it's used by buffer cache. I have posted an example in my blog entry about how to measure it yourself (by mapping x$bh.ba with x$ksmsp.ksmchadr). The "composite granule" code isn't probably able to reclaim that memory back from buffer cache use to shared pool - or doesn't realize that it would be good to shrink the buffer cache...
-- Tanel Poder http://blog.tanelpoder.com On Tue, Sep 27, 2011 at 10:52 PM, Marcus Mönnig <mm_at_marcusmoennig.de> wrote:Received on Thu Sep 29 2011 - 23:53:21 CDT
> 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;
>
>
-- http://www.freelists.org/webpage/oracle-l