상담원 80명이 띄워 두는 취소 주문 모니터가 첫 화면마다 주문 테이블을 통째로 읽는다

중급SELECT한빛유통625 / 700

상황

상담팀의 「취소 주문 모니터」는 취소된 주문을 최신순으로, 고객 이름과 등급을 붙여 보여 준다. 상담원 80명이 띄워 두고 수시로 새로 고쳐 하루 약 1만 번 호출되는데, 거의 모두 첫 화면만 본다. 화면 프레임워크는 커서 하나를 열어 두고 스크롤할 때마다 20행씩 더 가져가는 방식(무한 스크롤)이라 SQL에 행 수 제한을 넣을 수 없다. 지난달 ORD_ST에 히스토그램을 걸었고(10화 정책서 1호), 이 화면을 위해 (ORD_ST, ORD_DT, ORD_SEQ) 인덱스도 만들었다. 그런데 계획은 그대로 FULL이다. 조민우: "인덱스가 있는데 왜 안 타죠? OPTIMIZER_INDEX_COST_ADJ를 낮춰 볼까요?"

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT DATE(전부 자정이라 같은 날짜의 주문이 많다) · ORD_ST CANCEL 20,000행(2%) · ORD_ST FREQUENCY 히스토그램
인덱스 IX_ORD_BIG_ST_DT (ORD_ST, ORD_DT, ORD_SEQ): BLEVEL 2 · 리프 3,600블록 · 클러스터링 팩터 약 990,000(같은 상태 안에서 날짜순으로 줄 세우면 행마다 다른 블록이다)
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID)
인스턴스 OPTIMIZER_MODE는 기본값 ALL_ROWS · 통계 최신

원본 SQL

sql

SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_AMT, c.CUS_ID, c.CUS_NM, c.CUS_GD
  FROM T_ORD_BIG o, M_CUS c
 WHERE o.ORD_ST = 'CANCEL'
   AND c.CUS_ID = o.CUS_ID
 ORDER BY o.ORD_DT DESC, o.ORD_SEQ DESC

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

xplan

[첫 화면 20행을 받고 커서를 닫았을 때]
------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |     20 |00:00:00.09 |    5856 |          |
|   1 |  SORT ORDER BY      |           |      1 |  20000 |     20 |00:00:00.09 |    5856 | 1213K (0)|
|*  2 |   HASH JOIN         |           |      1 |  20000 |  20000 |00:00:00.08 |    5856 | 1557K (0)|
|   3 |    TABLE ACCESS FULL| M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|*  4 |    TABLE ACCESS FULL| T_ORD_BIG |      1 |  20000 |  20000 |00:00:00.07 |    5826 |          |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."CUS_ID"="O"."CUS_ID")
   4 - filter("O"."ORD_ST"='CANCEL')

[같은 SQL + /*+ INDEX(o IX_ORD_BIG_ST_DT) */ 로 끝까지 받았을 때 — 비교용]  Buffers 약 60,000 (T_ORD_BIG 쪽 20,063)

문제

히스토그램으로 E-Rows가 정확한데도 옵티마이저가 인덱스를 두고 FULL을 고른 이유를 쓰시오. 끝까지 받는다면 그 판단이 맞는지도 손익분기점으로 판단하시오.
프레임워크는 그대로 두고 첫 화면이 빨리 뜨도록 SQL을 고치시오. 그 방법이 통하는 조건과, 조민우의 안을 포함한 다른 방법의 대가를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 앞의 20행을 빨리 내는 계획을 고르라고 알린다 (결과 · 프레임워크는 그대로)
SELECT /*+ FIRST_ROWS(20) */
       o.ORD_SEQ, o.ORD_DT, o.ORD_AMT, c.CUS_ID, c.CUS_NM, c.CUS_GD
  FROM T_ORD_BIG o, M_CUS c
 WHERE o.ORD_ST = 'CANCEL'
   AND c.CUS_ID = o.CUS_ID
 ORDER BY o.ORD_DT DESC, o.ORD_SEQ DESC

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

xplan

[첫 화면 20행]
---------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  |      1 |        |     20 |00:00:00.01 |      63 |
|   1 |  NESTED LOOPS                   |                  |      1 |     20 |     20 |00:00:00.01 |      63 |
|   2 |   NESTED LOOPS                  |                  |      1 |     20 |     20 |00:00:00.01 |      43 |
|   3 |    TABLE ACCESS BY INDEX ROWID  | T_ORD_BIG        |      1 |     20 |     20 |00:00:00.01 |      23 |
|*  4 |     INDEX RANGE SCAN DESCENDING | IX_ORD_BIG_ST_DT |      1 |     20 |     20 |00:00:00.01 |       3 |
|*  5 |    INDEX UNIQUE SCAN            | PK_M_CUS         |     20 |      1 |     20 |00:00:00.01 |      20 |
|   6 |   TABLE ACCESS BY INDEX ROWID   | M_CUS            |     20 |      1 |     20 |00:00:00.01 |      20 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("O"."ORD_ST"='CANCEL')
   5 - access("C"."CUS_ID"="O"."CUS_ID")

