library cache


OMS, select from dual and library cache latches (and how to diagnose)

Yesterday I went through a problem related to library cache latch contention which I was able to diagnose with the help of Arup Nanda blog
It was OMS-Enterprise Manager Grid Control who was blocking dozens of other user sessions… The problem is classified in Metalink under Bugs but described as “not a bug”: Bug 10061837: CHILD LIB CACHE LATCH HELD BY SESSION TRAVERSING V$SQL CAUSES HANG

This led to investigate if we could decrease the library cache latch, and investigation on how to avoid soft parses and how to tune the SESSION_CACHED_CURSOR parameter. Help this time come from a compilation work by Juan Carlos Reyes Pacheco on oracle-l mailing list

Another question was brought by another DBA, what is the influence of “select … from dual” into library cache latches. This time a good answer from Tom Kyte showed the huge gain of having direct assignment of variables in PLSQL instead of using or abusing of the dual table. I copy Tom Kyte’s conclusion: “Why do so many people “select …. from dual” in plsql when a simple assignment would work??”