Creating SQL Tuning Task with sql_id

Creating SQL Tuning Task with sql_id
=============================

First get sql_id of sql those who try to create task

Hope as dba you all know how get sql_id. and here i take below sql-id to create task

sql_id= 2w3y1nfj1uanf

Note: remember in below red color sql_id change as per your id.

Step1: - Create task: 


SET serveroutput ON

DECLARE
  v_tune_taskid  VARCHAR2(100);
BEGIN
  v_tune_taskid := dbms_sqltune.create_tuning_task (
                          sql_id      => '2w3y1nfj1uanf',
                          scope       => dbms_sqltune.scope_comprehensive,
                          time_limit  => 30,
                          task_name   => 'sql_tuning_task_2w3y1nfj1uanf',
                          description => 'Tuning task sql_id 2w3y1nfj1uanf');
  dbms_output.put_line('taskid = ' || v_tune_taskid);
END;
/


PL/SQL procedure successfully completed.

successful mean task created

Step2: - verify task as you created.


SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_2w3y1nfj1uanf';


Step3:  Execute SQL Tuning task to get recommendation. 

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_task_2w3y1nfj1uanf');

Step4:  once complete run verify status of job. 

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_2w3y1nfj1uanf';


Step5:  Run report to get info. 

SET LINES 150
SET pages 50000
SET long 5000000
SET longc 5000000


SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_2w3y1nfj1uanf') AS recommendations FROM dual;

====================================================================
make below step recomdation task need to run or need if run recommendation
example jobs looks execute dbms_sqltune.accept_sql_profile
=====================================================================
Step6:  drop task once finish your recomdation run . 

BEGIN
  DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_2w3y1nfj1uanf');
END;

/