Using the DBMS_SQLTUNE Package to Run the SQL Tuning Advisor

You can create tuning tasks from the following:

>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.


#Get the snap id using below

select min(snap_id),max(snap_id) from DBA_Hist_SQLStat where SQL_ID ='&SQL_ID';

SET SERVEROUTPUT ON
DECLARE
L_SQL_TUNE_TASK_ID VARCHAR2(100);
BEGIN
L_SQL_TUNE_TASK_ID := DBMS_SQLTUNE.CREATE_TUNING_TASK (
BEGIN_SNAP=>30039,
END_SNAP=>30040,
SQL_ID => '03kc2mrhf3xj1',
SCOPE => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
TIME_LIMIT => 3600,
TASK_NAME => '03kc2mrhf3xj1_task',
DESCRIPTION => 'Tuning task for 03kc2mrhf3xj1');
DBMS_OUTPUT.PUT_LINE('l_sql_tune_task_id: ' || L_SQL_TUNE_TASK_ID);
END;
/

or

SET SERVEROUTPUT ON declare stmt_task VARCHAR2(40); begin stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '03kc2mrhf3xj1'); DBMS_OUTPUT.put_line('task_id: ' || stmt_task ); end; /


#Execute the tuning task.:

Execute dbms_sqltune.Execute_tuning_task (task_name => '03kc2mrhf3xj1_task');


#Check the status of the task using following query:

select status from dba_advisor_log where task_name='03kc2mrhf3xj1_task';


#View the Recommendations

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


Reference Doc ID 262687.1