SQL Tuning Advisor

The SQL Tuning Advisor takes one or more SQL statements as input to perform SQL tuning. Use of SQL Tuning Advisor

• Automatic Tuning Optimizer using  the SQL Tuning Advisor.
• Reactive Tuning Using the  SQL Tuning Advisor.

 Automatic Tuning Optimizer using  SQL Tuning Advisor: 

Oracle Database automatically runs the SQL Tuning Advisor on a selected high loaded SQL statements which is identified from AWR.

Reactive Tuning Using the  SQL Tuning Advisor:
We can also run the SQL Tuning Advisor selectively on a single or a set of SQL statements that have been identified as problematic. We run the SQL Tuning Advisor against the SQL statements or SQL_ID to improve the query performance.

Steps to SQL Tuning Advisor against SQL_ID:
I have one SQL_ID='8tf5zwqu4z40s'

 1. Create Tuning Task:

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '8tf5zwqu4z40s',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 500,
                          task_name   => '8tf5zwqu4z40s_tuning_task11',
                          description => 'Tuning task1 for statement 8tf5zwqu4z40s');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

 2. Execute Tuning task:

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '8tf5zwqu4z40s_tuning_task11');
/

 3. Get the Tuning advisor report:

select dbms_sqltune.report_tuning_task('8tf5zwqu4z40s_tuning_task11') from dual;
/

 4. Get list of tuning task present in database:

EXECUTE dbms_sqltune.drop_tuning_task('8tf5zwqu4z40s_tuning_task11');
/

 5. Drop a tuning task:

SELECT TASK_NAME, STATUS
FROM DBA_ADVISOR_LOG
WHERE TASK_NAME= '8tf5zwqu4z40s_tuning_task11';
/