카디널리티 점검표가 날마다 서른 줄을 올리는데 고칠 만한 줄은 한두 개뿐이다

중급SELECT버거609 / 700

상황

조민우는 지난달 버거 본사 DB에 「추정 오차 점검표」를 만들었다. 매일 08:00에 V$SQL_PLAN_STATISTICS_ALL에서 E-Rows와 A-Rows가 10배 넘게 차이 나는 계획 줄을 뽑아 메일로 보낸다(실행 통계는 STATISTICS_LEVEL = ALL로 띄운 점검용 세션에서 모은다). 점검표에는 날마다 서른 줄 가까이 올라온다. 선배는 어제 점검표에서 SQL 세 개를 골라 계획을 펼쳐 보고 이렇게 말했다. "여섯 줄 가운데 진짜는 세 줄이고, 그 셋도 원인은 한 줄이야. 점검표부터 고치자."

테이블과 인덱스

V$SQL_PLAN_STATISTICS_ALL: 계획 줄마다 한 행 · CARDINALITY = E-Rows(한 번 시작할 때의 추정) · LAST_STARTS = Starts · LAST_OUTPUT_ROWS = A-Rows(모든 Starts를 더한 값) · ID, PARENT_ID, DEPTH(계획의 줄과 부모 줄) · OPERATION, OPTIONS
ORD: 1,000,000행 · 22,422블록 · ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS): 날짜순 적재 · 하루 약 1,368건
ORD_ITEM: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · 주문당 평균 2.47줄
통계 최신 · 히스토그램 없음
SQL 셋: ① 「일 마감 메뉴 집계」(하루치 주문의 메뉴별 수량) ② 상황판 「최근 주문 20건」 ③ 「밸런타인데이 메뉴 비교」(ORD_DT LIKE '%0214')

원본 SQL

sql

-- 「추정 오차 점검표」 매일 08:00 (조민우)
SELECT p.SQL_ID, p.CHILD_NUMBER, p.ID,
       p.OPERATION || ' ' || p.OPTIONS AS op, p.OBJECT_NAME,
       p.CARDINALITY      AS e_rows,
       p.LAST_OUTPUT_ROWS AS a_rows
  FROM V$SQL_PLAN_STATISTICS_ALL p
 WHERE p.LAST_OUTPUT_ROWS > p.CARDINALITY * 10
    OR p.LAST_OUTPUT_ROWS * 10 < p.CARDINALITY
 ORDER BY p.SQL_ID, p.CHILD_NUMBER, p.ID

실행계획 학습용 예시 · 점검 방식은 교재 10화 추적 SOP

xplan

[점검표 · 10월 14일 08:00 (서른한 줄 가운데 SQL 세 개의 여섯 줄)]
 SQL   ID  OP                              OBJECT_NAME    E_ROWS    A_ROWS
 ①      6  INDEX RANGE SCAN                ORD_ITEM_PK         2     3,378
 ①      7  TABLE ACCESS BY INDEX ROWID     ORD_ITEM            1     3,378
 ②      3  TABLE ACCESS BY INDEX ROWID     ORD             1000K        20
 ③      1  HASH GROUP BY                                  67,252       184
 ③      2  HASH JOIN                                       124K     6,758
 ③      3  TABLE ACCESS FULL               ORD            50,000     2,736

[① 「일 마감 메뉴 집계」 · ORD_DT = '20131230']
-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |      1 |        |     92 |00:00:00.02 |    4137 |
|   1 |  HASH GROUP BY                        |             |      1 |     92 |     92 |00:00:00.02 |    4137 |
|   2 |   NESTED LOOPS                        |             |      1 |   3383 |   3378 |00:00:00.02 |    4137 |
|   3 |    NESTED LOOPS                       |             |      1 |   3383 |   3378 |00:00:00.01 |    2771 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| ORD        |      1 |   1368 |   1366 |00:00:00.01 |      39 |
|*  5 |      INDEX RANGE SCAN                 | ORD_X01     |      1 |   1368 |   1366 |00:00:00.01 |       8 |
|*  6 |     INDEX RANGE SCAN                  | ORD_ITEM_PK |   1366 |      2 |   3378 |00:00:00.01 |    2732 |
|   7 |    TABLE ACCESS BY INDEX ROWID        | ORD_ITEM    |   3378 |      1 |   3378 |00:00:00.01 |    1366 |
-------------------------------------------------------------------------------------------------------
   5 - access("O"."ORD_DT"='20131230')
   6 - access("OI"."ORD_NO"="O"."ORD_NO")

