Figure 7-9. Shown is the result of three syntactically exact SQL statements, which yield different hash values and also hash bucket assignments.

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.