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

Tanel Poder

2009-09-09

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 ;-)


  1. I am finally close to launching the completely rebuilt 2024 versions of my Linux & AOT classes in my Learning Platform! (Updates to SQL Tuning class in H2 2024):
    Advanced Oracle SQL Tuning training. Advanced Oracle Troubleshooting training, Linux Performance & Troubleshooting training. Check them out!
  2. Get randomly timed updates by email or follow Social/RSS