총건수 쿼리를 목록에 합쳤더니 1쪽까지 느려졌다

중급SELECT이커머스332 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

운영팀의 「주문 관리」는 전체 주문을 최신순으로 한 쪽에 20건씩 보여 주고 위에 「총 n건」을 띄운다. 지난달까지는 목록과 건수를 두 SQL로 보냈는데, 이번 배포에서 개발자가 "DB 왕복을 한 번 줄이겠다"며 COUNT(*) OVER ()로 둘을 합쳤다. 그 뒤로 1쪽을 여는 데 걸리는 시간이 0.01초에서 0.06초로 늘고 DB 서버 CPU가 눈에 띄게 올랐다. 이 화면은 하루 약 20만 번 열린다(주문 알림을 받으면 운영자가 새로고침한다).

테이블과 인덱스

orders: 34,908행 · 193블록 · PK(order_id) · order_date DATE(시분초 있음) · customer_id · status · total_amount
인덱스: PK(order_id, 리프 약 70블록) · IX_ORDERS_DT (order_date, order_id)(지난달 이 화면용으로 만들었다). 주문은 날짜순으로 쌓여 있다.
통계는 최신이다.

원본 SQL

sql

SELECT order_id, order_date, customer_id, status, total_amount,
       COUNT(*) OVER () AS total_cnt
  FROM orders
 ORDER BY order_date DESC, order_id DESC
 FETCH FIRST 20 ROWS ONLY

실행계획 학습용 예시

xplan

[합친 뒤 · 지금]
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |      1 |        |     20 |00:00:00.06 |     193 |
|*  1 |  VIEW                          |              |      1 |  34908 |     20 |00:00:00.06 |     193 |
|   2 |   WINDOW SORT                  |              |      1 |  34908 |  34908 |00:00:00.05 |     193 |
|   3 |    TABLE ACCESS FULL           | ORDERS       |      1 |  34908 |  34908 |00:00:00.01 |     193 |
-----------------------------------------------------------------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=20)
   2번 줄 WINDOW SORT 의 Used-Mem : 2381K (0)

[합치기 전 · 지난달 · 목록]
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |      1 |        |     20 |00:00:00.01 |       4 |
|*  1 |  VIEW                          |              |      1 |     20 |     20 |00:00:00.01 |       4 |
|*  2 |   WINDOW NOSORT STOPKEY        |              |      1 |     20 |     20 |00:00:00.01 |       4 |
|   3 |    TABLE ACCESS BY INDEX ROWID | ORDERS       |      1 |  34908 |     20 |00:00:00.01 |       4 |
|   4 |     INDEX FULL SCAN DESCENDING | IX_ORDERS_DT |      1 |     20 |     20 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=20)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORDER_DATE") DESC ,
              INTERNAL_FUNCTION("ORDER_ID") DESC )<=20)

[합치기 전 · 지난달 · 건수]   SELECT COUNT(*) FROM orders
  SORT AGGREGATE ← INDEX FAST FULL SCAN (PK) · Buffers 75

문제

합친 SQL의 2번 줄이 34,908행을 정렬하는 까닭을 COUNT(*) OVER ()와 부분범위 처리로 설명하시오.
합치기 전과 뒤의 일량을 하루 단위로 비교하고, "왕복을 한 번 줄였다"는 판단이 왜 손해였는지 쓰시오.
목록 SQL을 고치고, 「총 n건」을 어떻게 다룰지 대안을 두 가지 이상 비교하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 목록 : 분석 함수를 떼면 IX_ORDERS_DT 를 역순으로 읽다가 21건에서 멈춘다 (21번째는 '다음' 버튼을 켤지 보는 용도)
SELECT order_id, order_date, customer_id, status, total_amount
  FROM orders
 ORDER BY order_date DESC, order_id DESC
 FETCH FIRST 21 ROWS ONLY;

-- 「총 n건」 은 없앤다. 대략의 규모가 필요하면 앱이 몇 분마다 세어 둔 값(또는 NUM_ROWS)을 '약 3만 5천 건' 으로 보여 준다

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

