Most statements are unique near the tail end of their text, because that is where the filtering and bind variable assignment take place. As a result of this patch, thousands of SQL statements generated the same hash value and were then mapped to the same hash bucket! Library cache hash chains became huge, resulting in massive library cache latch contention. The intense cursor creation and destruction also created significant shared pool latching contention. Performance immediately took a significant plunge, and a patch for the patch was quickly released. This is a classic example of testing code in a nonproduction-like situation and rubber-stamping the code ready for release. Why this patch caused problems was a mystery to me until I understood a hashing structure was used to locate objects in the library cache.

It should be clear now that building a cursor is a relatively expensive operation. The CPU consumption and possible IO to bring objects into the library cache can significantly slow performance. This typically manifests into increased parsing CPU consumption and, in particular, the library cache latch or mutex becoming the top wait event. So one obvious objective is to keep cursors in the library cache. However, a balance must be maintained, or other performance-inhibiting issues will arise. The shared pool must contain many types of objects, and library cache objects are just one of those types. Plus memory is a limited resource. The following subsections discuss a variety of ways to influence Oracle to keep cursors in the cache.

Oracle cannot deallocate open cursors. Even if the shared pool is flushed, open cursors are pinned and therefore cannot be deallocated. Normally, when cursor execution is complete, the cursor is closed, the cursor pin is removed, and if there are no other sessions pinning the cursor, Oracle can deallocate the associated memory. This allows for newer and active cursors to remain in memory while less active cursors are naturally deallocated. But if parsing becomes a significant performance problem, as performance analysts, we become motivated to influence Oracle to keep the cursors in memory. One of the ways to do this is keep the cursors open.