A little new feature for shared pool geeks :-)

If you’ve taken any shared pool dumps from 11g+ databases lately, you might have wondered about what all the memory allocation reason codes like SQLA^ea880c38, KGLS^da11791e might mean.

Oracle 11g has introduced a little improvement in how library cache manager allocates shared pool chunks for its objects.

Here’s an excerpt from a shared pool heap dump, but this is visible also from X$KSMSP (you should be very careful when thinking of running shared pool heap-dumps or querying X$KSMSP in busy production databases as they may hang your instance for a while).

  Chunk 93ff6000 sz=     4096    freeable  "SQLA^ea880c38  "  ds=0x947880a4
  Chunk 93ff7000 sz=     4096    freeable  "KGLS^da11791e  "  ds=0x90f5f6b4
  Chunk 93ff8000 sz=     4096    freeable  "SQLA^97be6474  "  ds=0x90abb49c
  Chunk 93ff9000 sz=     4096    freeable  "SQLA^ea880c38  "  ds=0x947880a4
  Chunk 93ffa000 sz=     4096    freeable  "SQLA^4dd0e25f  "  ds=0x90d564b4
  Chunk 93ffb000 sz=     4096    recreate  "SQLA^99c127e6  "  latch=(nil)
     ds 946a0e4c sz=    12288 ct=        3
        923dcd58 sz=     4096
        923ded58 sz=     4096
  Chunk 93ffc000 sz=     4096    freeable  "PLMCD^336b5f2b "  ds=0x903f4bb4
  Chunk 93ffd000 sz=     4096    freeable  "SQLA^a730b47d  "  ds=0x9012049c
  Chunk 93ffe000 sz=     4096    freeable  "SQLA^8a34991c  "  ds=0x942b4e14
  Chunk 93fff000 sz=     4096    recreate  "KGLS^d3be5dbe  "  latch=(nil)

What are the new cryptic-looking hex strings in the chunk comment? These appeared in Oracle 11g…

Someone in Oracle decided to use some of the “real estate” of a chunk comment (a comment is passed in to every chunk when it’s allocated - for memory leak troubleshooting reasons) for putting the hash value of the corresponding library cache object in it!

This is pretty cool as whenever you have an ORA-4031 in the middle of the night when you’re not there, then you’ll at least be able to extract the library cache object hash values from the dumpfile. Later you can use these hash values to query various AWR or Statspack views (or just V$SQL & X$KGLOB) to find which objects were you dealing with.

For example, I picked one hash value stored in the chunk SQLA^99c127e6 (SQLA stands for SQL area) and queries V$SQL (this assumes that the cursor with this hash value is present in library cache):

SQL> SELECT child_number,child_address,sql_text 
     FROM v$sql 
     WHERE hash_value = TO_NUMBER('99c127e6', 'XXXXXXXXXXXXXXXX');

CHILD_NUMBER CHILD_AD SQL_TEXT
------------ -------- -----------------------------------------------------
           0 983FA974 WITH MET AS (SELECT DISTINCT METRIC_GUID METRIC_GU...
           1 98925BE8 WITH MET AS (SELECT DISTINCT METRIC_GUID METRIC_GU...

Let’s try another one, KGLS^d3be5dbe:

SQL> SELECT child_number,sql_text 
     FROM v$sql 
     WHERE hash_value = TO_NUMBER('d3be5dbe', 'XXXXXXXXXXXXXXXX');

no rows selected

Nothing is returned from V$SQL - because that chunk is apparently not allocated for a cursor. V$SQL only shows us cursors from library cache, but in order to see every library cache object, we can query the underlying X$KGLOB:

SQL> SELECT kglnaown owner, kglnaobj name 
     FROM x$kglob 
     WHERE kglnahsh = TO_NUMBER('d3be5dbe', 'xxxxxxxxxxxxxxxx');

OWNER      NAME
---------- ------------------------------
SYS        C_OBJ#

Apparently this chunk was allocated for a C_OBJ# cluster owner by SYS user…

This note was originally posted at my old website: http://tech.e2sn.com/oracle/troubleshooting/shared-pool