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

Monitoring and Tuning the Shared Pool

One warning about the script in figure 7, the report it generates can run to several hundred pages for a user with a large number of SQL areas. What things should you watch for in a user's SQL areas? First, watch for the non-use of bind variables, bind variable usage is shown by the inclusion of variables such as ":1" or ":B" in the SQL text. Notice that in the example report in Figure 8 the first four statements use bind variables, and, consequently are reusable. Non-bind usage means hard coded values such as 'Missing' or '10' are used. Notice that for most of the rest of the statements in the report no bind variables are used even though many of the SQL statements are nearly identical. This is one of the leading causes of shared pool misuse and results in useful SQL being drown in tons of non-reusable garbage SQL.

The problem with non-reusable SQL is that it must still be looked at by any new SQL inserted into the pool (actually it's hash value is scanned). While a hash value scan may seem a small cost item, if your shared pool contains tens of thousands of SQL areas this can be a performance bottleneck. How can we determine, without running the report in Figure 7 for each of possibly hundreds of users, if we have garbage SQL in the shared pool?

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.