Figure 7-9 shows multiple runs of the hash_text.pl script. Look very closely at the differences in the SQL text. While each statement is syntactically identical, there is a single character difference in each statement. The second statement has a capital T, and the third statement simply has an additional space after the select. Based on this hash value function and the hash function, this seemingly insignificant difference was enough to produce different hash values and also different hash bucket assignments. This is why Oracle SQL must be textually exact, or a new cursor will be created for each textually unique statement. This is important for developers to understand, because unless they are told, it is highly unlikely they would assume even the statement spacing must be the same.

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.