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


NB! Dates updated: After a 1.5 year break, this year’s only Advanced Oracle Troubleshooting training class (updated with Oracle 12c content) takes place on 14-18 December 2015 and 11-15 January 2016 (I had to reschedule the start from November to December). So sign up now if you want to learn new cool stuff!

This entry was posted in Oracle and tagged , , . Bookmark the permalink.

4 Responses to A little new feature for shared pool geeks :-)

  1. Dion Cho says:

    I’ve also found that this additional info(LCO’s hash value) is printed out in ORA-04031 error message. For instance

    ORA-04031: unable to allocate 4016 bytes of shared memory ("shared
    pool","select 'x ...","SQLA^9aec1c76","ub1[]: qkexrXformVal")

  2. Tanel Poder says:

    @Dion Cho

    Yes, this is because ORA-4030/4031 dumps print out the chunk comment for which the allocation was made – and this hash value is stored in the comment…

  3. Paul Janda says:

    I could have used this in 7.3 and 8.1.7.
    Has anybody had a scenario, in 11g, where this was the ONLY way to resolve ora-4031? My, albeit, inexperienced, situations involving ora-4031 usually came with other symptoms or traceable inefficiencies.
    With that said, I think this post is really interesting and I (almost, but not quite) wish to have a 4031 problem in the near future.

  4. Tanel Poder says:

    @Paul Janda
    I haven’t actually used it for anything practical yet. In earlier versions, X$KSMLRU is one very useful view for troubleshooting shared pool flushing & fragmentation issues…

Leave a Reply

Your email address will not be published. Required fields are marked *