How to Create/Execute/Drop Tuning Tasks

 Oracle Tuning Advisor is a tool that can help DBA's to improve certain SQL queries. Here how you can use them:

CREATE 

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '238djik018ja1',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 600,
task_name => '238djik018ja1_tuning_task',
 description => 'Tuning task for sql id 238djik018ja1');
 DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

EXECUTE

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '238djik018ja1_tuning_task');

GETTING REPORT

select dbms_sqltune.report_tuning_task('238djik018ja1_tuning_task') from dual;

QUERY TASKS

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG;

DROP

execute dbms_sqltune.drop_tuning_task('238djik018ja1_tuning_task');