SQL plan baseline 계획 고정, 비활성화, 삭제

명령어

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

하는 일

baseline 계획 속성을 관리한다. FIXED 계획은 다른 accepted 계획보다 우선하며 새 계획의 자동 evolve를 멈춘다. ENABLED=NO는 삭제하지 않고 계획을 비활성화한다. DROP은 영구적으로 제거한다.

사용 시점

중요 SQL의 계획을 확실히 고정하거나, 실수로 수락된 나쁜 계획을 비활성화하거나, 더 이상 필요 없는 baseline을 정리하기 위해.

주의

Enterprise Edition이 필요하다. FIXED 계획은 데이터가 커지면서 성능이 저하될 수 있고 옵티마이저가 더 나은 계획을 쓰지 못하게 하므로 주기적으로 검토한다. 모든 계획이 ENABLED=NO인 SQL은 자유롭게 최적화된다. DROP은 되돌릴 수 없으므로 먼저 DBMS_SPM.PACK_STGTAB_BASELINE으로 staging 테이블에 백업한다.