Diving Into the Shared Pool - An In Depth Look at Tuning the Shared Pool (Part 2) Oracle Tips by Mike Ault

A Matter Of Hashing

We have discussed hashing in prior sections, essentially each SQL statement is hashed and this hash value is then used to compare to already stored SQL areas, if a matching hash is found the statements are compared. The hash is only calculated based on the first 200 or so characters in the SQL statement, so extremely long SQL statements can result in multiple hashes being the same even though the stored SQL is different (if the first 100 or so characters in each statement are identical). This is another argument for using stored procedures and functions to perform operations and for the use of bind variables. There is hope, in 8i  (or 8.1 if you prefer) the hash value will be calculated on the first 100 and last 100 characters reducing the chances of multiple identical hash values for different SQL statements.

If the number of large, nearly identical statements is high, then the number of times the parser has to compare a new SQL statement to existing SQL statements with the same hash value increases. This results in a higher statement overhead and poorer performance. You should identify these large statements and encourage users to re-write them using bind variables or to proceduralize them using PL/SQL. The report in Figure 4 will show if you have a problem with multiple statements being hashed to the same value.

SEE CODE DEPOT FOR FULL SCRIPTS

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.