A little new feature for shared pool geeks :-)

Tanel Poder

2010/11/05

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

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

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: