KGH: NO ACCESS allocations in V$SGASTAT – buffer cache within shared pool!

Since Oracle 10.2 it’s valid to say that buffer cache can be stored inside shared pool.

Now you may think I’m crazy, but read until the end of the post – no matter how crazy I may sound – I have proof!

Here it is:

Few years ago I started noticing a strange memory allocation in shared pool (in V$SGASTAT), called KGH: NO ACCESS.

SQL> select * from v$sgastat where name = 'KGH: NO ACCESS';
POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  KGH: NO ACCESS               10513696

SQL>

You see, some 10 MB of memory in shared pool has been allocated for something called KGH: NO ACCESS.

Ok, lets see where this memory resides inside shared pool. We can use x$ksmsp view for that, this view has a line in it for each chunk of memory allocated from it (and also the free chunks), along the reasons (or comments) for what reason these chunks were allocated.

NB! Don’t run this query in production! And the reason is that whenever X$KSMSP is queried, it locks the shared pool (by taking shared pool latches) and walks through the linked lists of memory chunks in it. If your shared pool is large and you have lots of chunks in there, the linked list walking will take long time, thus the latches will be held for long time. And while shared pool latches are held, no-one can even log on as new sessions usually need to allocate some memory for session state (like v$parameter array) from shared pool.

Nevertheless, if you see KGH: NO ACCESS allocations in V$SGASTAT in your test database, you can continue with querying, where exactly (in SGA memory) these allocations reside:

SQL> SELECT ksmchcom,ksmchptr,ksmchsiz,ksmchcls
  2  FROM x$ksmsp  -- do not query from this view in production!!!
  3  WHERE ksmchcom = 'KGH: NO ACCESS'
  4  AND rownum = 1;
KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS
---------------- ---------------- ---------- --------
KGH: NO ACCESS   00000000BCFF7FE0      32800 no acce

SQL>

I limited my search for only one chunk in this case. And that chunk lives in address  BCFF7FE0 and is 32800 bytes in size.

So, now I know where one of the KGH: NO ACCESS chunks is residing. Out of interest, let’s query if there are any buffer cache buffers placed inside that chunk of 32800 bytes. X$BH.BA column nicely tells us the physical address of a buffer cache buffer in SGA:

SQL> SELECT
  2      dbarfil, dbablk, obj, ba, state             --<-- dbarfil = relative file number, dbablk = block number, obj = data object id
  3  FROM
  4      x$bh                                        --<-- X$BH lists all initialized buffers in buffer cache
  5  WHERE
  6      to_number(rawtohex(ba),'XXXXXXXXXXXXXXXX')  --<-- BA is the "buffer address", actual buffer location in SGA memory
  7      BETWEEN
  8          to_number('00000000BCFF7FE0','XXXXXXXXXXXXXXXX')
  9      AND to_number('00000000BCFF7FE0','XXXXXXXXXXXXXXXX') + 32800 - 1
 10  /
   DBARFIL     DBABLK        OBJ BA                    STATE
---------- ---------- ---------- ---------------- ----------
         4      34936      81252 00000000BCFFE000          1
         3      46222 4294967295 00000000BCFF8000          1
         3      34676 4294967295 00000000BCFFA000          0
         2       7074       5938 00000000BCFFC000          1

Wow! This query reports that indeed there are 4 buffer cache buffers which physically reside in the KGH: NO ACCESS shared pool chunk reported by x$ksmsp!

As the OBJ column has the data_object_id of the object in it, lets see what some of these objects are!

SQL> select owner, object_name, object_type
  2  from dba_objects
  3  where data_object_id = 81252;
OWNER              OBJECT_NAME             OBJECT_TYPE
------------------ ----------------------- -------------------
TANEL              SERVER_STATS_SUM        TABLE
SQL> select owner, object_name, object_type
  2  from dba_objects
  3 where data_object_id = 5938;
OWNER              OBJECT_NAME             OBJECT_TYPE
------------------ ----------------------- -------------------
SYS                WRH$_SQL_PLAN           TABLE

So, both above tables have a block residing physically in shared pool heap :)

Note that there’s strangely large data_object_id in two other blocks, which don’t return anything from dba_objects:

SQL> select owner, object_name, object_type
  2  from dba_objects
  3  where data_object_id = 4294967295;

no rows selected

These are blocks belonging to undo segments. I can take the DBARFIL returned from X$BH and match it to relative_fno in dba_data_files to find out into which tablespace these blocks belong:

SQL> select tablespace_name
  2  from dba_data_files
  3  where relative_fno = 3;
TABLESPACE_NAME
------------------------------
UNDOTBS1

So, Oracle CAN store buffer cache buffers inside shared pool nowadays (since 10.2).

Now the next question is – why on the earth would Oracle want such complexity?

The answer is ASMM – Automatic Shared Memory Management, the manageability thing controlled with SGA_TARGET parameter and the need to increase buffer cache at the expense of shared pool.

I’m pasting some reasoning from an old Oracle-L post of mine here (posted 3 years ago… huh time flies fast, I planned to write an article to my website about it right after that Oracle-L posting back then :)

Note that the query I posted at that Oracle-L article also queries X$KSMSP – so you should not run it in production unless you want to cause performance trouble or hang in there!

When MMAN tries to get rid of a shared pool granule it obviously can't just
flush and throw away all the object in it. As long as anybody references
chunks in this granule, it cannot be completely deallocated.

Oracle has faced a decision what to do in this case:
1) wait until all chunks aren't in use anymore - this might never happen
2) suspend the instance, relocate chunks somewhere else and update all
SGA/PGA/UGA/CGA structures for all processes accordingly - this would get
very complex
3) flush as many chunks from this shared pool granule as possible, mark them
as "KGH: NO ACCESS" that nobody else would touch them, mark corresponding
entry to DEFERRED in V$SGA_RESIZE_OPS and notify buffer cache manager about
the new memory locations being available for use.

