JOIN-061

카테고리 페이지의 「이 분류 인기 상품」이 491블록만 읽는데 한 번에 0.1초 가까이 걸린다

기초SELECT이커머스211 / 700

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

상황

상품 목록의 카테고리 페이지에는 그 분류의 상품을 배송 완료 기준 판매량 순으로 보여 주는 「이 분류 인기 상품」 칸이 있다. 카테고리를 열 때마다 돌아서 하루 2만 번 호출된다. 개발자는 주문 → 주문상품 → 상품 순서로 해시 조인을 고정해 두었다. 읽는 블록이 491개뿐이라 아무도 의심하지 않았는데, AWR을 보니 이 SQL 하나가 DB CPU의 6%를 쓴다. 아래는 상품 6개가 있는 소분류(category_id = 23)를 연 실행계획이다.

테이블과 인덱스

orders: 34,908행 · 193블록 · PK만 있음 · status 6종, 배송 완료('DELIVERED') 24,436건(70%)
order_items: 73,498행 · 290블록 · PK만 있음(order_id, product_id에 인덱스 없음) · 소분류 23의 상품 6개가 들어간 줄 1,577줄, 그중 배송 완료 주문의 줄 1,104줄
products: 280행 · 8블록 · PK만 있음 · 소분류 하나에 상품 5 ~ 7개
인덱스를 더하려면 승인 절차를 거쳐야 해서 이번에는 SQL만 고친다 · 통계 최신

원본 SQL

sql

SELECT /*+ LEADING(o i p) USE_HASH(i p) */
       p.product_id, p.name,
       COUNT(DISTINCT o.order_id) AS ord_cnt, SUM(i.quantity) AS qty
  FROM orders o, order_items i, products p
 WHERE o.status      = 'DELIVERED'
   AND i.order_id    = o.order_id
   AND p.product_id  = i.product_id
   AND p.category_id = :cat
 GROUP BY p.product_id, p.name
 ORDER BY qty DESC

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |      1 |        |      6 |00:00:00.09 |     491 |           |
|   1 |  SORT ORDER BY          |             |      1 |      6 |      6 |00:00:00.09 |     491 |  2048 (0) |
|   2 |   HASH GROUP BY         |             |      1 |      6 |      6 |00:00:00.09 |     491 | 1260K (0) |
|*  3 |    HASH JOIN            |             |      1 |   1103 |   1104 |00:00:00.09 |     491 | 1452K (0) |
|*  4 |     TABLE ACCESS FULL   | PRODUCTS    |      1 |      6 |      6 |00:00:00.01 |       8 |           |
|*  5 |     HASH JOIN           |             |      1 |  51449 |  51463 |00:00:00.07 |     483 | 2734K (0) |
|*  6 |      TABLE ACCESS FULL  | ORDERS      |      1 |  24436 |  24436 |00:00:00.01 |     193 |           |
|   7 |      TABLE ACCESS FULL  | ORDER_ITEMS |      1 |  73498 |  73498 |00:00:00.02 |     290 |           |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("P"."PRODUCT_ID"="I"."PRODUCT_ID")
   4 - filter("P"."CATEGORY_ID"=:CAT)
   5 - access("I"."ORDER_ID"="O"."ORDER_ID")
   6 - filter("O"."STATUS"='DELIVERED')

문제

Buffers가 491로 작은데도 시간이 걸리는 이유를 5번 줄과 3번 줄의 A-Rows와 Used-Mem으로 설명하시오.
조인 순서를 고쳐 SQL을 작성하고, 고친 뒤에 Buffers와 중간 결과가 각각 어떻게 바뀌는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 분류 조건이 있는 상품 6개를 먼저 build 로 올리고 주문상품을 대어 1,577줄만 남긴 뒤, 그 결과에 주문을 맞댄다
SELECT /*+ LEADING(p i o) USE_HASH(i o) */
       p.product_id, p.name,
       COUNT(DISTINCT o.order_id) AS ord_cnt, SUM(i.quantity) AS qty
  FROM orders o, order_items i, products p
 WHERE o.status      = 'DELIVERED'
   AND i.order_id    = o.order_id
   AND p.product_id  = i.product_id
   AND p.category_id = :cat
 GROUP BY p.product_id, p.name
 ORDER BY qty DESC

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

