Automated SQL Tuning: Generating a SQL Script to Implement Automatic Tuning Advice

Problem
You’ve reported on the automatic tuning advice. Now you want to generate a SQL script that can be used to implement tuning advice.
Solution
Use the DBMS_SQLTUNE.SCRIPT_TUNING_TASK function to generate the SQL statements to implement the advice of a tuning task. You need to provide as input the name of the automatic tuning task. In this example, the name of the task is SYS_AUTO_SQL_TUNING_TASK:

SET LINES 132 PAGESIZE 0 LONG 10000

SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK('SYS_AUTO_SQL_TUNING_TASK') FROM dual;

Here is a small snippet of the output for this example:

execute dbms_stats.gather_index_stats(ownname => 'STAR2', indname => 'F_CONFIG_P

ROD_INST_FK1'

, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

create index NSESTAR.IDX$$_17F5F0004 on NSESTAR.D_DATES("FISCAL_YEAR","FISCAL_WE

EK_NUMBER_IN_YEAR","DATE_DTT");

How It Works
The SCRIPT_TUNING_TASK function generates the SQL to implement the advice recommended by the Automatic SQL Tuning job. If the tuning task doesn’t have any advice to give, then there won’t be any SQL statements generated in the output. SYS_AUTO_SQL_TUNING_TASK is the default name of the Automatic SQL Tuning task. If you’re unsure of the details regarding this task, then query the DBA_ADVISOR_LOG view

select task_name, execution_start from dba_advisor_log

where task_name='SYS_AUTO_SQL_TUNING_TASK'

order by 2;

Here’s some sample output for this example:

TASK_NAME                      EXECUTION

------------------------------ ---------

SYS_AUTO_SQL_TUNING_TASK       19-APR-11