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;
/