Tuning Advice - Be Sure to Check the Bind Variables

An occasional problem I encounter is with queries doing too much work due to the bind variables used. When looking at queries performing a high number of reads, part of the investigation should include a review of all of the bind variables passed into the query.

Recall from older posts that the top queries can be identified in AWR/Statspack reports or by running a query against the data dictionary such as:

select buffer_gets, elapsed_time,cpu_time,executions,sql_id,sql_text
from (select buffer_gets, elapsed_time,cpu_time,executions,sql_id,sql_text, rank() over
(order by buffer_gets desc
) as rank from v$sqlstats
where last_active_time > (sysdate - 1/12)
) where rank <=20;

A trace with binds and waits will give you information about the bind variables that were used for a query. You can also query v$sql_bind_capture. If you have the appropriate license you can query dba_hist_sqlbind to get the bind variables for older queries.

select child_number,position,name,datatype_string,value_string 
from v$sql_bind_capture 
where sql_id=&SQL_ID_from_1
order by child_number,position;

When the bind variables have been identified, check the occurrences of those values in the table columns being queried. A value that is heavily skewed could represent a programming issue if too much data is being read or retrieved. Also any date variables that are causing the query to read multiple years worth of data should be reviewed.

Sometimes the fix to a query performing high reads is a programming change to alter how those queries are used. If bad variables are used against a query, then you will be judged for the performance issues created.

The Smith family seeing a query using bad variables: