Oracle 10G SQL Tuning Advisor

Over the 9 months or so I have been supporting a datawarehouse which has been running on 10G R1. The experience has been fun, given the new features and strange in other respects (see my post about PARALLEL_AUTOMATIC_TUNING behaviour.

Oracle 10G comes with a new PL/SQL package called DBMS_SQLTUNE, which is the API or command line way of running the SQL Tuning advisor, instead of through Oracle EM GUI.

Sometimes SQL or PL/SQL is complex, running to many lines and in some cases involving several levels of nested inline views. It can take time just to figure out what the SQL is trying achieve, before you can actually start to review if there are any potential tuning opportunities.

Enter SQL tuning advisor, in a nutshell it takes sql, either in text, referenced by SQL_ID or from a snapshot or captured source and does a more indepth search for a better execution plan.

If you want a quick analysis to kick off the tuning exercise this tool is probably to best spot to start.
I have added some simple scripts to run SQL Tuning advisor from the sqlplus command line at the end of this post. I used similar scripts to produce some analysis in which the advisor found a better execution plan (which I verified). It reduced the running time substantially.
DBMS_SQLTUNE has had extra work in Oracle 10G R2 and has lots of additional features.

Have Fun

Paul

### Cut here ###
-- Script to create tuning task and automatically run tuning task using default settings
-- Input: sql_id captured from v$sql or history tables
-- PM 2006/03/04

set serveroutput on size 80000

declare v_taskname varchar2(50);
begin
v_taskname := dbms_sqltune.create_tuning_task(sql_id=>'&sql_id');
dbms_output.put_line('TaskName: '||v_taskname);
dbms_sqltune.execute_tuning_task(v_taskname);
end;
/

### End of script ###

### Cut Here ###

-- Script to report results from tuning task
-- Input: TaskName
-- Modify linesize or chunksize to sort required output width.
-- PM 2006/03/04

set long 8000
set long_chunksize 200
set linesize 200
set pagesize 3000

spool sqltune_report;
select dbms_sqltune.report_tuning_task('&task_name') from dual;
spool off;
### End of script ###