SYSAUX 공간 경고를 따라가 보니 베이스라인이 3,400개 넘게 쌓여 있고 핵심 화면 하나는 베이스라인이 꺼져 있다

모범 답안 SQL ​

-- ① 가장 먼저 : S2 의 베이스라인을 다시 켠다 (2주째 하루 약 486만 블록을 더 읽고 있다)
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(sql_handle      => 'SQL_3f8a1c2e9d7b0461',
                                        plan_name       => 'SQL_PLAN_3f8a1c2e9d7b0461a5c3e9d2',
                                        attribute_name  => 'ENABLED',
                                        attribute_value => 'YES');
END;
/

-- ② 자동 캡처를 끈다 (업그레이드 주간에 켠 것)
ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = FALSE SCOPE = BOTH;

-- ③ 리포트 도구의 리터럴 SQL 에 자동으로 잡힌 베이스라인을 지운다 (손으로 올린 것은 건드리지 않는다)
DECLARE
  n PLS_INTEGER;
BEGIN
  FOR r IN (SELECT SQL_HANDLE
              FROM DBA_SQL_PLAN_BASELINES
             GROUP BY SQL_HANDLE
            HAVING MIN(ORIGIN) = 'AUTO-CAPTURE' AND MAX(ORIGIN) = 'AUTO-CAPTURE'
               AND MAX(MODULE) = 'RPT_TOOL'
               AND MAX(LAST_EXECUTED) < SYSDATE - 30) LOOP
    n := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => r.SQL_HANDLE);
  END LOOP;
END;
/

-- ④ 쓰이지 않는 계획은 10주가 지나면 정리되게 한다
EXEC DBMS_SPM.CONFIGURE('PLAN_RETENTION_WEEKS', 10);

-- ⑤ S3 : 재현할 수 없는 계획 대신 지금 계획(CF15 FULL · 361블록, 하루 1번)을 받아들이고 깨진 계획은 지운다
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '&s3_sql_id', plan_hash_value => &s3_now_phv);
  n := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => '&s3_handle', plan_name => '&s3_broken_plan');
END;
/

모범 답안 서술 ​

위험한 순서: ① S2가 첫째다. ENABLED NO는 그 계획이 없는 것과 같고, 이 SQL의 계획이 하나뿐이라 베이스라인 없이 계획을 세운다(교재 18화 §2.4 [8]). 그래서 받아들인 NL(312블록) 대신 HASH와 CF11 전체 스캔(8,412블록)으로 돈다. 하루 600번이면 약 19만 → 505만 블록으로, 2주째 하루 약 486만 블록을 더 읽고 있다. 지금 진행 중인 회귀다. ② 자동 캡처가 둘째다. 자동 캡처는 두 번 이상 실행된 SQL을 베이스라인으로 잡는다. 리포트 도구는 조건을 리터럴로 박아 보내므로 조건마다 서명이 다른 새 문장이고, 같은 리포트를 두 번 누를 때마다 베이스라인이 하나씩 생겼다. 그렇게 3,190개가 쌓였지만 대부분 다시 실행되지 않으니 아무것도 지키지 않으면서 SYSAUX만 1.21GB 차지했다. 끄지 않으면 계속 쌓인다. ③ S3이 셋째다. REPRODUCED NO는 받아들인 계획을 다시 만들지 못한다는 뜻이다. 9월 인덱스 정리 때 IX_CF15_ITM이 사라져 보호가 깨졌다. 다만 하루 한 번 도는 배치이고 지금 계획(361블록)과의 차이가 350블록 남짓이라 급하지 않다. ④ S6의 FIXED는 진화를 막지만 작은 테이블(6블록)이라 다음 점검으로 미룬다. ⑤ 대기 줄 71개는 반기 점검의 몫이다. 정리: S2는 ALTER_SQL_PLAN_BASELINE으로 ENABLED를 YES로 되돌린다. 자동 캡처는 FALSE로 끈다. 리포트 도구의 베이스라인은 손으로 올린 계획이 섞이지 않은 핸들만 골라(ORIGIN이 모두 AUTO-CAPTURE, MODULE RPT_TOOL, 30일 넘게 쓰이지 않음) DROP_SQL_PLAN_BASELINE으로 지운다. PLAN_RETENTION_WEEKS를 10으로 줄여 앞으로 쓰이지 않는 계획이 저절로 정리되게 한다. S3은 인덱스를 되살릴 만큼의 차이가 아니므로 지금 계획을 받아들인 계획으로 올리고 재현할 수 없는 계획은 지운다. 부작용: 리포트 도구의 SQL 가운데 실제로 매일 같은 글자로 도는 것이 있다면 그 보호도 함께 사라진다. 그래서 30일 안에 쓰인 것은 남겼다. 자동 캡처를 끄면 새로 생기는 핵심 SQL은 사람이 골라 올려야 한다. 보존을 10주로 줄이면 분기에 한 번 도는 SQL의 계획도 정리될 수 있으므로, 그런 SQL은 대장에 적어 두고 손으로 다시 올린다. 대장과 주기: 베이스라인마다 SQL과 화면 이름, 올린 사람과 날짜, 올린 이유와 그때의 규모, 받아들인 계획이 기대는 인덱스, 재검토 조건을 적는다. ENABLED, FIXED, REPRODUCED, LAST_EXECUTED, 대기 줄 수는 매월 한 번 조회해 바뀐 것을 대장과 맞춘다. ENABLED나 FIXED를 바꾸는 시험은 시험 DB에서만 하고, 운영에서 바꿨다면 되돌린 시각까지 대장에 적는다.

해설 ​

• 자동 캡처는 지킬 계획을 고르지 않는다. 두 번 실행된 문장을 모두 잡는다. 업그레이드 주간처럼 기간을 정해 켰다면 끄는 날짜도 같이 정한다(교재 18화 §2.2: 자동 캡처의 '두 번째'는 문장 기록으로 센다).
• 공간 경고는 경고일 뿐이다. SPACE_BUDGET_PERCENT를 넘어도 캡처는 멈추지 않고 경고만 남는다. 한도를 올리는 것은 원인을 덮는 일이다.
• 실습의 한 줄이 운영에 남았다. 교재 18화 [8]의 ENABLED NO는 복사본에서 재고 곧바로 되돌린 것이다. 운영의 베이스라인 속성은 시험하지 않는다.
• REPRODUCED NO를 늘 인덱스를 되살려 고칠 필요는 없다. 깨진 계획과 지금 계획의 차이를 숫자로 보고, 차이가 작으면 지금 계획을 받아들인다. 차이가 크면 인덱스를 되살리는 것이 먼저다(OPT-046).
• DBMS_SPM.CONFIGURE는 바꾼 시각이 DBA_SQL_MANAGEMENT_CONFIG에 남는다(교재 18화 §3). 바꾸기 전 값을 대장에 적어 둔다.

관련 교재 ​

• 18화 · SQL Plan Management
• 18화 · Free의 SPM 자동 기능