An Oracle SQL Tuning Task is a logical container used by Oracle Database's built-in SQL Tuning Advisor to analyze and optimize specific SQL statements.
When you create a tuning task, you register one or more SQL statements (retrieved from memory, the Automatic Workload Repository (AWR), or entered manually as text) to be evaluated. When executed, the database analyzes the execution path, identifies performance bottlenecks, and generates a detailed optimization report.
CREATE TUNING TASK
DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_id => '5mn47p0gv2q83', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 4000, task_name => 'Task_5mn47p0gv2q83',
description => 'Manual Tuning on 06 SEP 16'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; /
EXECUTE TUNING TASK
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'Task_5mn47p0gv2q83');
CHECK STATUS OF TASK
SELECT task_name, status FROM dba_advisor_log WHERE task_name = 'Task_5mn47p0gv2q83';
CHECK RECOMMENDATION
SET LONG 10000;
SET PAGESIZE 1000;
SET LINESIZE 1000;
SELECT DBMS_SQLTUNE.report_tuning_task('Task_5mn47p0gv2q83') AS recommendations FROM dual;