how to use SQL tuning advisor with SQL*Plus

because I’ve worked on a lot of environments where I only have ssh access, i cannot use graphical tools like Toad or SQL Developer… so I have to find a suitable way to tune sql statements with sql*plus… I’m just describing how to use the sql tuning advisor, how to find the correct sql_id will be another artikel.

TAKE CARE: you need the oracle diagnostic & tuning pack license!

ok, not the technical part… there are 3 steps: create a tuning task, where you define what oracle should do, execute the tuning task and show the result of the tuning task. Lets start with #1

define sql_id=abcdefghijkl
define begin_snap=1234
define end_snap=1235

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (                   
                          begin_snap  => &begin_snap,
                          end_snap    => &end_snap,
                          sql_id      => '&sql_id',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 7200, -- max runtime in seconds... so this means 2 hours
                          task_name   => '&sql_id._AWR_tuning_task');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

ok, now you have to execute the tuning task

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '&sql_id._AWR_tuning_task');

and when the task has finished, you can view the report with this sql statement:

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
column RECOMMENDATIONS format a200
-- optionally you can spool the output
-- spool sql_advisor_&sql_id._AWR_tuning_task.txt
SELECT DBMS_SQLTUNE.report_tuning_task('&sql_id._AWR_tuning_task') AS recommendations FROM dual;
-- spool off

if you want to accept a sql profile, the syntax to do so is withing the report, but are always the same…

exec dbms_sqltune.accept_sql_profile(task_name => '&sql_id._AWR_tuning_task', replace => TRUE);

if you want to undo (=remove) the sql profile, you just have to execute this:

exec DBMS_SQLTUNE.drop_sql_profile (name => '&sql_id._AWR_tuning_task');

you can drop the tuning task including the report (but without the profile) with this:

exec dbms_sqltune.drop_tuning_task(task_name => '&sql_id._AWR_tuning_task');

if you want to see the which sql tuning tasks exist, you can query the related dba view…

select task_name from DBA_ADVISOR_TASKS where advisor_name='SQL Tuning Advisor';

small hint: The automatic sql tuning advisor is running every night (if you haven’t disabled it). Internally it’s just a regular sql tuning task called ‘SYS_AUTO_SQL_TUNING_TASK’… you can get the report as described above (you just have to replace the task name) and see the recommendations of all automatically analysed sql statements within your AWR retention time… pretty cool to get an overview of slow sql.