DB를 19c로 올린 뒤 고객센터의 「최근 주문 5건」이 가끔 날짜 순서가 뒤섞여 나온다

중급SELECT한빛유통100 / 700

상황

고객센터 「최근 주문 5건」 칸은 고객 번호를 넣으면 그 고객의 최근 주문 다섯 건을 보여 준다. 상담원들이 하루 1만 번쯤 연다. 2016년에 만든 SQL에는 ORDER BY가 없고, INDEX_DESC 힌트와 ROWNUM으로 최근 5건을 뽑는다. 주석은 "정렬 없이 인덱스 역순으로 최근 5건 (2016 강도현)"이다. 지난달 운영 DB를 11g에서 19c로 올린 뒤, 상담원 신고가 열두 건 들어왔다. "가끔 5월 주문이 6월 주문보다 위에 나오고, 어떤 때는 어제 주문이 빠져 있다." 다시 열면 멀쩡하고, 주로 아침 첫 조회에서 생긴다. 실행계획은 한 줄만 바뀌었다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록(블록당 약 112행) · ORD_SEQ 순으로 쌓였다 · ORD_DT DATE(시분초 있음) · 고객당 50건, 한 고객의 주문은 ORD_SEQ 2,000건 간격이라 50건이 50블록에 흩어져 있다
인덱스 IX_T_ORD_CUS_DT (CUS_ID, ORD_DT): BLEVEL 1 · 리프 약 375블록 · CLUSTERING_FACTOR 약 100,000 · 통계 최신

원본 SQL

sql

SELECT /*+ INDEX_DESC(o IX_T_ORD_CUS_DT) */
       o.ORD_SEQ, o.ORD_DT, o.ORD_ST, o.ORD_AMT
  FROM T_ORD o
 WHERE o.CUS_ID = :v_cus
   AND ROWNUM <= 5

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로

xplan

[업그레이드 전 11g: 보관해 둔 계획]
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                 |      1 |        |      5 |00:00:00.01 |       7 |
|*  1 |  COUNT STOPKEY                       |                 |      1 |        |      5 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS BY INDEX ROWID        | T_ORD           |      1 |      5 |      5 |00:00:00.01 |       7 |
|*  3 |    INDEX RANGE SCAN DESCENDING       | IX_T_ORD_CUS_DT |      1 |     50 |      5 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------------

[지금 19c]
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                 |      1 |        |      5 |00:00:00.01 |       7 |
|*  1 |  COUNT STOPKEY                       |                 |      1 |        |      5 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD           |      1 |      5 |      5 |00:00:00.01 |       7 |
|*  3 |    INDEX RANGE SCAN DESCENDING       | IX_T_ORD_CUS_DT |      1 |     50 |      5 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=5)
   3 - access("O"."CUS_ID"=:V_CUS)

문제

업그레이드 전후 계획에서 달라진 줄을 짚고, 그 변화 때문에 이 SQL이 '최근 5건'과 그 순서를 보장하지 못하게 된 이유를 설명하시오. Buffers가 7로 같은 이유도 설명하시오.
늘 최근 5건이 날짜 역순으로 나오도록 SQL을 고치시오. 고친 SQL이 정렬 없이 5건에서 멈추는지 확인하는 방법과, 멈추지 않을 때 어떻게 할지도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 결과의 순서는 ORDER BY 로만 정한다 · 5건은 정렬된 결과에서 자른다
SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
  FROM (SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_ST, o.ORD_AMT
          FROM T_ORD o
         WHERE o.CUS_ID = :v_cus
         ORDER BY o.ORD_DT DESC)
 WHERE ROWNUM <= 5;
-- 계획에 SORT ORDER BY STOPKEY 가 보이면 (BATCHED 를 고르면서 정렬을 붙인 경우)
--   안쪽 SELECT 에 /*+ NO_BATCH_TABLE_ACCESS_BY_ROWID(o) */ 를 붙여 인덱스 역순 그대로 멈추게 한다

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로

xplan

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                 |      1 |        |      5 |00:00:00.01 |       7 |
|*  1 |  COUNT STOPKEY                       |                 |      1 |        |      5 |00:00:00.01 |       7 |
|   2 |   VIEW                               |                 |      1 |      5 |      5 |00:00:00.01 |       7 |
|   3 |    TABLE ACCESS BY INDEX ROWID       | T_ORD           |      1 |     50 |      5 |00:00:00.01 |       7 |
|*  4 |     INDEX RANGE SCAN DESCENDING      | IX_T_ORD_CUS_DT |      1 |     50 |      5 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------------
   1 - filter(ROWNUM<=5)
   4 - access("O"."CUS_ID"=:V_CUS)