[② 「최근 주문 20건」]
--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |     20 |00:00:00.01 |       5 |
|*  1 |  COUNT STOPKEY                |         |      1 |        |     20 |00:00:00.01 |       5 |
|   2 |   VIEW                        |         |      1 |     20 |     20 |00:00:00.01 |       5 |
|   3 |    TABLE ACCESS BY INDEX ROWID| ORD     |      1 |   1000K|     20 |00:00:00.01 |       5 |
|   4 |     INDEX FULL SCAN DESCENDING| ORD_X01 |      1 |     20 |     20 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------
   1 - filter(ROWNUM<=20)

[③ 「밸런타인데이 메뉴 비교」]
-------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |      1 |        |    184 |00:00:00.48 |   77687 |          |
|   1 |  HASH GROUP BY      |          |      1 |  67252 |    184 |00:00:00.48 |   77687 | 1250K (0)|
|*  2 |   HASH JOIN         |          |      1 |    124K|   6758 |00:00:00.47 |   77687 | 3712K (0)|
|*  3 |    TABLE ACCESS FULL| ORD      |      1 |  50000 |   2736 |00:00:00.14 |   22422 |          |
|   4 |    TABLE ACCESS FULL| ORD_ITEM |      1 |   2472K|   2472K|00:00:00.21 |   55265 |          |
-------------------------------------------------------------------------------------------------------
   2 - access("OI"."ORD_NO"="O"."ORD_NO")
   3 - filter("O"."ORD_DT" LIKE '%0214')

문제

점검표의 여섯 줄 가운데 진짜 추정 오차는 어느 줄들이고 원인은 어느 한 줄인지 가리시오. 나머지 줄이 오탐인 이유를 Starts와 STOPKEY로 설명하시오.
점검 SQL을 고쳐 오탐을 걸러 내시오. 비교 기준과 빼야 할 줄을 SQL로 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 「추정 오차 점검표」 고친 판 (교재 10화 추적 SOP 의 ② · ③ 을 점검표로 옮긴다)
--  ① E-Rows 는 한 번 시작할 때의 추정 → E-Rows × Starts 를 A-Rows 와 비교한다 (NL 안쪽 줄의 오탐 제거)
--  ② COUNT STOPKEY · SORT ORDER BY STOPKEY 아래 줄은 멈추기 전의 카디널리티 → 대조하지 않는다
--  ③ 작은 수의 흔들림은 빼고, 한 SQL 안에서는 가장 안쪽(깊은) 줄부터 보이게 정렬한다
WITH p AS (
  SELECT s.SQL_ID, s.CHILD_NUMBER, s.ID, s.DEPTH,
         s.OPERATION, s.OPTIONS, s.OBJECT_NAME,
         s.LAST_STARTS                  AS starts,
         s.CARDINALITY * s.LAST_STARTS  AS e_total,
         s.LAST_OUTPUT_ROWS             AS a_rows
    FROM V$SQL_PLAN_STATISTICS_ALL s
   WHERE s.LAST_STARTS > 0
     AND s.CARDINALITY IS NOT NULL
), stop AS (
  SELECT q.SQL_ID, q.CHILD_NUMBER, q.ID
    FROM V$SQL_PLAN_STATISTICS_ALL q
   START WITH q.OPTIONS LIKE '%STOPKEY%'
 CONNECT BY PRIOR q.SQL_ID       = q.SQL_ID
        AND PRIOR q.CHILD_NUMBER = q.CHILD_NUMBER
        AND PRIOR q.ID           = q.PARENT_ID
)
SELECT p.SQL_ID, p.CHILD_NUMBER, p.ID, p.DEPTH,
       p.OPERATION || ' ' || p.OPTIONS AS op, p.OBJECT_NAME,
       p.starts, p.e_total, p.a_rows,
       ROUND(GREATEST(p.e_total, p.a_rows, 1) / GREATEST(LEAST(p.e_total, p.a_rows), 1), 1) AS ratio
  FROM p
 WHERE NOT EXISTS (SELECT 1
                     FROM stop t
                    WHERE t.SQL_ID = p.SQL_ID
                      AND t.CHILD_NUMBER = p.CHILD_NUMBER
                      AND t.ID = p.ID)
   AND GREATEST(p.e_total, p.a_rows) >= 100
   AND GREATEST(p.e_total, p.a_rows, 1) / GREATEST(LEAST(p.e_total, p.a_rows), 1) >= 10
 ORDER BY p.SQL_ID, p.CHILD_NUMBER, p.DEPTH DESC, p.ID;

