{"id":36,"date":"2010-11-24T09:58:00","date_gmt":"2010-11-24T09:58:00","guid":{"rendered":"http:\/\/anjo.pt\/wp\/keyword-oracle\/2010\/11\/24\/oms-select-from-dual-and-library-cache-latches-and-how-to-diagnose\/"},"modified":"2015-11-17T20:27:24","modified_gmt":"2015-11-17T19:27:24","slug":"oms-select-from-dual-and-library-cache-latches-and-how-to-diagnose","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2010\/11\/24\/oms-select-from-dual-and-library-cache-latches-and-how-to-diagnose\/","title":{"rendered":"OMS, select from dual and library cache latches (and how to diagnose)"},"content":{"rendered":"<p>Yesterday I went through a problem related to library cache latch contention which I was able to diagnose with the help of <a href=\"http:\/\/arup.blogspot.com\/2008\/08\/diagnosing-library-cache-latch.html\">Arup Nanda blog<\/a>.&nbsp;<a href=\"http:\/\/arup.blogspot.com\/2008\/08\/diagnosing-library-cache-latch.html%20\"><\/a><br \/>\nIt was OMS-Enterprise Manager Grid Control who was blocking dozens of other user sessions&#8230; The problem is classified in Metalink under Bugs but described as &#8220;not a bug&#8221;:&nbsp;Bug 10061837: CHILD LIB CACHE LATCH HELD BY SESSION TRAVERSING V$SQL CAUSES HANG<\/p>\n<p>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 <a href=\"http:\/\/www.freelists.org\/post\/oracle-l\/session-cached-cursors-pros-cons,3\">compilation work by Juan Carlos Reyes Pacheco on oracle-l mailing list<\/a><\/p>\n<p>Another question was brought by another DBA, what is the influence of &#8220;select &#8230; from dual&#8221; into library cache latches. This time a good <a href=\"http:\/\/asktom.oracle.com\/pls\/asktom\/f?p=100:11:0::::P11_QUESTION_ID:3675961676045#3767912888051\">answer from Tom Kyte<\/a> 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&#8217;s conclusion: &#8220;Why do so many people &#8220;select &#8230;. from dual&#8221; in plsql when a simple assignment would work??&#8221;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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.&nbsp; It was OMS-Enterprise Manager Grid Control who was blocking dozens of other user sessions&#8230; The problem is classified in Metalink under Bugs but described as &#8220;not a bug&#8221;:&nbsp;Bug 10061837: CHILD [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29,28,31,30,6],"tags":[],"class_list":{"0":"post-36","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-dual","7":"category-latch","8":"category-library-cache","9":"category-oms","10":"category-oracle","11":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/36","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=36"}],"version-history":[{"count":1,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/36\/revisions"}],"predecessor-version":[{"id":180,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/36\/revisions\/180"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=36"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=36"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=36"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}