PART-008

프루닝이 안 된다며 날짜를 SQL에 이어 붙인 뒤 하드 파싱이 하루 4천 번으로 늘었다

기초SELECT한빛유통383 / 700

상황

정산 화면의 「기간 매출」은 시작일과 종료일을 골라 그 기간의 완료 주문 건수와 금액을 낸다. 하루 약 4천 번 호출되고, 고르는 기간은 대부분 한 달 안이다. 지난달 T_ORD_BIG_P14로 옮긴 뒤 조민우가 실행계획의 Pstart와 Pstop에 KEY가 찍힌 것을 보고 "바인드 변수 때문에 프루닝이 안 된다"며 날짜를 SQL 문자열에 이어 붙이도록 바꿨다. 이번 주 AWR에서 하드 파싱이 하루 약 4,100번으로 늘었고, 공유 풀 경합 대기가 새로 보인다.

테이블과 인덱스

T_ORD_BIG_P14 주문(파티션 복사본, 교재 14화): 1,000,000행 · PARTITION BY RANGE (ORD_DT) 월별 36칸(P202201 ~ P202412)과 빈 PMAX, 모두 37칸. P202406은 30번째 칸이고 FULL로 약 161블록이다.
ORD_DT는 DATE이고 전부 자정이다. 하루 주문은 약 913건이고, ORD_ST는 COMPLETE가 90%다.
인덱스는 없다. 통계는 최신이다.

원본 SQL

sql

-- 지금 : 화면이 날짜를 SQL 문자열에 이어 붙인다 (2024-06-10 ~ 2024-06-20)
SELECT COUNT(*) AS cnt, SUM(ORD_AMT) AS amt
  FROM T_ORD_BIG_P14
 WHERE ORD_ST = 'COMPLETE'
   AND ORD_DT >= TO_DATE('20240610', 'YYYYMMDD')
   AND ORD_DT <  TO_DATE('20240621', 'YYYYMMDD')

실행계획 학습용 예시 · 교재 14화 규모

xplan

[지금 — 리터럴 · 2024-06-10 ~ 2024-06-20]
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |               |      1 |        |      1 | 00:00:00.01 |     161 |        |       |
|   1 |  SORT AGGREGATE          |               |      1 |      1 |      1 | 00:00:00.01 |     161 |        |       |
|   2 |   PARTITION RANGE SINGLE |               |      1 |   9015 |   9039 | 00:00:00.01 |     161 |     30 |    30 |
|*  3 |    TABLE ACCESS FULL     | T_ORD_BIG_P14 |      1 |   9015 |   9039 | 00:00:00.01 |     161 |     30 |    30 |
----------------------------------------------------------------------------------------------------------------------

   3 - filter(("ORD_ST"='COMPLETE' AND "ORD_DT">=TO_DATE(' 2024-06-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "ORD_DT"<TO_DATE(' 2024-06-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

[지난달 — 바인드 변수 · 같은 기간 (:v_from = '20240610', :v_to = '20240620')]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |      1 |        |      1 | 00:00:00.01 |     161 |        |       |
|   1 |  SORT AGGREGATE             |               |      1 |      1 |      1 | 00:00:00.01 |     161 |        |       |
|*  2 |   FILTER                    |               |      1 |        |   9039 | 00:00:00.01 |     161 |        |       |
|   3 |    PARTITION RANGE ITERATOR |               |      1 |   9015 |   9039 | 00:00:00.01 |     161 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL       | T_ORD_BIG_P14 |      1 |   9015 |   9039 | 00:00:00.01 |     161 |    KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------

   2 - filter(TO_DATE(:V_FROM,'YYYYMMDD')<TO_DATE(:V_TO,'YYYYMMDD')+1)
   4 - filter(("ORD_ST"='COMPLETE' AND "ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1))

[AWR 하루 요약 — 바꾸기 전 → 바꾼 뒤]
  parse count (hard)                   41 → 4,112
  이 화면의 SQL 문장 수 (V$SQLAREA)      1 → 3,958   (FORCE_MATCHING_SIGNATURE 는 하나)
  Top 대기                             latch: shared pool · library cache: mutex X 가 새로 보인다

문제

Pstart와 Pstop의 KEY가 무슨 뜻인지 쓰고, 지난달 바인드 변수판도 6월 칸 하나만 읽었다는 것을 실행계획의 어느 숫자로 알 수 있는지 설명하시오.
SQL을 바인드 변수판으로 되돌려 쓰고, 리터럴판의 대가를 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 바인드 변수판으로 되돌린다 : 칸은 실행할 때 고른다 (KEY)
SELECT COUNT(*) AS cnt, SUM(ORD_AMT) AS amt
  FROM T_ORD_BIG_P14
 WHERE ORD_ST = 'COMPLETE'
   AND ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1

개선 후 실행계획 학습용 예시 · 교재 14화 규모

xplan

[6월 10일 ~ 20일]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |      1 |        |      1 | 00:00:00.01 |     161 |        |       |
|   1 |  SORT AGGREGATE             |               |      1 |      1 |      1 | 00:00:00.01 |     161 |        |       |
|*  2 |   FILTER                    |               |      1 |        |   9039 | 00:00:00.01 |     161 |        |       |
|   3 |    PARTITION RANGE ITERATOR |               |      1 |   9015 |   9039 | 00:00:00.01 |     161 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL       | T_ORD_BIG_P14 |      1 |   9015 |   9039 | 00:00:00.01 |     161 |    KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------

[같은 커서 · 5월 25일 ~ 6월 5일 (두 달에 걸친다)]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |      1 |        |      1 | 00:00:00.01 |     327 |        |       |
|   1 |  SORT AGGREGATE             |               |      1 |      1 |      1 | 00:00:00.01 |     327 |        |       |
|*  2 |   FILTER                    |               |      1 |        |   9861 | 00:00:00.01 |     327 |        |       |
|   3 |    PARTITION RANGE ITERATOR |               |      1 |   9015 |   9861 | 00:00:00.01 |     327 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL       | T_ORD_BIG_P14 |      2 |   9015 |   9861 | 00:00:00.01 |     327 |    KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------

  (실행 때 고른 칸 — 위는 30번 하나, 아래는 29번과 30번)

모범 답안 서술

Pstart와 Pstop이 숫자(30 / 30)이면 계획을 세울 때 이미 어느 칸인지 안다는 뜻이고(정적 Pruning), KEY이면 계획을 세울 때는 모르고 실행할 때 바인드 값을 보고 칸을 고른다는 뜻이다(동적 Pruning). KEY는 실패가 아니다. 실패는 PARTITION RANGE ALL과 1 / 37이다. 지난달 바인드 변수판도 4번 줄의 Starts가 1이고 Buffers가 161이다. 칸 하나가 약 161블록이므로 리터럴판(3번 줄 Starts 1, Buffers 161)과 똑같이 6월 칸 하나만 읽었다. 조민우는 KEY라는 글자만 보고 숫자는 보지 않았다. SQL은 바인드 변수판(ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD') AND ORD_DT < TO_DATE(:v_to, 'YYYYMMDD') + 1)으로 되돌린다. 기간이 두 달에 걸쳐도 같은 커서가 실행할 때 두 칸을 고른다(Starts 2, 327블록). 리터럴판은 한 번 실행할 때의 일량은 같지만(161블록), 시작일과 종료일 조합마다 새 SQL 문장이 생긴다. 문장마다 하드 파싱을 하고 공유 풀에 커서를 새로 올리므로, 하드 파싱이 하루 41번 → 4,112번, 이 화면의 문장이 1개 → 3,958개로 늘었다. 그 과정에서 latch: shared pool과 library cache 경합이 생긴다. 공유 풀은 다른 SQL과 나눠 쓰므로 다른 화면까지 느려질 수 있다.

해설

판독표(교재 14화 §7.1): 숫자는 계획을 세울 때 이미 아는 칸, KEY는 실행할 때 고르는 칸, KEY(I)는 IN 목록의 칸, 1 / N과 ALL은 전부다. 실제로 연 칸 수는 Pstart, Pstop이 아니라 Starts와 Buffers에서 읽는다.
리터럴이 필요한 경우도 있다. 분포가 크게 치우친 컬럼이라 값마다 계획이 달라야 할 때다(교재 11화 §6, §7). 이 조회는 기간이 바뀌어도 '칸 몇 개를 FULL로 읽는다'는 계획이 같으므로 해당하지 않는다.
CURSOR_SHARING = FORCE로 리터럴을 바인드 변수로 바꿔 주는 방법도 있지만 DB 전체에 영향을 주는 차선책이다(교재 11화 §8). 고칠 수 있으면 SQL을 고친다.
칸 하나 161블록은 칸 안을 FULL로 읽은 값이다. 칸 안의 범위만 읽으려면 LOCAL 인덱스를 따로 검토한다(교재 14화 §8).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 14화 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 14화 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황