One attempt is particularly humorous. Back in Oracle 7 days, a shared pool patch was released with the intention of improving parse times. In particular, the Oracle developers decided to hash only on the object's first 60 characters. So a 65-character statement or a 6,500-character statement would take the same amount of hash value generation time. This is equivalent to altering the hash function shown in Figure 7-8 and considering only the first five characters. If this were the case, since all three statements shown in Figure 7-8 have the same first five characters (selec), they would each generate the same hash value and be mapped to the same hash bucket. The hash chain would be three objects long. Now translate this back to Oracle's attempt at considering only the first 60 characters while hashing.

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.