SQL Tuning Advisor - Expert Indexing in Oracle Database 11g - page 207

Now use the DBMS_SQLTUNE.SCRIPT_TUNING_TASK function to generate the SQL statements to
implement the advice of a tuning task. In this example, the name of the task is
SYS_AUTO_SQL_TUNING_TASK .

SQL> SET LINESIZE 132 PAGESIZE 0 LONG 10000
SQL> SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK('SYS_AUTO_SQL_TUNING_TASK')
FROM dual;

For this database, the output is an index creation script.

create index STAR2.IDX$$_17F5F0001
on STAR2.D_PRODUCT_INSTANCES("CREATE_DTT","D_PRODUCT_INSTANCE_ID");

If the tuning task doesn't have any advice to give, there won't be any SQL statements generated in
the output. Before creating an index in a production environment, you should test whether or not an
index actually increases performance and doesn't have any adverse impacts on the performance of other
SQL statements. Also consider adjusting the index name, storage, and tablespace placement as per your
standards.

Disabling and Enabling Automatic SQL Tuning
You might desire to disable the automatic SQL tuning job because you have a very active database and
want to ensure that this job doesn't impact the overall performance of the database. The
DBMS_AUTO_TASK_ADMIN.ENABLE/DISABLE procedures allow you to turn on and off the automatic SQL
tuning job. These procedures take three parameters (see Table 9-2 for details). The behavior of the
procedures varies depending on which parameters you specify.

If CLIENT_NAME is provided and both OPERATION and WINDOW_NAME are NULL , then the
client is disabled.

•

If OPERATION is provided, then the operation is disabled. •

If WINDOW_NAME is provided, and OPERATION is NULL , then the client is disabled in the
provided window name.

•

These parameters allow you to control at a granular detail the schedule of the automatic task. Given
the prior rules, you would disable the automatic SQL tuning job during the Tuesday maintenance
window as follows:

BEGIN
dbms_auto_task_admin.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => 'TUESDAY_WINDOW');
END;
/

You can verify that the window has been disabled via this query:

SELECT window_name,TO_CHAR(window_next_time,'DD-MON-YY HH24:MI:SS')
,sql_tune_advisor
FROM dba_autotask_window_clients;