감사팀 분기 추출이 지난주 배포 뒤로 여섯 배 느려졌다

중급SELECT한빛유통56 / 700

상황

감사팀은 분기마다 「취소 또는 고액 주문」 전체 목록(3년 치, 약 4만 건)을 내려받고, 감사 대시보드도 같은 SQL을 하루 수십 번 호출한다. 원래는 테이블을 한 번 읽고 끝났다. 지난주 조민우가 "OR는 펼쳐야 인덱스를 탄다"는 글을 보고 /*+ USE_CONCAT */ 힌트를 붙였다. 상태 인덱스와 금액 인덱스가 다 있으니 빨라질 거라 생각했는데, 추출 시간이 0.3초에서 1.8초로 늘었다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록(블록당 약 172행) · 주문 순으로 쌓였다
ORD_ST COMPLETE 900,000 · PENDING 80,000 · CANCEL 20,000 · ORD_AMT 490,000원 이상 20,512건(2.05%) · 둘 다 해당하는 주문 410건 · 두 조건의 주문은 테이블 전체에 고르게 흩어져 있다
인덱스 IX_ORD_BIG_ST (ORD_ST): 리프 2,775블록 · IX_ORD_BIG_AMT (ORD_AMT): 리프 약 2,780블록 · 통계 최신(ORD_ST 히스토그램 있음)

원본 SQL

sql

SELECT /*+ USE_CONCAT */
       ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_ST = 'CANCEL'
    OR ORD_AMT >= 490000

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Starts | E-Rows | A-Rows | A-Time      | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |      1 |        |  40102 | 00:00:01.84 |   40433 |
|   1 |  CONCATENATION                |                |      1 |        |  40102 | 00:00:01.84 |   40433 |
|   2 |   TABLE ACCESS BY INDEX ROWID | T_ORD_BIG      |      1 |  20512 |  20512 | 00:00:00.92 |   20421 |
|*  3 |    INDEX RANGE SCAN           | IX_ORD_BIG_AMT |      1 |  20512 |  20512 | 00:00:00.02 |      58 |
|*  4 |   TABLE ACCESS BY INDEX ROWID | T_ORD_BIG      |      1 |  20000 |  19590 | 00:00:00.90 |   20012 |
|*  5 |    INDEX RANGE SCAN           | IX_ORD_BIG_ST  |      1 |  20000 |  20000 | 00:00:00.02 |      59 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ORD_AMT">=490000)
   4 - filter(LNNVL("ORD_AMT">=490000))
   5 - access("ORD_ST"='CANCEL')

문제

인덱스 두 개로 갈래마다 찾아갔는데 테이블 전체 스캔보다 일곱 배 가까이 읽은 이유를 갈래마다 숫자로 설명하시오.
이 추출을 고치시오. OR를 갈래로 펼치는 것이 이득인 경우와 손해인 경우를 갈래가 돌려주는 건수로 구분해 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 힌트를 뗀다 (옵티마이저가 비용으로 FULL을 고른다)
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_ST = 'CANCEL'
    OR ORD_AMT >= 490000

개선 후 실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows | A-Time      | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |  40102 | 00:00:00.30 |    5826 |
|*  1 |  TABLE ACCESS FULL | T_ORD_BIG |      1 |  40102 |  40102 | 00:00:00.30 |    5826 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("ORD_ST"='CANCEL' OR "ORD_AMT">=490000))

모범 답안 서술

2번 줄은 IX_ORD_BIG_AMT로 49만 원 이상 20,512건을 찾고, 3번 줄 인덱스(58블록)에서 얻은 ROWID로 테이블을 한 건씩 찾아가 Buffers가 20,421이 됐다. 4번 줄은 IX_ORD_BIG_ST로 CANCEL 20,000건을 찾아 같은 방식으로 20,012블록을 읽고, 첫 갈래에 이미 나온 410건을 LNNVL로 뺀다. 두 갈래를 합치면 40,433블록이다. 4만 건이 테이블 전체에 흩어져 있어 한 건마다 블록 하나를 다시 찾아간 셈이고, 테이블 전체(5,826블록)보다 일곱 배 가까이 많다. 게다가 인덱스로 찾아간 테이블 블록은 한 번에 하나씩 읽는데, FULL은 멀티블록으로 여러 블록을 한꺼번에 읽는다. 그래서 시간은 0.30초 → 1.84초로 여섯 배가 됐다. 고치는 방법은 힌트를 떼는 것이다. 두 갈래를 합친 결과가 테이블의 약 4%(40,102건)이고, 블록으로 따지면 거의 모든 블록에 결과가 몇 건씩 들어 있다. 이런 범위는 인덱스로 찾아가는 것보다 테이블을 한 번 읽는 쪽이 싸다(손익분기점). 힌트를 떼면 옵티마이저가 비용으로 FULL을 고른다(Buffers 40,433 → 5,826). OR-Expansion이 이득인 것은 갈래마다 돌려주는 건수가 적을 때다. 고객 한 명이나 한 시간 구간처럼 갈래가 몇 건에서 몇백 건이면, 갈래마다 인덱스로 찾아가는 것이 테이블 전체를 읽는 것보다 훨씬 싸다. 갈래 하나가 수만 건이면 그 갈래만으로 FULL보다 비싸지므로 펼치면 손해다. 펼칠지는 옵티마이저가 이 비용을 따져 정하는데, 힌트는 그 판단을 막고 데이터가 바뀌어도 그대로 남는다.

해설

함정: "인덱스를 타면 빠르다"는 결과가 적을 때의 이야기다. CANCEL 2%가 작아 보여도 100만 행의 2%는 2만 건이고, 흩어진 2만 건을 하나씩 찾아가면 2만 블록이다.
같은 조건에 날짜 범위가 붙는 화면(예: 하루치 취소 또는 고액 주문)이라면 이야기가 다르다. 그때는 ORD_DT를 앞에 둔 인덱스로 하루치만 읽는 것이 맞고, 그것은 OR를 펼칠지가 아니라 어떤 인덱스로 범위를 줄일지의 문제다.
4만 건을 내려받는 SQL을 대시보드가 하루 수십 번 호출한다면, 결과를 하루 한 번 집계해 두는 쪽을 먼저 검토한다.
힌트로 계획을 바꿨으면 바꾼 뒤의 Buffers와 A-Time을 반드시 잰다. 이 배포는 계획이 바뀐 것만 보고 숫자를 재지 않았다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황