Into the Shared Pool - An In Depth Look at Tuning the Shared Pool (Part 2)
Oracle Tips by Mike Ault
Long statements require special care to see
that bind variables are used to prevent this problem with hashing.
Another help for long statements is to use views to store values at
an intermediate state thus reducing the size of the variable portion
of the SQL. Notice in the example select in Figure 8 that the only
difference between the two identically hashed statements is that the
“region_code” and “region_dealer_num” comparison values are
different, if bind variables had been used in these statements there
would only have been one entry instead of two.
Guideline 6: Use bind variables, PL/SQL
(procedures or functions) and views to reduce the size of large SQL
statements to prevent hashing problems.
||If you like
Oracle tuning, you may enjoy the book
Oracle Tuning: The Definitive Reference
, with over 900 pages of BC's favorite tuning tips &
You can buy it directly from the
publisher and save 30%, and get instant access to the code
depot of Oracle tuning 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.