「매장 월 순위」 200줄이 1.4초씩 걸리고, 월초에는 논리 읽기 1위다

기초SELECT버거354 / 700

상황

본사 영업관리팀의 「매장 월 순위」는 고른 달의 매장별 주문 수와 전체 대비 비중, 순위를 보여 준다. 주문 수가 같은 매장은 같은 순위를 받고 다음 순위는 그만큼 건너뛴다(공동 3위가 둘이면 다음은 5위). 매장 점장들도 자기 매장 순위를 보려고 열어서 월초 사흘 동안 하루 1,500번쯤 호출된다. 한 번 여는 데 1.4초가 걸리고, 월초에는 DBA가 뽑은 논리 읽기 순위에서 1위다. 아래는 2013년 12월로 연 실행계획이다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) · SHOP_NO 매장 200개 · ORD_X01 (ORD_DT, ORD_HMS) · 주문일 순으로 쌓여 있다(CF 22,223).
2013년 12월 주문은 42,408건이다. 인덱스 범위는 리프 약 140블록, 테이블은 951블록이다.
매장별 12월 주문 수는 168 ~ 259건이고, 서로 다른 값은 67가지다(주문 수가 같은 매장이 여럿 있다).
SHOP_NO로 시작하는 인덱스는 없다. 통계는 최신이다.

원본 SQL

sql

SELECT s.SHOP_NO, s.ord_cnt,
       ROUND(s.ord_cnt * 100 / (SELECT COUNT(*)
                                  FROM ORD
                                 WHERE ORD_DT BETWEEN :d_from AND :d_to), 2) AS pct,
       (SELECT COUNT(*) + 1
          FROM (SELECT SHOP_NO, COUNT(*) AS ord_cnt
                  FROM ORD
                 WHERE ORD_DT BETWEEN :d_from AND :d_to
                 GROUP BY SHOP_NO) x
         WHERE x.ord_cnt > s.ord_cnt)                                      AS rnk
  FROM (SELECT SHOP_NO, COUNT(*) AS ord_cnt
          FROM ORD
         WHERE ORD_DT BETWEEN :d_from AND :d_to
         GROUP BY SHOP_NO) s
 ORDER BY rnk, s.SHOP_NO

실행계획 학습용 예시

xplan

[2013년 12월]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |         |      1 |        |    200 |00:00:01.42 |   74535 |          |
|   1 |  SORT AGGREGATE                         |         |      1 |      1 |      1 |00:00:00.01 |     143 |          |
|*  2 |   FILTER                                |         |      1 |        |  42408 |00:00:00.01 |     143 |          |
|*  3 |    INDEX RANGE SCAN                     | ORD_X01 |      1 |  42408 |  42408 |00:00:00.01 |     143 |          |
|   4 |  SORT AGGREGATE                         |         |     67 |      1 |     67 |00:00:01.36 |   73298 |          |
|*  5 |   VIEW                                  |         |     67 |     10 |   6633 |00:00:01.36 |   73298 |          |
|   6 |    HASH GROUP BY                        |         |     67 |    200 |  13400 |00:00:01.35 |   73298 | 1186K (0)|
|*  7 |     FILTER                              |         |     67 |        |   2841K|00:00:00.97 |   73298 |          |
|   8 |      TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |     67 |  42408 |   2841K|00:00:00.71 |   73298 |          |
|*  9 |       INDEX RANGE SCAN                  | ORD_X01 |     67 |  42408 |   2841K|00:00:00.24 |    9581 |          |
|  10 |  SORT ORDER BY                          |         |      1 |    200 |    200 |00:00:01.42 |   74535 | 2048 (0) |
|  11 |   VIEW                                  |         |      1 |    200 |    200 |00:00:00.03 |    1094 |          |
|  12 |    HASH GROUP BY                        |         |      1 |    200 |    200 |00:00:00.03 |    1094 | 1186K (0)|
|* 13 |     FILTER                              |         |      1 |        |  42408 |00:00:00.02 |    1094 |          |
|  14 |      TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |      1 |  42408 |  42408 |00:00:00.01 |    1094 |          |
|* 15 |       INDEX RANGE SCAN                  | ORD_X01 |      1 |  42408 |  42408 |00:00:00.01 |     143 |          |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:D_FROM<=:D_TO)
   3 - access("ORD_DT">=:D_FROM AND "ORD_DT"<=:D_TO)
   5 - filter("X"."ORD_CNT">:B1)
   7 - filter(:D_FROM<=:D_TO)
   9 - access("ORD_DT">=:D_FROM AND "ORD_DT"<=:D_TO)
  13 - filter(:D_FROM<=:D_TO)
  15 - access("ORD_DT">=:D_FROM AND "ORD_DT"<=:D_TO)

문제

