Apex Library Cache Latch Contention

Post to Twitter

I recently encountered an unusual contention issue on the library cache latch, and thought it was worth recording what happened and how the issue was resolved.

The problem occurred with one of our Oracle Apex applications running on v3.1.2 of Oracle Apex and v10.2.0.5 of Oracle RDBMS. For the sake of completeness the application was hosted on an IBM AIX 6 server but that was not relevant to the investigation.

The site who reported the problem are a relatively high volume site, with over 800 users and around 400,000 page views per day. The were reporting a serious degradation in performance under load, to the point where the application was almost unusable so an urgent investigation was required.

The site had already run off some AWR reports that clearly showed the problem lay with the library cache latch – the Top 5 Timed Events from the report are shown below.

Top 5 Timed Events                                      Avg %Total
~~~~~~~~~~~~~~~~~~                                     wait   Call
Event                              Waits    Time (s)   (ms)   Time 
---------------------------- ----------- ----------- ------ ------ 
latch: library cache             692,012      86,615    125   20.0 
CPU time                                      41,692           9.7
db file sequential read        3,656,449      30,389      8    7.0 
latch: row cache objects         164,161      23,585    144    5.5 
latch: library cache lock        176,975      21,011    119    4.9 

So the problem was contention for library cache latch but what was causing it ?

The site had recently applied a patch to the application, so naturally that was carrying the blame – but the patch was relatively minor and did not seem like it could be responsible for the problems we were seeing.

So what are the most likely causes for contention on the library cache latch ?

Experience tells us that we should be looking for SQL statements that are not being reused (maybe using literals rather than bind variables) or possibly that the shared pool is simpy too big. The site in question had similar problems in the past due to poorly configured Automatic Shared Memory Management parameters that had allowed the shared pool to grow to over 10GB in size.

The shared pool was a reasonable size so the focus initially was on looking for SQL statements that weren’t being reused. Sure enough we found a couple of candidate statements that were showing a very high version count – statements with a high version count are reported in AWR and StatsPack reports but the script top10.sql is also useful for identifying them.

However the SQL statements we had identified were not new and were also showing similar high version counts at other sites who were not experiencing the same contention.

We needed to find out exactly what was causing the contention on the library cache latch – and I was under the impression that was going to be difficult.

Enter the frankly invaluable latchprof.sql script from Tanel Poder which does exactly that.

There’s even a handy guide that tells how to use it to troubleshoot latch contention – how had I not found this before ?

Running latchprof.sql against the database showed the following

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
library cache lock allocation       g78p6p30n7srb         87         11     .87     191.748      17.432

So that was pretty conclusive, the contention was down to one particular SQL statement – but what was it ?

Well it turns out this was the call to

dbms_session.reset_package

that is invoked by every request that is made to the Oracle Apex application – its a consequence of the Oracle Apex architecture.

There is simply no way we can stop the application from calling this routine – but why was it suddenly causing such crippling contention ?

To cut a long story short the customer had set the following database initialisation parameter

*.session_cached_cursors=75

Now on the face of it that doesn’t seem unreasonable – caching session cursors is a good thing isn’t it ?

Well actually when you’re dealing with an Oracle Apex application caching session cursors is largely irrelevant.

As we have already discovered Oracle Apex calls dbms_session.reset_package essentially for each page view recorded in the application, and one of the many things this routine does is to close all session cursors. All of which means that Oracle Apex applications will hardly ever reuse cursors, and you should be expecting to see a relatively high number of Soft Parses in your database.

Consequently the recommendation for our application has always been to stick with the default value for the session_cached_cursors parameter – at Oracle v10.2.0.5 this is 20, although it increases to 50 at Oracle v11.2.0.2.

The theory we were now operating under was that since each session in the Oracle Apex session pool was potentially caching up to 75 cursors, the repeated calls to dbms_session.reset_package were that much more expensive and were therefore resulting in the latch contention that we were seeing.

This fix was obvious – we needed to remove the session_cached_cursors parameter from the database and revert back to the recommended configuration for the Oracle Apex application.

After making that change the latch contention disappeared and performance at the site returned to normal – although the number of Soft Parses did increase!

The lessons to be learned here are

  1. Oracle Apex applications hardly ever reuse session cached cursors
  2. If you’re suffering from latch contention then you need latchprof!

Hope all that proves useful should you find yourself in a similar situation.  :)

4 thoughts on “Apex Library Cache Latch Contention”

  1. Thanks for this concise and nice explanation on how you’ve identified the performance problem and the setps you’ve conducted to solve this problem.

    Best regards

    Mohamed Houri

  2. Hi Steve,

    Thanks very much for this post. I followed it up with Tom Kyte who says mod_plsql can be configured to use
    dbms_session.modify_package_state(dbms_session.reinitialize) instead. This procedure does not close cursors.

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2588723819082#3317885600346106212

    I then asked about the APEX Listener on the OTN forum, and Colm Divilly answered: “APEX Listener uses dbms_session.modify_package_state(dbms_session.reinitialize) only.”

    So it really depends on how you access APEX: mod_plsql or the APEX Listener.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>