2 Replies Latest reply: Mar 30, 2011 11:05 AM by 819458 RSS

    Library Latch Contention - A Cautionary Tale

      we recently encountered a library latch contention issue with our apex application (v3.1.2 on RDBMS v10.2.0.5) at a high volume site (800+ users) and i thought it was worth sharing what we found

      the users were reporting a serious degradation in performance under load, and a quick look at the top wait events showed that contention on the library cache latch was to blame

      Top 5 Timed Events

      Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
      latch: library cache 788,755 106,096 135 15.2 Concurrency
      latch free 1,599,072 70,099 44 10.0 Other
      CPU time 62,604 9.0
      db file sequential read 5,063,905 43,760 9 6.3 User I/O
      latch: library cache lock 288,246 41,931 145 6.0 Concurrency

      we needed to find out what was causing the contention, which was where the excellent latchprof.sql script from tanel poder (http://blog.tanelpoder.com/) proved invaluable - it showed that there was one particular bit of SQL that was causing the problem

      SQL> @latchprof name,sqlid % "library cache" 10000;

      NAME SQLID Held Gets Held % Held ms Avg hold ms
      library cache lock allocation 4dqasqjrvudqn 7880 3548 78.80 17367.520 4.895
      library cache lock allocation 6472 3034 64.72 14264.288 4.701
      library cache pin allocation 408 373 4.08 899.232 2.411

      turned out this was the call dbms_session.reset_package that's invoked by every request that's made to the apex dad

      this was also one of the top pieces of SQL by CPU in the database so clearly something was amiss

      cutting a long story short the customer had set


      which on the face of it doesn't seem unreasonable

      but since we know that apex hardly ever reuses cursors (because it calls reset_package on each request) we felt it was too high

      the normal recommendation for our apex application is to stick with the default value (20)

      the theory being that caching too many cursors just gives the call to reset_package too much to do when its being called so often

      (at this site we can see over 50,000 executions per hour on reset_package)

      the site removed the parameter and performance has since returned to normal - and the latch contention has disappeared

      SQL> @latchprof name,sqlid % "library cache" 10000;

      NAME SQLID Held Gets Held % Held ms Avg hold ms
      library cache 3yxpcvrnkzfvd 406 383 4.06 613.466 1.602
      library cache lock allocation 4dqasqjrvudqn 320 320 3.20 483.520 1.511
      library cache lock allocation 201 201 2.01 303.711 1.511

      hopefully all that info may be of use to anyone that finds themselves in a similar situation :)