DBMS_SQLTUNE | SQL Tuning Task using SQL_ID

--- SQL Tuning Task using SQL_ID

--- first capture Highest SQL using following Query for last 60 minutes.

select *

  from (select s.BUFFER_GETS, s.DISK_READS ,nvl(s.sql_id,'null') as sql_id , Nvl(S.sql_text, 'NULL') ASSQL_text,

               round(COUNT(*) / 60, 2) DB_TIME,

               ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER(), 2)AS PCT_LOAD

          FROM V$active_Session_History A, v$SQL S

         WHERE A.SQL_ID = S.SQL_ID

           AND SAMPLE_TIME > SYSDATE - 60 / 24 / 60  

           AND SESSION_TYPE <> 'BACKGROUND'

         GROUP BY s.sql_id, s.SQL_text, s.BUFFER_GETS,s.DISK_READS

         ORDER BY COUNT(*) DESC)

 where rownum <= 1;

 --- notedown sql_id from above command. e.g 3hww0a8at1tnv

 --- now execute following  plsql block for sql tuning using sql_id and place your sql_id in sql_id parameter

declare

stmt_task VARCHAR2(100);

begin

stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id =>'3hww0a8at1tnv');

DBMS_OUTPUT.put_line('task_id: ' || stmt_task );

end; 

--- notedown  task_id from above plsql block. e.g TASK_18790

--- task is created, now execute task using following.

begin  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name =>'TASK_18790'); end;

--- queries related to TASK.

select * from v$advisor_progress x  ;

SELECT task_name, status FROM DBA_ADVISOR_LOG;

-- generate report using following

set long 100000

set longchecksize 10000

set pages 0

set lines 250

SELECT DBMS_SQLTUNE. REPORT_TUNING_TASK('TASK_18789') ASrecommendations FROM dual;

as always its better to follow ORACLE documentation for complete understandby refer following link.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sqltun.htm