2012년에 넣은 옵티마이저 설정 세 줄을 기본값으로 되돌리자는데 무엇이 바뀔지 아무도 모른다

실전DDL유통631 / 700

상황

본사 유통 DB의 spfile에는 2012년 이관 때 넣은 세 줄이 있다. optimizer_mode = FIRST_ROWS, optimizer_index_cost_adj = 20, optimizer_index_caching = 80. 넣은 사람도 이유도 남아 있지 않다. 박팀장은 다음 달 정기 점검 때 기본값으로 되돌리자고 했다. "단, 점장 화면이 하나라도 느려지면 안 돼요." 조민우가 복사본(같은 데이터, 같은 통계)에서 대표 SQL 셋을 지금 설정 세션과 기본값 세션으로 각각 쟀다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO)(SLE_NO는 날짜로 시작해 판매 순서와 같다) · IX_CF10_SLE_DT (SLE_DT) · 매장 6곳(매장당 약 83,000건) · SLE_DT VARCHAR2(8)
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) 높이 3
CF12 발주 734행 · 10블록 / CF13 발주상품 7,102행 · 57블록 · 각각 PK(ORD_NO, ORD_NO + ITM_NO)
SQL Plan Management: 베이스라인 없음 · 이 DB의 반복 SQL은 대부분 하루에 한 번 이상 돈다

원본 SQL

sql

-- 박팀장 안 (다음 달 정기 점검 · 재기동)
ALTER SYSTEM RESET optimizer_mode           SCOPE = SPFILE;
ALTER SYSTEM RESET optimizer_index_cost_adj SCOPE = SPFILE;
ALTER SYSTEM RESET optimizer_index_caching  SCOPE = SPFILE;

-- S1 점장 화면 「최근 판매」 : 화면은 20행만 받고 커서를 닫는다 (하루 3,000번)
SELECT SLE_NO, SLE_DT, SLE_HMS
  FROM CF10
 WHERE SHP_NO = :shp
 ORDER BY SLE_NO DESC;

-- S2 본부 「월 상품 판매」 (하루 40번)
SELECT b.ITM_NO, SUM(b.SLE_QTY) AS qty
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT BETWEEN :d1 AND :d2
   AND b.SLE_NO = a.SLE_NO
 GROUP BY b.ITM_NO;

-- S3 「발주 상세」 (하루 500번)
SELECT d.ITM_NO, d.ORD_QTY
  FROM CF12 h, CF13 d
 WHERE h.ORD_NO = :ord
   AND d.ORD_NO = h.ORD_NO;

실행계획 학습용 예시 · 복사본에서 두 설정으로 잰 값

xplan

[복사본 측정 — 두 설정]
          지금 설정 (FIRST_ROWS · 20 · 80)                     기본값 (ALL_ROWS · 100 · 0)
 S1   INDEX FULL SCAN DESCENDING CF10_PK · 7블록 (20행에서 멈춘다)   TABLE ACCESS FULL + SORT ORDER BY · 4,583블록 · 0.19초
 S2   NESTED LOOPS (CF11_PK 20,953번) · 84,500블록 · 0.31초        HASH JOIN (CF11 FULL) · 8,457블록 · 0.12초
 S3   NESTED LOOPS (PK 두 번) · 7블록                               같은 계획 · 7블록

[S2 — 지금 설정 · 6월]
-------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                |      1 |        |    109 |00:00:00.31 |   84500 |
|   1 |  HASH GROUP BY                  |                |      1 |    109 |    109 |00:00:00.31 |   84500 |
|*  2 |   FILTER                        |                |      1 |        |  45960 |00:00:00.29 |   84500 |
|   3 |    NESTED LOOPS                 |                |      1 |   5858 |  45960 |00:00:00.28 |   84500 |
|   4 |     NESTED LOOPS                |                |      1 |   5858 |  45960 |00:00:00.17 |   63112 |
|   5 |      TABLE ACCESS BY INDEX ROWID| CF10           |      1 |   2673 |  20953 |00:00:00.02 |     253 |
|*  6 |       INDEX RANGE SCAN          | IX_CF10_SLE_DT |      1 |   2673 |  20953 |00:00:00.01 |      60 |
|*  7 |      INDEX RANGE SCAN           | CF11_PK        |  20953 |      2 |  45960 |00:00:00.11 |   62859 |
|   8 |     TABLE ACCESS BY INDEX ROWID | CF11           |  45960 |      2 |  45960 |00:00:00.10 |   21388 |
-------------------------------------------------------------------------------------------------------

