Automatic SQL Tuning

创建调优任务：

DECLARE
  sql_tune_task  VARCHAR2(100);
BEGIN
  sql_tune_task := DBMS_SQLTUNE.create_tuning_task (
            sql_id      => '2xdbrck0jer43',
            scope       => 'COMPREHENSIVE',
            time_limit  => 60,
            task_name   => '2xdbrck0jer43_tuning_task',
            description => 'Tuning 2xdbrck0jer43.');
  DBMS_OUTPUT.put_line('sql_tune_task: ' || sql_tune_task);
END;
/      

​参数说明：

time_limit：执行时间限制，默认是60s。

scope：

LIMITED，优化SQL语句不进行SQL Profiling分析。

​

​COMPREHENSIVE，进行全面分析，包含SQL Profiling分析；比LIMITED用时更长。​

执行调优任务：

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '2xdbrck0jer43_tuning_task');      

查看调优任务：

SELECT task_name, status FROM dba_advisor_log WHERE task_name like '2xdbrck0jer43_tuning_task';      

显示调优结果：

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('2xdbrck0jer43_tuning_task') AS recommendations FROM dual;      

BEGIN
  DBMS_SQLTUNE.drop_tuning_task (task_name => '2xdbrck0jer43_tuning_task');
END;
/