DB를 19c로 올린 뒤 하루치 주문 목록은 첫 30줄을 보여 주기 전에 1,378블록을 읽는다

중급SELECT버거102 / 700

상황

버거원 본사 고객센터의 「일별 주문 조회」는 날짜를 하나 고르면 그날 주문을 시각 순으로 보여 준다. 화면은 커서를 연 뒤 30줄씩 가져오고(Fetch), 상담원이 스크롤하면 다음 30줄을 가져온다. 대부분 첫 화면에서 찾는 주문을 본다. 상담원들이 하루 3천 번쯤 연다. 지난 주말 DB를 11g에서 19c로 올린 뒤 첫 화면이 늦게 뜬다. 오과장이 보관해 둔 11g 메모에는 "TABLE ACCESS BY INDEX ROWID, SORT 없음, 첫 화면 33블록"이라고 적혀 있다. SQL과 인덱스는 바꾸지 않았다. 아래는 2012-01-01(1,371건)을 열고 첫 화면만 본 뒤 닫았을 때다.

테이블과 인덱스

ORD_RANDOM(운영본): 1,000,000행 · 22,414블록 · 서버 교체 때 순서 없이 다시 적재되어 행마다 다른 블록에 있다 · 하루 약 1,370건 · ORD_DT VARCHAR2(8)
인덱스 ORD_RANDOM_X01 (ORD_DT, ORD_HMS): BLEVEL 2 · 리프 3,774 · CLUSTERING_FACTOR 999,958 · 통계 최신

원본 SQL

sql

SELECT o.ORD_NO, o.ORD_HMS, o.SHOP_NO
  FROM ORD_RANDOM o
 WHERE o.ORD_DT = :v_dt
 ORDER BY o.ORD_HMS

실행계획 학습용 예시 · 하루치 Buffers는 교재 04화 실측

xplan

[19c · 지금]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |     30 |00:00:00.02 |    1378 |          |
|   1 |  SORT ORDER BY                       |                |      1 |   1370 |     30 |00:00:00.02 |    1378 |   59392  |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD_RANDOM     |      1 |   1370 |   1371 |00:00:00.02 |    1378 |          |
|*  3 |    INDEX RANGE SCAN                  | ORD_RANDOM_X01 |      1 |   1370 |   1371 |00:00:00.01 |       7 |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("O"."ORD_DT"=:V_DT)

[11g · 오과장 메모의 계획]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |     30 |00:00:00.01 |      33 |          |
|   1 |  TABLE ACCESS BY INDEX ROWID         | ORD_RANDOM     |      1 |   1370 |     30 |00:00:00.01 |      33 |          |
|*  2 |   INDEX RANGE SCAN                   | ORD_RANDOM_X01 |      1 |   1370 |     30 |00:00:00.01 |       3 |          |
------------------------------------------------------------------------------------------------------------------------

문제

첫 화면 30줄만 보는데 19c에서는 1,378블록을 읽는 이유를, 두 계획에서 달라진 줄 두 개를 근거로 설명하시오. 정렬할 필요가 없던 SQL에 SORT ORDER BY가 생긴 이유도 설명하시오.
첫 화면이 다시 30줄만 읽고 뜨도록 SQL을 고치시오. 시스템 전체에서 이 기능을 끄는 방법을 쓰지 않는 이유와, 이 화면에 남는 비용도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- BATCHED 를 끄면 인덱스 순서(ORD_DT 고정 · ORD_HMS 순) 그대로 나오므로 정렬이 필요 없다 → 30줄에서 멈춘다
SELECT /*+ NO_BATCH_TABLE_ACCESS_BY_ROWID(o) */
       o.ORD_NO, o.ORD_HMS, o.SHOP_NO
  FROM ORD_RANDOM o
 WHERE o.ORD_DT = :v_dt
 ORDER BY o.ORD_HMS;
-- 다른 답 : /*+ FIRST_ROWS(30) */ — 첫 30행을 빨리 내는 계획을 고르게 한다

