SQL計画ベースラインの計画を固定、無効化、削除

コマンド

-- Show the plan content:
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
         sql_handle => '<sql_handle>',
         plan_name  => '<plan_name>',
         format     => 'TYPICAL'));

SET SERVEROUTPUT ON
DECLARE
  n PLS_INTEGER;
BEGIN
  -- FIXED: this plan is preferred, new plans are not evolved automatically
  n := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
         sql_handle => '<sql_handle>', plan_name => '<plan_name>',
         attribute_name => 'fixed', attribute_value => 'YES');

  -- Take the bad plan out of use (without dropping it):
  n := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
         sql_handle => '<sql_handle>', plan_name => '<bad_plan_name>',
         attribute_name => 'enabled', attribute_value => 'NO');
END;
/

-- Drop one plan or the SQL's entire baseline:
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => '<sql_handle>', plan_name => '<plan_name>');
  -- without plan_name, all plans of the SQL are dropped:
  -- n := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => '<sql_handle>');
  DBMS_OUTPUT.PUT_LINE(n || ' plan(s) dropped');
END;
/

概要

ベースライン計画の属性を管理する: FIXEDの計画は他の受入れ済み計画より優先され、新しい計画の自動展開を停止する。ENABLED=NOは計画を削除せずに無効化する。DROPは完全に削除する。

使用する場面

重要なSQLの計画を確実に固定する、誤って受け入れた悪い計画を無効化する、不要になったベースラインを整理するため。

注意

Enterprise Editionが必要。FIXEDの計画はデータ増加に伴い劣化し、オプティマイザがより良い計画を使用するのを妨げる可能性があるため、定期的に見直す。すべての計画がENABLED=NOのSQLは自由に最適化される。DROPは元に戻せない。先にDBMS_SPM.PACK_STGTAB_BASELINEでステージング表にバックアップする。