SQL Tuning Advisorタスクを作成・実行し、推奨事項を確認

コマンド

-- 1) Create and run a task for a SQL in the cursor cache:
VAR task VARCHAR2(64)
BEGIN
  :task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
             sql_id      => '<sql_id>',
             scope       => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
             time_limit  => 300,
             task_name   => 'STA_<sql_id>',
             description => 'Slow report query');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => :task);
END;
/
-- For a historical SQL in AWR: begin_snap => <b>, end_snap => <e>, sql_id => '<sql_id>'

-- 2) Status and report:
SELECT task_name, status FROM dba_advisor_log WHERE task_name = :task;

SET LONG 1000000 LONGCHUNKSIZE 1000000 LINESIZE 200 PAGESIZE 0
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:task) FROM dual;

-- 3) Get the recommendations as an executable script:
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:task) FROM dual;

-- 4) When done:
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => :task);

概要

SQLに対してオプティマイザを拡張「チューニングモード」で実行し、不足している統計、新しいインデックス、SQLプロファイル、代替計画、SQLの再構成に関する推奨事項を生成する。プロファイルの推奨はテスト実行で検証する。

使用する場面

問題のある単一SQLについて素早くセカンドオピニオンが必要なとき、またはコードを変更せずに計画劣化をSQLプロファイルで修正したいとき。

注意

Tuning Packライセンスが必要。COMPREHENSIVEスコープはテストのためにSQLを実行する場合がある(DMLではクエリ部分のみ)。本番ではtime_limitを設定する。インデックスの推奨は他のワークロードを考慮しないため、DMLコストは別途評価する。ユーザーにはADVISOR権限が必要。