Use the SQL Tuning Advisor

Characteristics
- It analyses one or more SQL Statements.   
- You can run the SQL Tuning Advisor Manual or Automatically.
- You can specified that the analysis performed during the maintenance windows -automatically implements.

Capability Of SQL Tuning Advisor
- Statistics analysis: checking the statistics and recommend refreshing or creating them.
- SQL profiling: Collects auxiliary statistics on a SQL Statement and stores them in a SQL Profile.
- Access Paths:  Analyses the impact of creating a new indexes.
- Structure Analyses: Restructures the SQL statements to see whether better Execution             

Possible recommendation
- Gathering fresh object statistics.
- Creating a SQL profile.
- Creating additional indexes. 
- Creating a revised SQL statement

Enabling Automatic SQL Tuning

note: Automatic SQL tuning runs as part of the automated maintenance tasks infrastructure.
BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'sql tuning advisor', 
    operation => NULL, 
    window_name => NULL);
END;

/

Disabling Automatic SQL Tuning

note: you can run manually even if It is disable. 

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'sql tuning advisor', 
    operation => NULL, 
    window_name => NULL);
END;
/

Setting the automatic SQL tuning task to automatically accept SQL profiles recommended by the SQL Tuning Advisor
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
    task_name => 'SYS_AUTO_SQL_TUNING_TASK',
    parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');
END;
/

Viewing Automatic SQL Tuning Report
variable my_rept CLOB;
BEGIN
  :my_rept :=DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK(
    begin_exec => NULL,
    end_exec => NULL,
    type => 'TEXT',
    level => 'TYPICAL',
    section => 'ALL',
    object_id => NULL,
    result_limit => NULL);
END;
/
print :my_rept

The input for the SQL Tuning Advisor
- Automatic Database Diagnostic Monitor
- Automatic Workload Repository
- Cursor cache
- SQL Tuning Set

Use the SQL Tuning Advisor Manually
1. Create a SQL Tuning Set (if tuning multiple SQL statements)
2. Create a SQL tuning task
3. Execute a SQL tuning task
4. Display the results of a SQL tuning task
5. Implement recommendations as appropriate

Create a SQL Tuning Set
DECLARE
my_task_name VARCHAR2(30);
my_sqltext   CLOB;
BEGIN
my_sqltext := 'SELECT /*+ ORDERED */ * '                      ||
               'FROM employees e, locations l, departments d ' ||
               'WHERE e.department_id = d.department_id AND '  ||
                     'l.location_id = d.location_id AND '      ||
                     'e.employee_id < :bnd';
 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         sql_text    => my_sqltext,
         bind_list   => sql_binds(anydata.ConvertNumber(100)),
         user_name   => 'HR',
         scope       => 'COMPREHENSIVE',
         time_limit  => 60,
         task_name   => 'my_sql_tuning_task',
         description => 'Task to tune a query on a specified employee');
END;
/

Execute a SQL tuning task
BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
END;

/

Checking the Status of a SQL Tuning Task

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'my_sql_tuning_task';

Checking the Progress of the SQL Tuning Advisor

SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE user_name = 'HR' AND task_name = 'my_sql_tuning_task';

Displaying the Results of a SQL Tuning Task

SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task') FROM DUAL;

Accepting a SQL Profile

DECLARE
 my_sqlprofile_name VARCHAR2(30);
BEGIN
 my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( 
    task_name    => 'my_sql_tuning_task',
    name         => 'my_sql_profile',
    force_match  => TRUE);
END;

Altering a SQL Profile

BEGIN
  DBMS_SQLTUNE.ALTER_SQL_PROFILE(
     name            => 'my_sql_profile', 
     attribute_name  => 'STATUS', 
     value           => 'DISABLED');
END;

/

Dropping a SQL Profile
BEGIN
  DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');
END;
/

Packages

DBMS_SQLTUNE.CREATE_TUNING_TASK

DBMS_SQLTUNE.EXECUTE_TUNING_TASK

DBMS_SQLTUNE.REPORT_TUNING_TASK

Views

DBS_ADVISOR_LOG

DBA_ADVISOR_TASKS
V$ADVISOR_PROGRESS
Advisor Information 
DBA_ADVISOR_TASKS 
DBA_ADVISOR_EXECUTIONS 
DBA_ADVISOR_FINDINGS
DBA_ADVISOR_RECOMMENDATIONS 
DBA_ADVISOR_RATIONALE

SQL tuning information
DBA_SQLTUNE_STATISTICS
DBA_SQLTUNE_BINDS
DBA_SQLTUNE_PLANS
SQL Tuning Set 
DBA_SQLSET 
DBA_SQLSET_BINDS 
DBA_SQLSET_STATEMENTS
DBA_SQLSET_REFERENCES
DBA_SQLSET_PLANS
USER_SQLSET_PLANS
DBA_SQL_PROFILE
V$ADVISOR_PROGRESS

1. Creating Tuning Task 

variable vtask varchar2(100);

execute :vtask := dbms_sqltune.CREATE_TUNING_TASK(SQLSET_NAME=>'select max(ci) from agap.venezuela');

2. Executing Tuning Task

Execute dbms_tune.execute_tuning_task(:task); 

3. Creating the Tuning Report 

set long 10000

set longchunksize 10000

select dbms_sqltune.report_tuning_task(:vtask) from dual;

4.Cleaning vtask variable

undefine vtask