Gather Data for SQL tuning

This post will not focus on how to tune SQL statments. There is already a wealth of information about tuning practices. Plus there are many fine tools available such as Profiler that will assist with tuning SQL statements.

I'll give an overview on identifying SQL statements that can benefit from tuning efforts. For this approach I will only assume the use of SQLPlus. These steps are much easier with the appropriate tools, but if you are in a situation where the tools are not available then it is important to know the basic steps to gather information.

The key steps to follow are:

• Use Applied Analytics part 1 to find top sql
• Use additional queries to get information about calling programs
• Use v$sql_bind_capture to get sample bind data
• Use autotrace with SQL Plus to get plan and execution information.

Details:
1.
Use the following query to get the top SQL statements by Logical Reads for the past couple of hours. This is outlined in my post Applied Anayltics part 1

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;

2.
Use the following query to get information about who is calling the SQL statement. This is an important element of troubleshooting. Sometimes you may identify poor statements that are actually program bugs. Getting the calling information can help identify if the statements should even be running at all.

select parsing_schema_name, service,module,action,program_id,program_line#
from v$sql
where sql_id=&SQL_ID_from_1;

3.
If the poor performing statement has bind variables, then you need to see what values are being passed to the statement. Getting this information will allow you to test some real examples of the query.

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;

4.
Run the query in an SQLPlus session with Autotrace set on. This will return an explain plan and execution results after the query finishes.

If the query returns with a good performance, then check against other bind variables. It could be that only certain values result in a poor execution. Also check the execution count. If a query has a small individual impact however the statement is an overall high consumer, then it could be due to an execptionally high number of executions.

If the individual execution demonstrates a performance problem, then steps can be begun to tune the specific SQL.