10 Replies Latest reply: Sep 13, 2011 8:07 AM by user8985629 RSS

    Library Cache Lock

    878536
      Hi,
      my db version is 11.0.1.7
      i have high library cahce lock waits, and problematic sql is SELECT statement.
      what may be the cause?
      shared_pool_size 1500 MB
      session_cached_cursors 20.
      Thanks,


      {
      Top 5 Timed Foreground Events
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      Avg
      wait % DB
      Event Waits Time(s) (ms) time Wait Class
      ------------------------------ ------------ ----------- ------ ------ ----------
      library cache lock 6,193,375 203,610 33 79.9 Concurrenc
      DB CPU 12,668 5.0
      library cache: mutex X 59,365,758 11,970 0 4.7 Concurrenc
      cursor: mutex S 35,166,712 8,079 0 3.2 Concurrenc
      control file sequential read 2,208,754 2,646 1 1.0 System I/O
      }
        • 1. Re: Library Cache Lock
          sgalaxy
          Hi,
          you can consult the following:
          http://asktom.oracle.com/pls/asktom/f?p=100:11:3135704510460626::::P11_QUESTION_ID:1163635055580

          http://www.dba-oracle.com/m_library_cache_load_lock.htm

          Greetings,
          Sim
          • 2. Re: Library Cache Lock
            878536
            Hi,
            there is no DDL activity in terms of causing library cache lock.
            cursor_sharing is SIMILAR.
            But sql causing library cache lock is SELECT statement.
            And version count for that SELECT statement is high.
            select count(id) from XXXX  where a1 is not null and a2 is not null and a3=:"SYS_B_0"
            what may be the reason for that.
            Thanks,
            • 3. Re: Library Cache Lock
              Jonathan Lewis
              >
              cursor_sharing is SIMILAR.
              But sql causing library cache lock is SELECT statement.
              And version count for that SELECT statement is high.
              *select count(id) from XXXX  where a1 is not null and a2 is not null and a3=:"SYS_B_0"
              what may be the reason for that.
              How high is high ?
              Some people might say that a version count of 10 is high, some might not think it high until it reaches 100 ?

              High version counts lead to increased activity on library cache latches (and mutexes).
              cursor_sharing = SIMILAR leads to an increase in version counts because Oracle will re-optimize the query if it thinks the input values "might make a difference" to the execution plan.
              Range-based predicates, partition keys in predicates, histograms on columns in predicates are cases where you might see re-optimisation taking place.

              I believe Oracle's suggestion for 11g is to set cursor_sharing to force (rather than similar) and let adaptive cursor sharing take care of variation in input values. This MIGHT (in your case) result in fewer versions and less contention.
              The ideal solution is to rewrite the code so that you can set cursor_sharing back to exact.

              Regards
              Jonathan Lewis
              http://jonathanlewis.wordpress.com
              http://www.jlcomp.demon.co.uk

              To post code, statspack/AWR report, execution plans or trace files, START and END the text with the tag {noformat}
              {noformat} (the word "code" in lowercase, curly brackets, no spaces) so that the text appears in 
              fixed format
              .
              
              There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
              • 4. Re: Library Cache Lock
                878536
                Hi Jonothan,
                thanks for reply.
                version_count in my case is about 8500 in 1 hour period.
                If i remove histograms for the columns in the sql, would that solve my problem?
                In that case, no optimization will take for the execution plan ?
                Thanks a lot,
                • 5. Re: Library Cache Lock
                  Jonathan Lewis
                  user8907689 wrote:
                  Hi Jonothan,
                  thanks for reply.
                  version_count in my case is about 8500 in 1 hour period.
                  If i remove histograms for the columns in the sql, would that solve my problem?
                  It might do. I gave you two other reasons why you get re-optimisation, though, and I know there is one more that I can't remember at the moment.
                  You may still see the problem persist, at least for a while, because with that many child cursors you could have a bit of a mess in your library cache that stops the existing child cursors from being discarded properly - even after a flush shared pool - until you restart the instance. (There are a couple of bugs in this area.) But if you're lucky a flush shared pool after dropping the histograms might clear the pool and restore sanity
                  In that case, no optimization will take for the execution plan ?
                  Typically you tend to end up with a single plan that will get re-used - but there are various reasons (length of string variables, nls settings varying across clients, different optimizer environments) why you might see more than one. For a query as simple as yours, though I've rearely seen number beyond a couple of dozen. Once the small number of plans has been created, re-optimization is no longer needed.

                  Regards
                  Jonathan Lewis
                  http://jonathanlewis.wordpress.com
                  http://www.jlcomp.demon.co.uk


                  To post code, statspack/AWR report, execution plans or trace files, START and END the text with the tag {noformat}
                  {noformat} (the word "code" in lowercase, curly brackets, no spaces) so that the text appears in 
                  fixed format
                  .
                  
                  There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                  • 6. Re: Library Cache Lock
                    user8985629
                    I am having this issue where my select statemant caused library cache lock and oteher queries where hung ...what could be the reason for select statement to cause lock.

                    Thanks
                    • 7. Re: Library Cache Lock
                      sb92075
                      user8985629 wrote:
                      I am having this issue where my select statemant caused library cache lock and oteher queries where hung ...what could be the reason for select statement to cause lock.
                      post SQL & results that prove statement above

                      With Oracle readers do not block writers & writers do not block readers.

                      do as below so we can know complete Oracle version & OS name.

                      Post via COPY & PASTE complete results of
                      SELECT * from v$version;
                      • 8. Re: Library Cache Lock
                        VenkatB
                        >
                        I am having this issue where my select statemant caused library cache lock and oteher queries where hung ...what could be the reason for select statement to cause lock.
                        >

                        Run this SQL when you encounter the library cache lock to find out who and which SQL is really causing the lib lock. Think I found this SQL from one of the OTN sites long back when I had this library cache lock problem in 9i databases.
                        SELECT /*+ ordered */ 
                             w1.sid  waiting_session,  
                             w1.username who,
                              w1.sql_id SQL_ID,
                             h1.sid  holding_session,
                             w.kgllktype lock_or_pin,
                             w.kgllkhdl address,     
                             DECODE(h.kgllkmod,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_held,
                                DECODE(w.kgllkreq,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_requested,
                             h1.sql_hash_value  ,
                             lock_id1 object_waited_on
                        FROM 
                             dba_kgllock w, 
                             dba_kgllock h, 
                             v$session w1, 
                             v$session h1 , 
                             dba_lock_internal dl
                        WHERE   
                             (((h.kgllkmod != 0) AND (h.kgllkmod != 1)      
                           AND      ((h.kgllkreq = 0) OR (h.kgllkreq = 1)))
                           AND  (((w.kgllkmod = 0) OR (w.kgllkmod= 1))      AND ((w.kgllkreq != 0)
                           AND (w.kgllkreq != 1))))
                            AND  w.kgllktype    =  h.kgllktype   AND  w.kgllkhdl        =  h.kgllkhdl
                        AND  w.kgllkuse=w1.saddr   AND  h.kgllkuse=h1.saddr
                        and dl.mode_requested<>'None'
                        and dl.mode_requested<>dl.mode_held
                        and w1.sid=dl.SESSION_ID
                        /
                        • 9. Re: Library Cache Lock
                          Karan
                          This metric library cache lock happens when one client can prevent other clients from accessing the same object or the client can maintain a dependency for persistent time and this lock is also obtained to locate an object in the library cache. In your case i think library cache lock corruption can also be the cause may be, and a process might be waiting for a library cache lock without a holder or incorrect lock mode and so on .. first try to restart the instance and then try with your query.. and definitely if you are in 11G you better keep it to FORCE for cursor_sharing in order to reduce versions and contention.

                          Regards
                          Karan
                          • 10. Re: Library Cache Lock
                            user8985629
                            BANNER
                            ----------------------------------------------------------------
                            Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
                            PL/SQL Release 10.2.0.4.0 - Production
                            CORE     10.2.0.4.0     Production
                            TNS for Solaris: Version 10.2.0.4.0 - Production
                            NLSRTL Version 10.2.0.4.0 - Production


                            5 rows selected.