It works like this: When running a SQL statement, the session creates the statement's hash value, and then checks if that handle resides in its own cursor cache. Since no other process can access the session's cursor cache, no control structure is required. If the handle is found, the session knows the cursor exists in the cache. If the cursor is not found in the session cursor cache, the hash value is hashed to a library cache hash bucket, the appropriate control structure acquired, and then the chain is sequentially scanned, looking for the cursor. If the handle is found in the session's cursor cache, some effort has been expended parsing, but it's not as much as a hard parse (statement not found in the library cache) or even a soft parse (statement found in the library cache), and hence the term softer parse is used to describe this approach.

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.