The good news is that library cache contention can be significantly reduced by increasing every session's cursor cache. The bad news is that indeed every session's cursor cache is increased. If the Oracle instance has hundreds of sessions, together all the session cursor caches can require enough memory to cause shared pool memory availability issues. You'll know when you've gone too far, because you'll start receiving 4031 "out of memory" errors. At this point, either reduce the session cache cursor size or, if there is memory available, increase the shared pool size. So, as with nearly every tuning effort and parameter, there is a cost. As performance analysts, our hope is that the cost is less than the performance benefit.

As the library cache becomes increasingly active, competition for the control structures and potentially the time holding the control structures can increase so much that it becomes a serious performance issue. It will become obvious when this occurs because our response-time analysis will clearly point to a library cache latch- or mutex-related wait event(s). Furthermore, there will be significant Oracle CPU consumption, with an unusual amount of recursive SQL or parse-related time. The operating system will be ravaged by a CPU bottleneck. Fortunately, there are several very good solutions to this problem.

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.