개선 후 실행계획 학습용 예시 · 하루치 Buffers는 교재 04화 실측

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |      1 |        |     30 |00:00:00.01 |      33 |
|   1 |  TABLE ACCESS BY INDEX ROWID | ORD_RANDOM     |      1 |   1370 |     30 |00:00:00.01 |      33 |
|*  2 |   INDEX RANGE SCAN           | ORD_RANDOM_X01 |      1 |   1370 |     30 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------
   2 - access("O"."ORD_DT"=:V_DT)

모범 답안 서술

두 계획에서 달라진 줄은 둘이다. 테이블 줄이 TABLE ACCESS BY INDEX ROWID BATCHED로 바뀌었고, 그 위에 SORT ORDER BY가 생겼다. 인덱스 (ORD_DT, ORD_HMS)에서 ORD_DT를 하루로 고정하면 항목은 이미 ORD_HMS 순이라서 11g에서는 정렬이 필요 없었다. BATCHED는 인덱스에서 얻은 ROWID를 모아 두었다가 테이블 블록을 한꺼번에 읽는 방식이라, 나오는 행이 인덱스 순서라는 보장이 없다. 그래서 옵티마이저가 BATCHED를 고르면서 ORDER BY를 지키려고 SORT ORDER BY를 붙였다. 정렬은 마지막 행까지 다 받아야 첫 행을 내보낼 수 있다. 상담원은 첫 30줄만 보는데도 그날 1,371행을 모두 찾아가 정렬한 뒤에야 첫 화면이 뜬다. 11g에서는 30줄을 가져오면 멈추던 부분범위 처리가 사라진 것이다. 이 테이블은 행마다 다른 블록에 있으므로(CF 999,958) 1,371행이 그대로 1,371블록이 되어 첫 화면 Buffers가 33 → 1,378로 늘었다. SQL에 NO_BATCH_TABLE_ACCESS_BY_ROWID(o) 힌트를 붙여 이 SQL에서만 BATCHED를 끈다. 그러면 인덱스 순서 그대로 행이 나오므로 정렬이 필요 없고, 30줄을 가져오면 멈춘다(첫 화면 Buffers 1,378 → 33). FIRST_ROWS(30) 힌트도 같은 계획을 고르게 하는 다른 답이다. 하루 3천 번이면 약 413만 → 10만 블록이다. OPTIMIZER_FEATURES_ENABLE을 11g로 되돌리거나 숨은 파라미터로 시스템 전체에서 BATCHED를 끄지는 않는다. 정렬이 원래 있던 SQL이나 다 읽는 SQL에서는 BATCHED가 디스크 읽기를 모아 시간을 줄이므로, 문제가 된 SQL만 골라 고친다. 이 화면에 남는 비용은 첫 화면 30줄에 30블록이라는 점이다. 원인은 운영본이 순서 없이 적재되어 CF가 행 수와 같다는 데 있다. 근본 처방은 테이블을 주문 시각 순으로 다시 쌓는 것이고(ACC-028), 그러면 30줄이 한두 블록에 모인다.

해설

함정: Buffers만 보고 "1,378블록은 하루치를 다 읽는 원래 비용"이라고 넘기기 쉽다. 이 화면의 일은 30줄을 보여 주는 것이다. 몇 행을 읽고 멈추는지를 먼저 본다(교재 09화 §1).
11g 계획과 같은 모양이 되었는지는 SORT ORDER BY가 없고 1번 줄 A-Rows가 30인지로 확인한다. 화면을 닫지 않고 끝까지 스크롤하면 어느 계획이든 1,371행을 다 읽는다.
같은 업그레이드 뒤 ORDER BY가 없는 SQL은 반대로 결과 순서가 흔들린다(ACC-025). 업그레이드 뒤에는 BATCHED가 새로 붙은 계획 가운데 SORT가 함께 생긴 것과 ORDER BY가 없는 것을 골라 본다.
키 기반 페이징(교재 09화 §9)으로 바꾸면 스크롤할 때마다 다음 30줄만 읽는다. 화면 동작을 바꿀 수 있다면 함께 검토한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 하루치 Buffers는 교재 04화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 하루치 Buffers는 교재 04화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황