모범 답안 서술

E-Rows 20,000은 히스토그램 덕에 정확하다. 문제는 숫자가 아니라 목표다. ALL_ROWS는 결과를 끝까지 받는 비용(처리량)으로 계획을 고르므로, 옵티마이저는 취소 주문 2만 행을 전부 돌려준다고 보고 셈했다. 그 2만 행을 인덱스로 찾으면 클러스터링 팩터가 약 99만이라 행마다 다른 테이블 블록을 읽어 약 20,000블록이 든다. 테이블을 한 번 통째로 읽으면 5,826블록이다. 그래서 FULL로 읽고 M_CUS와 해시 조인한 뒤 SORT ORDER BY로 정렬했고, 해시 조인과 정렬은 입력을 다 받아야 첫 행을 내므로 20행을 띄우기 전에 이 일을 모두 끝냈다(Buffers 5,856). 끝까지 받는다면 이 판단이 맞다. 04화에서 잰 T_ORD_BIG의 손익분기점은 0.58%였고, 취소 주문 2%는 그 세 배가 넘는다. 비교용으로 인덱스 힌트를 주고 끝까지 받으면 테이블 쪽만 20,063블록, 고객 조인까지 약 6만 블록이다. 옵티마이저는 틀리지 않았다. 화면이 20행만 받고 닫는다는 것을 모를 뿐이다. 그래서 /*+ FIRST_ROWS(20) */로 앞의 20행이 중요하다고 알린다. 그러면 IX_ORD_BIG_ST_DT를 거꾸로(INDEX RANGE SCAN DESCENDING) 읽어 가장 최근 취소 주문부터 차례로 꺼내고, 고객은 PK_M_CUS로 NL 조인해 정렬 없이 20행에서 멈춘다. Buffers는 5,856 → 63이고, 하루 1만 번이면 약 5,856만 → 63만 블록이다. 이 방법이 통하는 것은 인덱스가 조건(ORD_ST = 'CANCEL')과 ORDER BY 순서(ORD_DT DESC, ORD_SEQ DESC)를 그대로 담고 있어서다. ORD_DT가 전부 자정이라 같은 날짜가 많은데, 인덱스 끝에 ORD_SEQ가 있어 동률까지 인덱스 순서로 정해진다. 대가도 있다. 상담원이 끝까지 스크롤해 2만 행을 다 받으면 NL과 인덱스 경로라 FULL보다 비싸다(약 6만 블록). 첫 화면만 보는 화면이라 이 대가를 받아들인다. 조민우의 안처럼 OPTIMIZER_INDEX_COST_ADJ를 낮추면 이 SQL은 인덱스를 타겠지만, 시스템이나 세션 전체의 인덱스 비용이 함께 깎여 다른 SQL까지 바뀐다(OPT-034). 고칠 범위는 이 SQL 하나로 좁힌다.

해설

힌트의 n: FIRST_ROWS(20)은 "20행만 돌려줘"가 아니다. 결과는 전부 나오고, 계획을 고를 때 앞의 20행을 내는 비용을 기준으로 삼을 뿐이다. 행 수를 자르는 것은 ROWNUM이나 FETCH FIRST다(09화).
함정: 인덱스가 (ORD_ST, ORD_DT)뿐이었다면 ORDER BY의 ORD_SEQ 때문에 정렬이 남아 FIRST_ROWS(20)도 FULL과 정렬을 고를 수 있다. 힌트를 넣은 뒤 계획에 SORT ORDER BY가 없는지 꼭 확인해야 한다.
다른 정답: 앱을 고칠 수 있다면 앞 페이지 마지막 (ORD_DT, ORD_SEQ)를 기억하는 키 기반 페이징 + ROWNUM이 어느 페이지든 일정한 일량을 보장한다(09화, SORT 단원).
히스토그램을 걸어 E-Rows를 바로잡는 것과 계획을 바로잡는 것은 다른 일이다. 정확한 숫자로도 목표가 다르면 다른 계획이 맞다. 힌트를 넣은 이유(무한 스크롤이라 ROWNUM을 못 씀)를 힌트 대장에 적는다(06화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황