11 Replies Latest reply: Jul 25, 2012 9:48 PM by Aman.... RSS

    relation between enq:HW – contention  v/s library cache lock

    951438
      Hi All,

      Sometime back we were seeing library cache lock in one of our database.

      One of my colleague execute alter system flush shared pool.

      After some time the library cache event was gone however i see the enq:HW – contention as the top event in AWR

      before flushing shared pool.

      Event Waits Time(s) Avg wait (ms) % DB time Wait Class
      library cache lock 2,356,438 64,073 27 71.26 Concurrency
      library cache: mutex X 4,780,489 21,881 5 24.34 Concurrency
      db file sequential read 329,465 2,279 7 2.54 User I/O
      cursor: pin S wait on X 50,922 615 12 0.68 Concurrency
      DB CPU 481 0.53

      after flushing shared pool

      Event Waits Time(s) Avg wait (ms) % DB time Wait Class
      enq: HW - contention 40,570 59,928 1477 77.45 Configuration
      db file sequential read 2,455,030 12,175 5 15.73 User I/O
      DB CPU 2,419 3.13
      log file sync 214,483 1,551 7 2.00 Commit
      enq: TX - row lock contention 396 443 1118 0.57 Application

      Just wanted to know is there any relation between enq:HW – contention and library cache lock.

      Kindly suggest.

      Thanks in advance.

      Thanks and Regards,
        • 1. Re: relation between enq:HW – contention  v/s library cache lock
          sb92075
          948435 wrote:
          Hi All,

          Sometime back we were seeing library cache lock in one of our database.
          It happens all the time & is normal and expected behavior.

          >
          One of my colleague execute alter system flush shared pool.
          Is this colleague still employed there? If so, why?
          • 2. Re: relation between enq:HW – contention  v/s library cache lock
            951438
            Hi ,

            After flushing the shared pool, enq:hw contention event occurs.

            As you said this is a normal behaviour.

            Please explain me in detail.
            Is there any relation between enq:HW – contention v/s library cache lock.

            Please suggest.

            Thanks
            • 3. Re: relation between enq:HW – contention  v/s library cache lock
              Aman....
              I can't think of anything so obvious as a relation between two but one thing I would say that the very advice of flushing shared pool is a wrong one. The lock would be acquired when you have the breakable parse lock to make the cursors as invalid. Now, if the assumption is that flushing the shared pool would flush the locks for those objects which are already being accessed(pinned) by some session, that's wrong. The pinned cursors would be still there AFAIK. So I don't think this would solve the issue. I think you should check that you are not running anything that's causing the db objects to get altered physically (like gathering stats, adding/dropping columns) so that this contention would be less.

              Just my 2 cents.

              Aman....
              • 4. Re: relation between enq:HW – contention  v/s library cache lock
                sb92075
                http://www.oracleangels.com/2011/08/enq-hw-contention-bug-9407198.html
                • 5. Re: relation between enq:HW – contention  v/s library cache lock
                  951438
                  Hi ,

                  I checked the ASH report and the enq:HW – contention is against update query.

                  I also see that it is locking is also there during that time.

                  Blocking Sid (Inst) % Activity Event Caused % Event User Program # Samples Active XIDs
                  376,13950( 1) 3.13 enq: HW - contention 3.13 USER JDBC Thin Client 57/60 [ 95%] 55
                  259,23539( 1) 2.85 enq: HW - contention 2.85 USER JDBC Thin Client 47/60 [ 78%] 45
                  252,49009( 1) 2.80 enq: HW - contention 2.80 USER JDBC Thin Client 54/60 [ 90%] 46
                  79, 9566( 1) 2.75 enq: HW - contention 2.75 USER JDBC Thin Client 54/60 [ 90%] 52
                  44,60409( 1) 2.69 enq: HW - contention 2.69 USER JDBC Thin Client 51/60 [ 85%] 51

                  As per the bug 9407198, this is due to multiple insert statement is trying to access the same block.

                  In my case the culprit statement is update.

                  Do you think it is a bug.

                  Kindly suggest

                  Thanks
                  • 6. Re: relation between enq:HW – contention  v/s library cache lock
                    sb92075
                    948435 wrote:

                    Do you think it is a bug.
                    It really does not matter what I think.
                    Consider opening a Service Request to learn what Oracle concludes about your situation.
                    • 7. Re: relation between enq:HW – contention  v/s library cache lock
                      Hemant K Chitale
                      is there any relation between enq:HW – contention and library cache lock.
                      No, I would think not. What you have are symptoms of two different issues.


                      Hemant K Chitale
                      • 8. Re: relation between enq:HW – contention  v/s library cache lock
                        Karan
                        No such relation as such, might offcourse be the symptoms of another issue but all i would tell you is that HW enqueue is just to increase HWM of a table, but yes HV and HW are somewhere same, difference being former is for HWM but the latter is for direct path.. as mentioned above library cache locks are parse locks which are used to keep dependency good between objects ... like aman said parse locks can be broken if DDL or some other packages are invoked... so simply speaking they use object handles and locks are put on them, if they are not available they would surely not be available because you wait till the time resource is in incompatible mode....

                        Regards
                        Karan
                        • 9. Re: relation between enq:HW – contention  v/s library cache lock
                          951438
                          Hi,

                          I can see in awr report lot of delete query when there was a library cache lock.


                          I guess this is a culprit.

                          Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
                          302 12 0 0.34 43cf13083wvr7 JDBC Thin Client /* criteria query */ select th...
                          276 12 8,326 0.03 0.31 4s8ckc819bz1h JDBC Thin Client delete from OWNER.SSR_SRCH...
                          260 12 8,330 0.03 0.29 860vyy4ddcunq JDBC Thin Client delete from OWNER.CST_SRCH...
                          244 11 8,328 0.03 0.27 dcryp5jkdjv45 JDBC Thin Client delete from OWNER.FLT_SRCH...
                          234 10 8,327 0.03 0.26 fpzf19885qgnu JDBC Thin Client delete from OWNER.RES_PRPT...
                          189 9 8,327 0.02 0.21 1bstm6uzxj15a JDBC Thin Client delete from OWNER.FLT_SRCH...
                          173 5 15,030 0.01 0.19 08uym9gfghdad JDBC Thin Client select activecomp0_.SCR_DOMN_I...


                          Please suggest.

                          Thanks and Regards,
                          • 10. Re: relation between enq:HW – contention  v/s library cache lock
                            sb92075
                            948435 wrote:
                            Hi,

                            I can see in awr report lot of delete query when there was a library cache lock.


                            I guess this is a culprit.

                            Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
                            302 12 0 0.34 43cf13083wvr7 JDBC Thin Client /* criteria query */ select th...
                            276 12 8,326 0.03 0.31 4s8ckc819bz1h JDBC Thin Client delete from OWNER.SSR_SRCH...
                            260 12 8,330 0.03 0.29 860vyy4ddcunq JDBC Thin Client delete from OWNER.CST_SRCH...
                            244 11 8,328 0.03 0.27 dcryp5jkdjv45 JDBC Thin Client delete from OWNER.FLT_SRCH...
                            234 10 8,327 0.03 0.26 fpzf19885qgnu JDBC Thin Client delete from OWNER.RES_PRPT...
                            189 9 8,327 0.02 0.21 1bstm6uzxj15a JDBC Thin Client delete from OWNER.FLT_SRCH...
                            173 5 15,030 0.01 0.19 08uym9gfghdad JDBC Thin Client select activecomp0_.SCR_DOMN_I...


                            Please suggest.

                            Thanks and Regards,
                            Does every Foreign Key have a corresponding INDEX?
                            • 11. Re: relation between enq:HW – contention  v/s library cache lock
                              Aman....
                              Since delete doesn't alter the table structure which would end up in making the cursor invalid, I am not sure how you have linked this with the library cache lock?

                              Aman....