xplan

-------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |      1 |        |      6 |00:00:00.03 |     491 |           |
|   1 |  SORT ORDER BY          |             |      1 |      6 |      6 |00:00:00.03 |     491 |  2048 (0) |
|   2 |   HASH GROUP BY         |             |      1 |      6 |      6 |00:00:00.03 |     491 | 1260K (0) |
|*  3 |    HASH JOIN            |             |      1 |   1103 |   1104 |00:00:00.03 |     491 | 1355K (0) |
|*  4 |     HASH JOIN           |             |      1 |   1575 |   1577 |00:00:00.02 |     298 | 1290K (0) |
|*  5 |      TABLE ACCESS FULL  | PRODUCTS    |      1 |      6 |      6 |00:00:00.01 |       8 |           |
|   6 |      TABLE ACCESS FULL  | ORDER_ITEMS |      1 |  73498 |  73498 |00:00:00.01 |     290 |           |
|*  7 |     TABLE ACCESS FULL   | ORDERS      |      1 |  24436 |  24436 |00:00:00.01 |     193 |           |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("I"."ORDER_ID"="O"."ORDER_ID")
   4 - access("P"."PRODUCT_ID"="I"."PRODUCT_ID")
   5 - filter("P"."CATEGORY_ID"=:CAT)
   7 - filter("O"."STATUS"='DELIVERED')

모범 답안 서술

Buffers 491은 세 테이블을 한 번씩 FULL로 읽는 몫(193 + 290 + 8)이라 순서를 바꿔도 줄지 않는다. 시간은 다른 곳에서 나온다. 5번 줄 해시 조인이 배송 완료 주문 24,436건을 build로 올리고(2,734K) 주문상품 73,498줄을 모두 대어 51,463줄을 만든다. 이 5만 줄이 3번 줄에서 상품 6개와 맞대어져 1,104줄만 남는다. 분류 조건이 마지막에야 적용되니, 그 앞의 조인이 버릴 행 5만 줄을 만들고 해시 테이블에 넣고 찾는 데 CPU를 쓴다. 해시 조인이 셋 이상이면 조인 순서가 곧 중간 결과의 크기다. 소분류 23의 상품 6개(8블록)를 먼저 build로 올리고 주문상품을 대면 1,577줄만 남는다. 이 1,577줄을 build로 삼아 주문을 대면 배송 완료 주문의 줄 1,104줄이 나온다. 중간 결과는 51,463 → 1,577줄, 가장 큰 해시 영역은 2,734K → 1,355K, 시간은 0.09 → 0.03초다. Buffers는 491 그대로다. 하루 2만 번이면 CPU 시간이 약 1,800초 → 600초로 준다. 계획을 볼 때 Buffers만 보지 말고 조인 줄의 A-Rows와 Used-Mem을 함께 봐야 하는 이유다.

해설

Buffers가 같아도 일량은 다르다: 블록을 읽는 비용 말고도 해시 테이블을 만들고 찾는 CPU가 있다. 같은 테이블을 같은 방식으로 읽는 두 계획을 비교할 때는 조인 줄의 A-Rows가 기준이다(교재 06화 §4).
힌트를 떼는 것도 답이다: 통계가 맞으면 옵티마이저는 분류 조건을 먼저 적용하는 순서를 고를 가능성이 크다. 순서를 고정한 힌트는 그때의 판단일 뿐이다.
인덱스를 만든다면: order_items (product_id, order_id, quantity)가 있으면 상품 6개에서 NL로 1,577줄을 바로 찾을 수 있어 수십 블록이면 된다. FK가 꺼져 있고 조인 키에 인덱스가 없는 이 DB에서는 인덱스도 승인 절차를 거쳐야 한다(교재 18화).
COUNT(DISTINCT o.order_id): 한 주문에 같은 분류의 상품이 둘 들어갈 수 있어서 DISTINCT가 필요하다. 조인 순서를 바꿔도 결과는 같다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황