Shared Pool troubleshooting

I'll start gathering my shared pool troubleshooting notes here... I'll start with a little new feature useful for very low-level troubleshooting of shared pool usage issues.

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

Below is 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...