When and Where HASH VALUE for a given SQL
statement get generated? I need to understand how hashing
works within the library cache and how Oracle implements hashing.
Answer: When a SQL
statement first enters Oracle, several steps take place:
1 - The SQL is standardized in capitalization, so
upper and lower case do not hash to a different value
2 - Oracle then feeds this SQL into their hashing
algorithm to generate a unique RAM address in the library cache.
The hash is built to avoid collisions (two statements generating to
the same address) and it will always find a space within the library
cache. The hashing algorithm is a proprietary secret, but it
works very fast, using a SQL statement to generate a RAM address in
just a few microseconds.
3 - Oracle then goes to the location indicated by
the hash and if the statement already exists, Oracle will re-use the
SQL statement, a "soft" parse. Else, the SQL statement is
loaded into this RAM location, and the SQL statement is processed as
a "hard" parse.
Also see my notes on
SQL statements for more details on the internals of Oracle
hashing of SQL.
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.