Automatic SQL Tuning Advisor

oracle
Automatic SQL Tuning Advisor
============================
1.create a new tuning task
2.execute_tuning_task
3.Report of the Tunning advisory


first step when using the SQL tuning advisor is to create a new tuning task
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
SET SERVEROUTPUT ON

-- Tuning task created for specific a statement from the AWR.
-----------------------------------------------------------------------------
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 764,
                          end_snap    => 938,
                          sql_id      => '19v5guvsgcd1v',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '19v5guvsgcd1v_AWR_tuning_task',
                          description => 'Tuning task for statement 19v5guvsgcd1v in AWR.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created for specific a statement from the cursor cache.
-----------------------------------------------------------------------------
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '19v5guvsgcd1v',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'sql_tuning_task',
                          description => 'Tuning task for statement sql.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created from a SQL tuning set.
-----------------------------------------------------------------------------
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sqlset_name => 'test_sql_tuning_set',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'sqlset_tuning_task',
                          description => 'Tuning task for a SQL tuning set.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created for a manually specified statement.
-----------------------------------------------------------------------------
DECLARE
  l_sql               VARCHAR2(500);
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql := 'SELECT e.*, d.* ' ||
           'FROM   emp e JOIN dept d ON e.deptno = d.deptno ' ||
           'WHERE  NVL(empno, ''0'') = :empno';

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_text    => l_sql,
                          bind_list   => sql_binds(anydata.ConvertNumber(100)),
                          user_name   => 'scott',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'emp_dept_tuning_task',
                          description => 'Tuning task for an EMP to DEPT join query.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

--execute_tuning_task:
-----------------------------------------------------------------------------
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '3t70pmjyf8c16_AWR_tuning_task');

--Completed Report of the Tunning advisory
-------------------------------------------
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('3t70pmjyf8c16_AWR_tuning_task') AS recommendations FROM dual;
SET PAGESIZE 24


-- Reset a tuning task allowing it to be re-executed.
-----------------------------------------------------------------------------
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'sqlset_tuning_task');

---tuning session is over the tuning task can be dropped using the DROP_TUNING_TASK procedure.
-----------------------------------------------------------------------------
BEGIN
  DBMS_SQLTUNE.drop_tuning_task (task_name => 'sqlset_tuning_task');
END;
/