Using the SQL TUNING ADVISOR (STA)

SQL Tuning Advisor analyzes candidate SQL statements, and execute a complete 
analysis of the statement including: 
 - Determining stale or missing statistics
 - Determining better execution plan
 - Detecting better access paths and objects required to satisfy them (indexes, materialized views)
 - Restructuring SQL

While the primary interface for the SQL Tuning Advisor is the Oracle Enterprise Manager Database Control, 
the advisor can be administered with procedures in the DBMS_SQLTUNE package. To use the APIs the user must 
have been granted the DBA role and the ADVISOR privilege. If using the SQL Tuning Advisor in Oracle Enterprise
Manager, the user must have been granted the select_catalog_role role.

Running SQL Tuning Advisor using DBMS_SQLTUNE package is a two-step process:
1. Create a SQL tuning task
2. Execute a SQL tuning task

Note: starting with Oracle 10.2 there is a script ORACLE_HOME/rdbms/admin/sqltrpt.sql which can be used for 
usage of SQL Tuning Advisor from the command line

DO IT YOURSELF

You can use STA through the PL/SQL API and query the various advisory views in SQL*Plus to examine how to 
solve performance issues.

The example is based on the SH account executing the various tasks. To 
allow SH user to both create task and execute it user SH needs to be granted proper access:
        CONNECT / AS SYSDBA
        GRANT ADVISOR TO SH;
        GRANT SELECT_CATALOG_ROLE TO SH;
        GRANT EXECUTE ON DBMS_SQLTUNE TO SH;

The example presented makes use of a table called SALES, residing in the 
SH schema. The table Is not analyzed.

I)  First step is to create the tuning task. 

 You can create tuning tasks from the following:
Connect sh/sh
-  SQL statement selected by SQL identifier from the cursor cache
-  SQL Tuning Set containing multiple statements
-  Text of a single SQL statement
-  SQL statement selected by SQL identifier from the Automatic Workload Repository.

DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext CLOB;
BEGIN
  my_sqltext := 'SELECT * '   ||
                'FROM sales ' || 'WHERE prod_id = 10 AND ' || 'cust_id = 100 ';

  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                           sql_text => my_sqltext,
                           user_name => 'SH',
                           scope => 'COMPREHENSIVE',
                           time_limit => 60,
                           task_name => 'TEST_sql_tuning_task',
                           description => 'Task to tune a query on a specified PRODUCT');
END;

User_name: User under which the CREATE_TUNING_TASK function analyzes the SQL statement.
Scope: COMPREHENSIVE which means that the advisor also performs SQL Profiling analysis 
Time_limit: Time in seconds that the function can run. 

The CREATE_TUNING_TASK function returns the task name that you have provided 
or generates a unique task name. You can use the task name to specify this task
when using other APIs. To view the task names associated with a specific owner,
you can run the following:

select task_name from dba_advisor_log where owner='SH'; 

II)  Execute the tuning task.

Begin
dbms_sqltune.Execute_tuning_task (task_name => 'TEST_sql_tuning_task');
end;

OR

Execute dbms_sqltune.Execute_tuning_task (task_name => 'TEST_sql_tuning_task'); 

III)  You can check the status of the task using following query:

select status from dba_advisor_log where task_name='TEST_sql_tuning_task';

iV)  View the Recommendation


set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('TEST_sql_tuning_task') from dual;