화면은 200줄인데 74,535블록을 읽는 까닭을 실행계획의 Starts로 설명하시오. 200번이 아니라 67번인 까닭도 쓰시오.
12월 주문을 한 번만 읽도록 SQL을 고치시오. 주문 수가 같은 매장의 순위가 지금 화면과 같게 나오는 함수를 고르고 그 까닭을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 한 번 묶은 200줄 위에서 비중과 순위를 분석 함수로 구한다
SELECT SHOP_NO,
       COUNT(*)                                          AS ord_cnt,
       ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100, 2) AS pct,
       RANK() OVER (ORDER BY COUNT(*) DESC)              AS rnk
  FROM ORD
 WHERE ORD_DT BETWEEN :d_from AND :d_to
 GROUP BY SHOP_NO
 ORDER BY rnk, SHOP_NO

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

xplan

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |         |      1 |        |    200 |00:00:00.03 |    1094 |          |
|   1 |  SORT ORDER BY                         |         |      1 |    200 |    200 |00:00:00.03 |    1094 | 2048 (0) |
|   2 |   WINDOW SORT                          |         |      1 |    200 |    200 |00:00:00.03 |    1094 | 2048 (0) |
|   3 |    HASH GROUP BY                       |         |      1 |    200 |    200 |00:00:00.03 |    1094 | 1186K (0)|
|*  4 |     FILTER                             |         |      1 |        |  42408 |00:00:00.02 |    1094 |          |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| ORD    |      1 |  42408 |  42408 |00:00:00.01 |    1094 |          |
|*  6 |       INDEX RANGE SCAN                 | ORD_X01 |      1 |  42408 |  42408 |00:00:00.01 |     143 |          |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(:D_FROM<=:D_TO)
   6 - access("ORD_DT">=:D_FROM AND "ORD_DT"<=:D_TO)

모범 답안 서술

순위를 구하는 스칼라 서브쿼리(4번 줄)가 Starts 67이다. 이 서브쿼리는 매장마다 '나보다 주문이 많은 매장 수 + 1'을 세는데, 그러려면 12월 주문 42,408건을 다시 읽어 200개 매장으로 묶어야 한다. 입력값이 바깥 매장의 주문 수(s.ord_cnt)이고 서로 다른 값이 67가지라서, 스칼라 서브쿼리 캐시가 주문 수가 같은 매장끼리만 결과를 재사용했다. 그래서 200번이 아니라 67번 돌았다. 한 번에 1,094블록씩 73,298블록이고, 본문과 비중 계산을 더해 74,535블록이다. 고친 SQL은 12월 주문을 한 번 읽어 매장별로 묶고(200줄), 그 200줄 위에서 비중은 RATIO_TO_REPORT(COUNT(*)) OVER ()로, 순위는 RANK() OVER (ORDER BY COUNT(*) DESC)로 구한다. 분석 함수는 GROUP BY가 끝난 뒤의 줄에 적용되므로 200줄을 한 번 정렬하면 된다(WINDOW SORT). 지금 화면의 순위는 '나보다 많은 매장 수 + 1'이라서 동점이면 같은 순위를 받고 다음 순위를 건너뛴다. 이것이 RANK의 정의와 같다. DENSE_RANK는 건너뛰지 않고(공동 3위 다음이 4위), ROW_NUMBER는 동점에도 서로 다른 번호를 아무 순서로 매기므로 화면과 달라진다. Buffers는 74,535 → 1,094, 시간은 1.42초 → 0.03초다.

해설

스칼라 서브쿼리 캐시는 입력값 종류에 기댄다: 매장 주문 수가 모두 달랐다면 200번 돌았을 것이고, 기간을 1년으로 넓히면 값이 거의 다 달라져 200번에 가까워진다. 67번은 운이다(교재 07화 스칼라 서브쿼리 캐시).
비중 계산(1번 줄)은 인덱스만 읽었다: COUNT(*)는 ORD_DT만 있으면 되므로 ORD_X01 범위 143블록으로 끝났다. 순위 서브쿼리는 매장별로 묶으려고 SHOP_NO가 필요해 테이블까지 읽는다.
ORDER BY rnk, SHOP_NO는 분석 함수의 정렬 기준과 달라 WINDOW SORT 뒤에 정렬이 한 번 더 붙는다. 200줄이라 무시할 만하다.
동점을 깨야 하는 화면(예: '1위 한 곳만')이라면 ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC, SHOP_NO)처럼 정렬 키를 유일하게 하고, 동점을 깨는 규칙을 화면 요구서에 적는다.
(SHOP_NO, ORD_DT) 인덱스로 테이블을 안 읽게 할 수도 있지만, 이 문제의 일량은 같은 집계를 67번 되풀이한 데서 나왔다. 되풀이를 없애면 1,094블록이고, 인덱스는 그다음 이야기다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황