[만약 SORT ORDER BY STOPKEY + BATCHED 가 나오면]  50건을 다 읽고 정렬 · Buffers 약 52 → NO_BATCH 힌트로 7

모범 답안 서술

달라진 줄은 2번 하나다. TABLE ACCESS BY INDEX ROWID가 TABLE ACCESS BY INDEX ROWID BATCHED로 바뀌었다. BATCHED는 인덱스에서 얻은 ROWID 여러 개를 모아 두었다가 테이블 블록을 한꺼번에 읽는 방식이다. 블록이 버퍼 캐시에 다 있으면 행은 인덱스 순서대로 나오지만, 디스크에서 읽어야 할 때는 블록이 도착하는 순서대로 행이 나올 수 있다. 이 SQL은 ORDER BY 없이 '인덱스 역순으로 읽으면 결과도 날짜 역순'이라는 가정에 기대고 있었다. 그런데 오라클이 결과의 순서를 보장하는 것은 ORDER BY뿐이고, 힌트는 접근 경로만 고른다. 그래서 순서가 뒤섞였다. ROWNUM은 나오는 순서대로 5건을 자르므로, 순서가 바뀌면 5건의 내용도 최근 5건이 아닐 수 있다. 아침 첫 조회에서만 생긴 것은 그때 블록이 캐시에 없었기 때문이다. Buffers가 7로 같은 것은 BATCHED가 읽는 블록 수를 줄이지 않기 때문이다. 줄이는 것은 디스크 읽기 요청을 보내는 횟수와 기다리는 시간이다. 그래서 성능 수치로는 아무 일도 없어 보였다. 고친 SQL은 인라인 뷰에서 ORDER BY ORD_DT DESC로 정렬한 결과를 바깥에서 ROWNUM <= 5로 자른다. 인덱스가 (CUS_ID, ORD_DT)이므로 옵티마이저는 인덱스를 역순으로 읽어 ORDER BY를 대신하고, COUNT STOPKEY로 5건에서 멈춘다(Buffers 7 그대로). 이제 순서는 ORDER BY가 보장하고, 인덱스는 그 일을 싸게 할 뿐이다. INDEX_DESC 힌트는 필요 없으므로 뗀다. 고친 뒤에는 실행계획에 SORT ORDER BY가 없는지, COUNT STOPKEY 아래에 INDEX RANGE SCAN DESCENDING이 있는지 확인한다. 옵티마이저가 BATCHED를 고르면서 SORT ORDER BY STOPKEY를 붙이면 그 고객의 50건을 다 읽고 정렬하므로 약 52블록이 된다. 결과는 맞지만 부분범위 처리가 사라진다. 그럴 때는 안쪽 SELECT에 NO_BATCH_TABLE_ACCESS_BY_ROWID(o) 힌트를 붙여 정렬 없이 멈추게 한다.

해설

함정: INDEX_DESC + ROWNUM은 11g 시절 많이 쓰던 Top-N 방식이다. 결과가 맞은 것은 우연히 그 실행 방식이 순서를 지켰기 때문이다. 업그레이드, 인덱스 변경, 병렬 처리 하나로 깨질 수 있다.
BATCHED가 나빠진 것은 아니다. 디스크 읽기를 모아서 처리하므로 대개 더 빠르다. 위험한 것은 ORDER BY 없이 순서에 기대는 SQL이다. 업그레이드 전에 INDEX_DESC와 ROWNUM을 함께 쓰는 SQL을 소스에서 찾아 두면 좋다.
ORD_DT가 같은 주문이 경계에 걸리면 5건이 흔들린다. 시분초가 있어 드물지만, 정렬 키를 유일하게 하려면 인덱스를 (CUS_ID, ORD_DT, ORD_SEQ)로 만들고 ORDER BY ORD_DT DESC, ORD_SEQ DESC로 쓴다(교재 09화).
FETCH FIRST 5 ROWS ONLY로 써도 같다. 시스템 전체에서 BATCHED를 끄는 숨은 파라미터는 다른 SQL의 이득까지 없애므로 쓰지 않는다(ACC-030).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황