xplan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |      1 |        |     21 |00:00:00.01 |       4 |
|*  1 |  VIEW                          |              |      1 |     21 |     21 |00:00:00.01 |       4 |
|*  2 |   WINDOW NOSORT STOPKEY        |              |      1 |     21 |     21 |00:00:00.01 |       4 |
|   3 |    TABLE ACCESS BY INDEX ROWID | ORDERS       |      1 |  34908 |     21 |00:00:00.01 |       4 |
|   4 |     INDEX FULL SCAN DESCENDING | IX_ORDERS_DT |      1 |     21 |     21 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=21)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORDER_DATE") DESC ,
              INTERNAL_FUNCTION("ORDER_ID") DESC )<=21)

모범 답안 서술

COUNT(*) OVER ()는 결과 전체의 행 수를 행마다 붙이는 분석 함수다. 첫 행에 붙일 값부터 전체 건수이므로 34,908행을 다 읽기 전에는 한 행도 내보낼 수 없다. 그래서 IX_ORDERS_DT를 거꾸로 읽다가 20건에서 멈추던 부분범위 처리(WINDOW NOSORT STOPKEY)가 사라지고, 옵티마이저는 테이블 전체를 읽어(193블록) 2번 줄 WINDOW SORT에서 34,908행을 정렬하고 건수를 센 뒤에야 20건을 고른다(Used-Mem 약 2.4MB). 합치기 전에는 목록 4블록, 건수 75블록이었다. 하루 20만 번으로 보면 합치기 전에는 (4 + 75) × 20만 = 약 1,580만 블록이고, 합친 뒤에는 193 × 20만 = 약 3,860만 블록에 3만 5천 행 정렬이 20만 번 더해진다. 왕복은 한 번 줄었지만 아낀 것은 네트워크 왕복 한 번(1ms 남짓)이고, 대신 클릭마다 전체를 읽고 정렬하는 일을 얻었다. CPU가 오른 것은 이 정렬 때문이다. 목록에서는 분석 함수를 떼고 21건을 가져와 21번째가 있으면 「다음」 버튼을 켠다. 한 번 열 때 193 → 4블록이고 정렬이 없다. 「총 n건」은 쓰임새를 물어 정한다. 숫자가 없어도 되면 지우는 것이 가장 싸다(추가 쿼리 0). 대략의 규모만 보면 되면 통계의 행 수(NUM_ROWS)나 앱이 몇 분마다 세어 둔 값을 "약 3만 5천 건"으로 보여 준다. 조건이 붙는 검색 화면이라면 ROWNUM <= 1001처럼 상한까지만 세어 "1,000건 이상"으로 보여 준다. 정확한 건수를 클릭마다 세는 것만은 피한다.

해설

COUNT(*) OVER ()가 늘 나쁜 것은 아니다. 결과를 어차피 다 읽는 화면(엑셀 내려받기, 집계 리포트)에서는 건수를 따로 세는 쿼리 한 번을 아낀다. 문제는 20건에서 멈출 수 있는 화면에 "전체를 보라"는 함수를 붙인 데 있다.
ROWNUM으로 바꿔도 같다. 인라인 뷰 안에 COUNT(*) OVER ()를 두고 바깥에서 ROWNUM <= 20으로 잘라도 분석 함수가 전체를 요구하므로 똑같이 다 읽는다.
건수 SQL을 따로 두더라도 COUNT(*)는 클릭마다 PK 인덱스를 훑는 75블록이다. 하루 20만 번이면 1,500만 블록이므로 "화면을 열 때마다"가 아니라 "버튼을 누를 때만" 세게 하는 것도 방법이다.
왕복 수를 줄이는 것은 Call이 많을 때의 처방이다(교재 09화 §11, 15화). 한 번 줄여서 얻는 것과 그 대가로 늘어나는 일량을 같은 단위로 비교한 뒤에 정한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황