[S1 — 기본값 · 화면이 20행을 받고 닫았을 때]
-------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                |      1 |        |     20 |00:00:00.19 |    4583 |
|   1 |  SORT ORDER BY                  |                |      1 |  83333 |     20 |00:00:00.19 |    4583 |
|*  2 |   TABLE ACCESS FULL             | CF10           |      1 |  83333 |  83312 |00:00:00.07 |    4583 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:D1<=:D2)                              (S2)
   6 - access("A"."SLE_DT">=:D1 AND "A"."SLE_DT"<=:D2)  (S2)
   7 - access("B"."SLE_NO"="A"."SLE_NO")             (S2)
   2 - filter("SHP_NO"=:SHP)                         (S1)

문제

세 SQL이 설정을 되돌리면 각각 어떻게 바뀌는지 그 이유를 세 파라미터로 설명하고, 하루 실행 횟수를 곱해 득실을 쓰시오.
점장 화면이 하나도 느려지지 않게 되돌리는 절차를 되돌리기 전, 되돌린 날, 그 뒤로 나눠 SQL로 쓰시오.
설정은 그대로 두고 S2만 힌트로 고치는 안, 화면과 배치의 세션 설정을 나누는 안과 비교해 부작용을 서술하시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- [되돌리기 전 ①] S1 은 화면의 의도(20행)를 SQL 에 적는다 — 설정의 버릇에 기대지 않게 (배포)
SELECT /*+ FIRST_ROWS(20) */ SLE_NO, SLE_DT, SLE_HMS
  FROM CF10
 WHERE SHP_NO = :shp
 ORDER BY SLE_NO DESC;

-- [되돌리기 전 ②] 안전망 : 지금 설정에서 도는 반복 SQL 의 계획을 베이스라인으로 (받아들인 계획)
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(attribute_name  => 'PARSING_SCHEMA_NAME',
                                             attribute_value => 'PUP');
END;
/

-- [되돌린 날] 정기 점검 · 재기동
ALTER SYSTEM RESET optimizer_mode           SCOPE = SPFILE;
ALTER SYSTEM RESET optimizer_index_cost_adj SCOPE = SPFILE;
ALTER SYSTEM RESET optimizer_index_caching  SCOPE = SPFILE;

-- [그 뒤] 기본값에서 새로 만든 계획은 대기 줄(ACCEPTED = NO)로 간다 → 진화로 시험해 나은 것만 받는다
DECLARE
  t VARCHAR2(128);
  e VARCHAR2(128);
  n NUMBER;
BEGIN
  t := DBMS_SPM.CREATE_EVOLVE_TASK(time_limit => 1800);   -- 대기 줄 전체
  e := DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name => t);
  -- REPORT_EVOLVE_TASK 를 읽고 : S2 의 HASH 는 통과(약 10배) · 나빠지는 계획은 거절되어 대기 줄에 남는다
  n := DBMS_SPM.IMPLEMENT_EVOLVE_TASK(task_name => t);
END;
/
-- 한 분기 뒤 : 진화가 끝난 SQL 의 옛 계획(OPT_PARAM 이 든 것)은 지운다 · 베이스라인 대장 정리

개선 후 실행계획 학습용 예시 · 복사본에서 두 설정으로 잰 값

xplan

[되돌린 뒤 — 복사본에서 같은 절차]
 S1   FIRST_ROWS(20) 힌트 판 · INDEX FULL SCAN DESCENDING CF10_PK · 7블록 (설정과 상관없이)
 S2   대기 줄의 HASH → 진화 "passed the benefit criterion ... 9.98712 times better" → 8,457블록
 S3   같은 계획 · 7블록 (대기 줄에 새 계획 없음)

모범 답안 서술