개선 후 실행계획 학습용 예시 · 점검 방식은 교재 10화 추적 SOP

xplan

[고친 점검표 · 같은 날 · SQL 세 개에서 남은 줄]
 SQL   ID  DEPTH  OP                   STARTS    E_TOTAL   A_ROWS   RATIO
 ③      3      3  TABLE ACCESS FULL         1     50,000    2,736    18.3   ← 출발점
 ③      2      2  HASH JOIN                 1    123,500    6,758    18.3
 ③      1      1  HASH GROUP BY             1     67,252      184   365.5
 (① 6 · 7번 줄 : E-Rows × Starts = 2,732 · 3,378 → 빠짐 / ② 3번 줄 : STOPKEY 아래 → 빠짐)

모범 답안 서술

진짜 추정 오차는 ③의 세 줄이고, 원인은 그중 가장 안쪽인 3번 줄 하나다. ORD_DT LIKE '%0214'처럼 앞이 %인 LIKE는 통계로 계산할 방법이 없어서 옵티마이저가 고정 선택도 5%를 쓴다. 100만 × 5% = 50,000인데 실제 2월 14일 주문은 두 해를 합쳐 2,736건이다. 2번 줄(124K / 6,758)과 1번 줄(67,252 / 184)은 이 오차를 그대로 물려받아 커진 것이므로 따로 고칠 대상이 아니다. 3번 줄을 고치면 위 줄은 따라 맞는다(교재 10화 SOP ③ "10배 이상 어긋난 가장 안쪽 줄"). ①의 두 줄은 오탐이다. NL 안쪽 줄의 E-Rows는 한 번 시작할 때의 추정이고, A-Rows는 모든 시작을 더한 값이다. 6번 줄은 1,366번 시작했으므로 2 × 1,366 = 2,732를 3,378과 비교해야 한다(1.2배). 7번 줄도 1 × 3,378 = 3,378로 정확하다. 점검 SQL이 CARDINALITY를 LAST_OUTPUT_ROWS와 바로 비교해서 생긴 오탐이므로 CARDINALITY * LAST_STARTS로 고친다. ②의 3번 줄도 오탐이다. COUNT STOPKEY 아래 줄의 1000K는 멈추기 전의 카디널리티이고, 20건에서 멈추는 몫은 VIEW 줄의 E-Rows 20에 들어 있다. 그래서 STOPKEY 줄 아래는 CONNECT BY로 찾아 대조에서 뺀다. 고친 점검표는 E-Rows × Starts와 A-Rows의 비가 10배 이상이고 큰 쪽이 100 이상인 줄만 남기고, 한 SQL 안에서 깊은 줄부터 보여 준다. 같은 날 세 SQL에서는 ③의 세 줄만 남고, 맨 위에 출발점(3번 줄)이 온다. 날마다 서른 줄이던 메일이 고칠 대상 몇 개로 줄어든다. 다음 단계는 ③을 IN 목록(ORD_DT IN ('20120214', '20130214'))으로 고치는 일이다.

해설

DISPLAY_CURSOR도 같다. 화면의 E-Rows는 줄마다 한 번 시작할 때의 값이고 A-Rows는 합계다. NL 안쪽이나 스칼라 서브쿼리 줄을 읽을 때마다 Starts를 곱한다(교재 06화 Starts).
STOPKEY 아래에서 어느 줄에 멈춤을 반영하는지는 일정하지 않다. 09화 rev01처럼 인덱스 줄에 1000, 테이블 줄에 1000K가 찍히기도 한다(10화 §11). 그래서 그 아래 줄은 통째로 빼고, 그 계획은 VIEW 줄과 Cost로 따로 본다.
하한을 두는 이유: 3건과 40건처럼 작은 수는 10배가 넘어도 계획을 바꾸지 못한다. 하한 100은 이 시스템의 줄 수에 맞춘 값이라 점검표 머리말에 근거와 함께 적어 둔다.
V$SQL_PLAN_STATISTICS_ALL은 커서가 공유 풀에서 밀려나면 사라지고, AWR에는 A-Rows가 남지 않는다. 점검표는 그날 아침의 스냅숏이므로 중요한 줄은 그때 계획을 떠서 남겨 둔다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 점검 방식은 교재 10화 추적 SOP ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 점검 방식은 교재 10화 추적 SOP ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황