Re: High shared pool usage

  • From: Tanel Poder <tanel@xxxxxxxxxxxxxx>
  • To: mm@xxxxxxxxxxxxxxxx
  • Date: Thu, 29 Sep 2011 21:53:21 -0700

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$ 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

On Tue, Sep 27, 2011 at 10:52 PM, Marcus Mönnig <mm@xxxxxxxxxxxxxxxx> wrote:

> 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 " 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",
> from v$sga_resize_ops
> where component in ('DEFAULT buffer cache', 'shared pool') and status
> order by start_time, component;


Other related posts: