운영 상황판의 「최근 30일 판매 품목 수」가 열 때마다 주문상품 인덱스를 통째로 읽는다

중급SELECT버거341 / 700

상황

본사 운영실 상황판에는 최근 30일의 일별 판매 품목 수(주문상품 줄 수) 막대그래프가 있다. 상황판 PC 8대가 5분마다 새로 고쳐 하루 약 2,300번 호출된다. 개발자는 판매일 인덱스 ORD_ITEM_X01이 있으니 최근 날짜부터 30일 치만 읽고 멈출 것이라 생각해 ROWNUM <= 30을 걸었다. SQL은 본사 공통 집계 틀로 만들어서 /*+ USE_HASH_AGGREGATION */ 힌트가 붙어 있다. 이 틀은 2019년에 월 집계 하나가 정렬 집계로 TEMP를 넘긴 뒤 "집계는 해시로" 하라며 만든 것이다. DBA는 논리 읽기 상위 SQL에서 이 SQL을 찾았다. 실행할 때마다 0.2초가 걸리고 주문상품 인덱스를 통째로 읽는다.

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_NO · ITEM_ID · ORD_ITEM_QTY · ORD_DT VARCHAR2(8) NOT NULL · ORD_HMS VARCHAR2(6) NOT NULL · 2012-01-01 ~ 2013-12-31 · 하루 약 3,390줄 · 날짜순으로 쌓여 있다
인덱스 ORD_ITEM_PK (ORD_NO, ITEM_ID), ORD_ITEM_X01 (ORD_DT, ORD_HMS): BLEVEL 2 · 리프 약 8,770블록(한 블록에 약 280개) · ORD_DT가 NOT NULL이라 모든 행이 이 인덱스에 있다
통계는 최신이다.

원본 SQL

sql

SELECT ORD_DT, ITEM_LINES
  FROM (SELECT /*+ USE_HASH_AGGREGATION */
               ORD_DT, COUNT(*) AS ITEM_LINES
          FROM ORD_ITEM
         GROUP BY ORD_DT
         ORDER BY ORD_DT DESC)
 WHERE ROWNUM <= 30

실행계획 학습용 예시

xplan

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |              |      1 |        |     30 |00:00:00.21 |    8812 |       |       |          |
|*  1 |  COUNT STOPKEY           |              |      1 |        |     30 |00:00:00.21 |    8812 |       |       |          |
|   2 |   VIEW                   |              |      1 |    730 |     30 |00:00:00.21 |    8812 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY |              |      1 |    730 |     30 |00:00:00.21 |    8812 |  9216 |  9216 | 8192  (0)|
|   4 |     HASH GROUP BY        |              |      1 |    730 |    730 |00:00:00.21 |    8812 |  1200K|  1200K| 1421K (0)|
|   5 |      INDEX FAST FULL SCAN| ORD_ITEM_X01 |      1 |   2472K|   2472K|00:00:00.09 |    8812 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=30)
   3 - filter(ROWNUM<=30)

문제

ROWNUM <= 30을 걸었는데도 5번 줄이 인덱스를 통째로 읽는 까닭을 4번 줄 HASH GROUP BY가 일하는 방식으로 설명하고, 해시로 묶게 만든 것이 무엇인지 쓰시오.
최근 30일 치 인덱스 항목만 읽고 멈추도록 고치고, 고친 뒤의 실행계획에서 무엇이 멈추게 하는지 설명하시오. 공통 틀의 힌트를 어떻게 다룰지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 공통 틀의 USE_HASH_AGGREGATION 을 뗀다 · 판매일 역순으로 인덱스를 읽으며 묶다가 30번째 날짜에서 멈춘다
SELECT ORD_DT, ITEM_LINES
  FROM (SELECT /*+ INDEX_DESC(ORD_ITEM ORD_ITEM_X01) */
               ORD_DT, COUNT(*) AS ITEM_LINES
          FROM ORD_ITEM
         GROUP BY ORD_DT
         ORDER BY ORD_DT DESC)
 WHERE ROWNUM <= 30

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

xplan

----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |     30 |00:00:00.02 |     367 |
|*  1 |  COUNT STOPKEY                |              |      1 |        |     30 |00:00:00.02 |     367 |
|   2 |   VIEW                        |              |      1 |    730 |     30 |00:00:00.02 |     367 |
|   3 |    SORT GROUP BY NOSORT       |              |      1 |    730 |     30 |00:00:00.02 |     367 |
|   4 |     INDEX FULL SCAN DESCENDING| ORD_ITEM_X01 |      1 |   2472K|    101K|00:00:00.01 |     367 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=30)

모범 답안 서술

4번 줄 HASH GROUP BY는 들어오는 행을 날짜별 해시 칸에 더해 가므로, 마지막 행을 볼 때까지 어느 날짜의 건수도 끝났다고 말할 수 없다. 그래서 5번 줄이 인덱스 247만 항목(8,812블록)을 다 읽어야 3번 줄 SORT ORDER BY STOPKEY가 730개 묶음에서 30개를 고를 수 있다. ROWNUM <= 30은 묶음 30개를 남길 뿐 읽는 양은 줄이지 못했다. ORD_DT가 NOT NULL이라 인덱스에 모든 행이 있으므로, 옵티마이저가 테이블(55,265블록) 대신 더 작은 인덱스를 FAST FULL SCAN으로 읽은 것까지는 맞는 선택이다. 해시로 묶게 만든 것은 공통 집계 틀의 /*+ USE_HASH_AGGREGATION */이다. 이 힌트는 정렬로 묶는 방식(SORT GROUP BY)을 후보에서 빼 버리므로, 판매일 순서로 줄 선 인덱스를 거꾸로 읽으며 묶는 계획을 옵티마이저가 고를 수 없다. 힌트를 떼고(인덱스 방향은 INDEX_DESC로 적었다) 돌리면 4번 줄이 ORD_ITEM_X01을 거꾸로 읽고, 3번 줄 SORT GROUP BY NOSORT가 판매일이 바뀔 때마다 그 날짜의 건수를 한 줄씩 내보낸다. 1번 줄 COUNT STOPKEY는 30번째 날짜를 받은 순간 멈추므로 인덱스 항목은 최근 30일 치 약 10만 개(리프 약 360블록)만 읽힌다. Buffers는 8,812 → 367, 시간은 0.21 → 0.02초다. 해시 작업 공간도 정렬도 없다. 공통 틀의 힌트는 2019년 월 집계의 사정이었다. 틀에서 바로 빼면 그 틀로 만든 다른 집계가 정렬 집계로 바뀌어 TEMP를 쓸 수 있으므로, V$SQL에서 그 힌트가 든 SQL을 찾아 계획과 TEMP를 확인한 뒤 SQL마다 정한다. 힌트를 넣은 이유와 다시 볼 조건은 힌트 대장에 적는다(유효기간).

해설

NO_USE_HASH_AGGREGATION으로 반대로 강제하는 답: 힌트를 떼는 대신 NO_USE_HASH_AGGREGATION을 적어도 정렬 집계가 후보로 돌아온다. 하지만 힌트로 힌트를 덮는 일이라, 틀의 힌트가 왜 있었는지 대장에 남기지 않으면 다음 사람이 또 헤맨다.
계획을 꼭 확인한다: 정렬 집계가 골라져도 묶기와 ORDER BY … DESC가 하나로 합쳐져 SORT GROUP BY STOPKEY가 되면 끝까지 읽는다. 교재 환경(23ai)에서는 내림차순 묶기에서 이 계획이 나왔다. 3번 줄이 NOSORT이고 4번 줄 A-Rows가 30일 치인지를 본다.
범위 조건도 답이다: WHERE ORD_DT >= :from_dt처럼 최근 30일의 시작일을 주면 해시로 묶어도 30일 치만 읽는다. 다만 '주문이 있는 마지막 30일'과 '달력의 최근 30일'은 휴무일이 끼면 다르다. 화면이 어느 쪽을 원하는지 확인한다.
해시 집계는 묶음이 많을 때 메모리를 적게 쓰지만, 묶음 몇 개만 필요한 부분범위 처리를 막는다. 힌트를 SQL 틀에 박아 두면 모든 집계가 그 대가를 함께 치른다(교재 06화 힌트 유효기간 대장).
막대에 판매 수량 합계(SUM(ORD_ITEM_QTY))를 더하면 인덱스에 없는 컬럼이라 항목마다 테이블에 간다. 날짜순으로 쌓여 클러스터링 팩터가 좋아서 약 2,300블록이 더해지는 정도이고, 더 줄이려면 인덱스 끝에 수량을 붙인다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황