Library Cache Latch Contention and Scalability

Lot has been written about Library Cache Latch Contention and one of the primary reason for this being "Too many Hard / Soft Parses". I have couple of blogs on this issue that was due to improper OS settings. There are many White papers that mention about this contention and all of these primarily state about LC Latches due to Hard or Soft Parses. To get a good conceptual knowledge on this contention asktom.oracle.com would be an ideal place where Tom Kyte mentions the steps that occur during Hard and Soft Parses and the issues due to these. All these paper refer Library Cache Latch contention as a Scalability Issue.

Why is Library Cache Latch Contention reffered to as a "Severe Scalability Issue" ?

During an Investigation of one such performance issue, I noticed that Library Cache Latches contributed to the major portion of Waits and these were due to the high number of Hard Parses. During peak load, I executed a query to check the value of Hard Parses.

select to_char(sysdate,'DD-Mon-YYYY HH24:MI:SS') s_date, name, value from v$sysstat where name like 'parse%';

S_DATE NAME VALUE
-------------------- --------------------------- ----------
08-Oct-2008 11:20:13 parse time cpu 728655
08-Oct-2008 11:20:13 parse time elapsed 1525274
08-Oct-2008 11:20:13 parse count (total) 60540369
08-Oct-2008 11:20:13 parse count (hard) 1471528
08-Oct-2008 11:20:13 parse count (failures) 1742

Elapsed: 00:00:00.80

SQL> /
S_DATE NAME VALUE
-------------------- --------------------------- ----------
08-Oct-2008 11:20:38 parse time cpu 729434
08-Oct-2008 11:20:38 parse time elapsed 1526035
08-Oct-2008 11:20:38 parse count (total) 60604181
08-Oct-2008 11:20:38 parse count (hard) 1473347
08-Oct-2008 11:20:38 parse count (failures) 1742

The time difference in the execution of these two is 25 seconds. Therefore, if we get the difference of "parse count (hard)" and divide by 25, the Hard Parse / Second would come out to 73 per second and this is too high. Further drilling down, we could see many queries that were similar textually but differed due to Literals and therefore were considered as different sql's. Each time these queries were executed, due to the difference in Literals, these were Hard Parsed.

Hard Parsing is very costly as the query goes through each and every steps, from semantics / syntatic checks to the Execution Step and this involves acquisition of latch for longer duration. Latches are to be held for a brief period, but if these latches are held frequently, and that too, for a longer duration, then the number of waits for these latches can increase. These waits are posted as either : "Latch Free" or "Latch: Library Cache" (since we are discussion about Library Cache Latch).

Soft Parsing is cheaper than Hard Parsing, but also involves considerable amount of processing in the Oracle Server.

In case of Library Cache Latches, we have limited number of latches. This defaults to the next prime number to the number of CPU's on the database server and the max value can go upto 67. Therefore, even if the database server has more than 67 CPU's, the number of Library Cache Child latches would be restricted to 67.

With these numbers, in a concurrent environment, with thousands of users connected and each executing different sql's, the latches can be held concurrently by limited numbers with a long queue waiting for the latches to get release and this is where the term Scalability comes into picture. No matter how many users you add, but the Oracle engine will only be able to process the request for limited number of users at one time, while others have to wait. This wait can have a huge impact on the overall performance and that can result in overall slowness of the production system, thus the term Scalability. You reduce the number of users, the waits on these latches will come down thereby improving the performance.

Therefore, utmost care should be taken if the application does too much of Hard or Soft Parses as it involves :

• CPU overhead,
• Library Cache Latch Contention
• Impact on Shared Pool effeciency

Every latch contention involves CPU Overhead, but with Library Cache Latches, as mentioned earlier, the limitation on the number of Child Latches affects the Scalability and therefore "Library Cache Latch Contention", specifically, is termed as a Scalability Issue. Oracle introduced CURSOR_SHARING from Oracle 8i onwards (8i=>FORCE and 9i onwards=>FORCE & SIMILAR) that take care of Literals and forcefull convert them into bind variables, but each of these values involve further CPU processing and have its own pros and cons.

This question was asked to me in a technical event, where I was actually presenting on Cost Based Optimizer and was discussing about Histograms and Bind Variables.

Next, I do intend to write on "Histograms and Bind Variables", but there are many papers on this topic and I need to evaluate if I can write something unique that is not much discussed so far.