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).
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):
Let's try another one, KGLS^d3be5dbe
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:
Apparently this chunk was allocated for a C_OBJ# cluster owner by SYS user...