To execute manual SQL Tunning Advisor

 

1. Create task for manual SQL Tuning Advisor

     
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '5sv842hgxk4az',--set a sql_id for that query 
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 500,--set a time limit in seconds
                          task_name   => '5sv842hgxk4az_tuning_task',
                          description => 'Tuning task1 for statement 5sv842hgxk4az');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
  

2. Execute task

  
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '5sv842hgxk4az_tuning_task'); 
 

3. View the task report

select dbms_sqltune.report_tuning_task('5sv842hgxk4az_tuning_task') from dual;

4. If task executed from other user & need to View the task report

select dbms_sqltune.report_tuning_task('SQL_TUNING_1595581407932',owner_name=> 'username') from dual;

5. To drop the task

EXEC DBMS_SQLTUNE.drop_tuning_task(task_name => '56n144hn5bdm0_tuning_task'); 

6. All task details

select * from dba_advisor_log where owner='username' ;