Oracle has gone with option 3 as option 1 wouldn't satisfy us and 2 would be
very complex to implement, and it would mean a complete instance hang for
seconds to minutes.

So, Oracle can share a granule between shared pool and buffer cache data.
This sounds like a mess, but there is not really a better way to do it (if
leaving the question, why the heck do you want to continuously reduce your
shared pool size anyway, out).

Here you go, here’s the proof that Oracle does store buffer cache buffers inside shared pool if its needs to do so :)

By the way, nowadays Oracle can store redo log buffers and undo data inside shared pool as well, but more about it in a future blog entry (come back in 3 years ;-)

This entry was posted in Oracle and tagged , , . Bookmark the permalink.

13 Responses to KGH: NO ACCESS allocations in V$SGASTAT – buffer cache within shared pool!

  1. Coskan says:

    Interesting one again. Thanks for sharing.

    I wonder how did you come to conclusion of 3 choices for Oracle.

    Was it after talks with guys from Oracle or came out of your mind only ?

  2. Tanel Poder says:

    Oh, I tried to reason about this myself. Asking WHY something is like it is (from yourself) can be quite educating as that’s how you can create more connections between things observed in your brain! :)

  3. Doug Burns says:

    That’s fascinating stuff – cheers!

  4. Bernard Polarski says:

    We got plenty of these ‘KGH no access’ in our environment, in such an extend that we seriously questioning the ASMM feature.

    I put your 3 queries into one to get an overall picture:

    set lines 190 pagesize 66
    col owner for a26
    col object_name for a30
    col DBARFIL for 9999 head ‘File|id’ justify l
    col ba head ‘Memory Address’
    select
    dbarfil, dbablk, obj, ba, state , o.owner, o.object_name, o.object_type
    from
    x$bh a,
    ( SELECT distinct ksmchptr FROM x$ksmsp WHERE ksmchcom = ‘KGH: NO ACCESS’) b ,
    dba_objects o
    where
    to_number(rawtohex(ba),’XXXXXXXXXXXXXXXX’)
    BETWEEN
    to_number(b.ksmchptr,’XXXXXXXXXXXXXXXX’)
    AND to_number(b.ksmchptr,’XXXXXXXXXXXXXXXX’) + 32800 – 1
    and a.obj=o.data_object_id(+)
    /

    File
    id DBABLK OBJ Memory Address STATE OWNER OBJECT_NAME OBJECT_TYPE
    —– ———- ———- —————- ———- ————————– —————————— ——————-
    24 7531 1932311 000000018D1D2000 2 GILLET_B1 POOL_HIST_ITEM TABLE SUBPARTITION
    20 18476 2004509 0000000190F96000 2 GILLET_B1 TXN1_ACQ1 INDEX SUBPARTITION

    Problem is there is not user ‘GILLET_B1′ connected at this moment and no job running.
    It it possible to trace which other session could be responsible of the pin block?

    I suspect header block of the sql in shared pool being pinned while many schema run different childs. This is an application with many schema running the same SQL over same tables structures (each schema has a set of identical tables).

  5. Tanel Poder says:

    Hi Bernard,

    I had written similar query in the Oracle-L post I referred to too.

    Note that with x$ksmsp queries you pretty much hang your instance for duration of that query..

    These buffers aren’t necessarily pinned, they are just buffer cache buffers.

    I think you may be understanding it the other way. The reason for KGH: NO ACCESS is that there are some *shared pool objects* pinned in a granule, thus the entire granule can not be freed. Therefore we won’t free the granule but just reallocate any memory we can get from it as KGH: NO ACCESS so shared pool manager wouldn’t touch that memory and then give it to buffer cache manager.

  6. Bernard Polarski says:

    The problem comes from ASMM decreasing shared pool in favor of cache buffer. When the shared pool needs to grow in peak time it cannot anymore. Hundreds of megs are in ‘KGL: no access’ and ORA-4031 start to pop into the system.

  7. Tanel Poder says:

    Disable ASMM in that case and manually adjust the pool sizes as needed.

  8. Tanel Poder says:

    Shared pool resizing can cause other problems as well, like latch contention and even hangs, I’ll write about it soon.

  9. Nitin says:

    Is this not similar to Note:70166.1 on Metalink? Just wondering.

  10. Tanel Poder says:

    No its something different. That note explains why there is db_block_buffers allocation in shared pool. This happens when you still use the old db_block_buffers init.ora parameter for setting buffer cache size – so the old (non-dynamic) buffer cache allocation method is used. When you use db_cache_size parameter, then the buffer headers (x$bh) memory is inside buffer cache granules themselves, thus there’s no need to allocate that memory from shared pool.

  11. Bernard Polarski says:

    Just a quick feed back : We were using db_cache_size, sga_target and pga_aggregate_target.
    shared_pool_size was unset. Given all the problems, I finally disabled ASSM and will not used it again 10.2.0.5 is out.

  12. Pablo says:

    Really interesting post.
    I came across the “KGH: NO ACCESS”
    while writting a query to get shared pool sizes from AWR tables.
    Initially was using DBA_HIST_SGASTAT, adding all components of the shared pool (initially after reading this post I ignored the KGH: NO ACCESS entry)
    But the numbers didn’t match the ones in proper AWR reports.
    I decided to check DBA_HIST_SHARED_POOL_ADVICE and realized that Oracle is getting the initial and end sized for the shared pool on AWR reports from there (at least the numbers match)
    I wonder where is Oracle actually getting those…

  13. That’s fascinating stuff – cheers!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>