If you’ve taken any shared pool dumps from 11g+ databases lately, you might have wondered about this:
Tanel Poder's blog: IT & Mobile for Geeks and Pros
Oracle, Exadata, Performance, Troubleshooting - Mobile Life and Productivity.
About me
The Exadata Conference
The Exadata Experts
The Exadata Book
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")
@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…
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.
@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…