Step 1: Get the hash value of the SQL using the SQL ID.
Enter the SQL ID
select a.plan_hash_value from v$sql a where sql_id=’&a’;
Step 2: Create a tuning task using SQL ID and plan hash value.
DECLARE
stmt_task VARCHAR2(64);
BEGIN
stmt_task:= sys.dbms_sqltune.create_tuning_task(sql_id => ‘1f1qmwyx0ntha’, plan_hash_value => ‘3898855966’,
time_limit => 120, task_name => ‘Tune_Support_WCC_01’, description => ‘Task to tune for WCC’);
END;
/
Step 3: Execute the tuning task using the below command.
EXECUTE dbms_sqltune.execute_tuning_task(‘Tune_Support_WCC_01’);
Step 4: Get the output of the Query.
spool sta.out.frgbkr3638h02
set long 30000 pages 1000 lines 1000
VARIABLE my_rept CLOB;
begin
:my_rept := dbms_sqltune.report_tuning_task(‘Tune_Support_WCC_01′,’TEXT’, ‘ALL’);
end;
/
PRINT :my_rept
spool off
Step 5: Drop the tuning task once it is completed.
BEGIN
DBMS_SQLTUNE.drop_tuning_task (task_name => ‘Tune_Support_WCC_01’);
END;
/
