Figure 7-11 is an example of what you may see when severe library cache latch contention exists. Notice that nearly 100% of the latch contention is related to the library cache! The load used to create this scenario was multiple sessions executing a tight PL/SQL loop, which simply opened and closed a cursor. Searching the library cache requires the library cache latch and opening a cursor requires the cursor to be pinned. As a result, we see both library cache latch types in heavy demand. Also interesting is that Oracle was consuming all available host CPU. Less then 1% of the CPU time was classified as parsing, but nearly 75% of the CPU was related to recursive SQL. If you recall back to the discussion in Chapter 5, any SQL with a depth greater than 0 is classified as recursive SQL, and the PL/SQL loop contents that were repeatedly executed will have a depth of 1 or more. When either recursive SQL time or parse time consume a significant amount of the available CPU, library cache-related latch contention is very common.

Figure 7-11. Shown is an example of severe library latch contention when mutexes are not used. While not shown, Oracle is consuming all the available host CPU, and because the load is based on a PL/SQL loop, recursive SQL is consuming over half of the Oracle CPU.

Figure 7-12 is based on the same load and interval as Figure 7-11. The only difference is library cache mutexes have been enabled by setting the instance parameter _kks_use_mutex_pin to true (which is actually the default). Notice the top wait event is cursor: pin S. This is the result of the cursor being repeatedly and intensely opened and closed.