While hash value creation may seem quick, every time a SQL statement is executed, its cursor must be searched and found, so the hash value creation function will be called a massive number of times each minute. This will eventually add up to considerable CPU consumption. While simple statements can be hashed quickly and with little resource consumption, real-life 5,000-line SQL statements will take a relatively long time. So Oracle will make every effort to optimize the hash value creation.

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.