S2: 지금 설정에서는 optimizer_index_cost_adj = 20이 인덱스 경로 비용을 20%로, optimizer_index_caching = 80이 NL 안쪽 인덱스 블록의 80%를 캐시에 있다고 본다. 판매 2,673건(E-Rows)마다 CF11_PK를 찾아가는 비용이 거의 사라져 NL이 CF11 FULL을 쓰는 해시보다 싸 보였다. 실제로는 20,953건마다 찾아가 84,500블록을 읽는다. 기본값으로 되돌리면 할인이 사라져 해시 조인으로 돌아가 8,457블록이 된다. 하루 40번이면 약 338만 → 34만 블록으로 좋아진다. S1: optimizer_mode = FIRST_ROWS(숫자 없는 옛 모드)는 정렬을 피할 수 있는 인덱스를 먼저 고르는 버릇이 있다. 그래서 CF10_PK를 거꾸로 읽다가 20행에서 멈췄다(7블록). ALL_ROWS가 되면 결과 전체를 받는 비용으로 셈하므로 매장의 83,312행을 FULL로 읽고 정렬한다(4,583블록, 0.19초). 하루 3,000번이면 약 2만 → 1,375만 블록으로 나빠진다. 박팀장이 걱정한 바로 그 화면이다. S3: PK로 한두 행을 찾는 SQL이라 어느 설정에서도 같은 계획이다. 이 DB의 SQL 대부분이 이쪽이다. 절차: 되돌리기 전에 두 가지를 한다. 첫째, S1에 /*+ FIRST_ROWS(20) */를 넣어 "화면은 20행만 본다"는 의도를 SQL에 적는다. 설정의 버릇에 기대던 것을 SQL로 옮기는 것이다. 둘째, 안전망으로 DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE로 이 스키마의 반복 SQL 계획을 베이스라인으로 올린다. 받아들인 계획의 Outline에는 그때의 설정이 OPT_PARAM 힌트와 FIRST_ROWS로 들어 있어, 되돌린 뒤에도 그 계획을 그대로 재현한다. 되돌린 날에는 spfile의 세 줄을 RESET하고 재기동한다. 그 뒤 옵티마이저가 기본값에서 만든 새 계획은 대기 줄(ACCEPTED NO)에 쌓인다. 진화 작업으로 시험 운행해 S2의 해시처럼 나은 계획만 받아들이고, 나빠지는 계획은 거절되어 옛 계획이 계속 돈다. 그래서 어떤 화면도 갑자기 느려지지 않는다. 한 분기 뒤 진화가 끝나면 옛 계획들을 정리한다. 다른 안: 설정을 그대로 두고 S2만 USE_HASH 힌트로 고치면 오늘 한 SQL은 낫지만, 세 줄의 왜곡은 다른 SQL과 앞으로 쓸 새 SQL에 그대로 남고 힌트에는 유효기간이 생긴다. 로그온 트리거나 서비스로 화면과 배치의 세션 설정을 나누는 안은 설정을 관리할 곳이 늘어나 다음 사람이 또 "이유를 모르는 설정"을 물려받는다. 그래서 기본값으로 되돌리되 SQL의 의도는 SQL에, 계획의 안전은 베이스라인에 맡긴다.

해설

안전망의 대가: 반복 SQL을 한꺼번에 베이스라인으로 올리면 SYSAUX가 늘고, 진화가 끝날 때까지 좋은 새 계획도 대기 줄에서 기다린다. 대상은 Gets 상위와 화면 SQL로 좁혀도 된다(18화 §2.5).
S1을 베이스라인에만 맡기는 안: 옛 계획이 재현되므로 당장은 느려지지 않는다. 하지만 FIRST_ROWS의 버릇이 SQL 밖 Outline에만 남아, 베이스라인을 정리하는 날 다시 같은 문제가 생긴다. 화면의 의도는 SQL에 적는다.
S2의 E-Rows 2,673(실제 20,953)은 문자 날짜 탓으로 그대로 남는다. 기본값에서는 그래도 해시가 이기지만, 근본은 10화 §12의 표현식 통계나 DATE 컬럼이다.
소스가 없어 S1처럼 고칠 수 없는 화면이 나중에 나오면 SQL 패치로 힌트를 붙이는 길이 있다(OPT-045).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 복사본에서 두 설정으로 잰 값 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 복사본에서 두 설정으로 잰 값 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황