cursor cache의 좋은 계획을 SQL plan baseline으로 로드

명령어

-- 1) Compare the SQL's plans and their performance:
SELECT sql_id, child_number, plan_hash_value, executions,
       ROUND(elapsed_time/1e3/NULLIF(executions,0),1) AS ms_per_exec,
       sql_plan_baseline
FROM   v$sql
WHERE  sql_id = '<sql_id>';

-- 2) Load the chosen plan as a baseline:
SET SERVEROUTPUT ON
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
         sql_id          => '<sql_id>',
         plan_hash_value => <plan_hash_value>,
         fixed           => 'NO',
         enabled         => 'YES');
  DBMS_OUTPUT.PUT_LINE(n || ' plan(s) loaded');
END;
/

-- 3) Verify the baseline:
SELECT sql_handle, plan_name, origin, enabled, accepted, fixed, reproduced,
       TO_CHAR(last_executed,'YYYY-MM-DD HH24:MI') AS last_exec
FROM   dba_sql_plan_baselines
WHERE  signature = (SELECT exact_matching_signature FROM v$sql
                    WHERE sql_id = '<sql_id>' AND ROWNUM = 1);
-- On subsequent executions v$sql.sql_plan_baseline should be populated.

하는 일

SQL Plan Management를 사용하여 선택한 계획을 ACCEPTED baseline으로 만든다. 이후 옵티마이저는 accepted baseline 계획만 사용하며 새로 발견한 계획은 evolve될 때까지 보류한다.

사용 시점

실행 계획 회귀 시 검증된 좋은 계획으로 되돌리고, 업그레이드나 통계 변경 전 중요 SQL의 계획을 보호하기 위해.

주의

Enterprise Edition에서는 추가 라이선스가 없다(SE2에서는 SQL당 baseline 계획 1개로 제한). ADMINISTER SQL MANAGEMENT OBJECT가 필요하다. baseline은 정확히 같은 SQL 텍스트(signature)에 적용되므로 리터럴을 사용하는 SQL에는 도움이 되지 않는다. 계획이 사용하는 인덱스가 삭제되면 계획을 재현할 수 없고(REPRODUCED=NO) 옵티마이저는 비용 기반 선택으로 돌아간다. OPTIMIZER_USE_SQL_PLAN_BASELINES는 TRUE(기본값)여야 한다.