PART-036

블랙프라이데이 오픈 20분 동안 상품 조회 기록 저장이 0.002초에서 0.09초로 늘어난다

중급DDL이커머스411 / 700

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

상황

이커머스는 상품 상세 화면을 열 때마다 product_views_p에 한 행을 넣는다. 2025년 11월 28일 블랙프라이데이 오픈(10:00 ~ 10:20)에는 동시에 약 150세션이 조회 기록을 넣었고, 그동안 기록 저장이 평균 0.002초에서 0.09초로 늘어 상세 화면이 눈에 띄게 늦게 떴다. 지난해 같은 증상 때는 PK를 역방향 키 인덱스로 바꿔 인덱스 경합을 풀었다. DBA가 올해 AWR을 뽑아 왔다.

테이블과 인덱스

product_views_p 상품 조회 기록(파티션 복사본, 문제 설정): product_views 100,000행을 PARTITION BY RANGE (view_date) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))로 나눠 CTAS로 지었다(STORAGE (INITIAL 64K)). 복사본에는 2020년 1월 ~ 2024년 12월의 60칸이 있고, 칸 하나는 약 1,670행, FULL로 약 7블록이다.
운영에서는 같은 테이블에 달마다 칸이 이어서 생기고 있다. 지금 행이 쌓이는 칸은 2025년 11월 칸 SYS_P3307이다.
view_id는 시퀀스 번호(CACHE 1000)다. customer_id는 비회원 조회면 NULL이다(약 30%). 블랙프라이데이에는 조회가 특가 상품 몇 개에 몰린다.
인덱스는 GLOBAL 역방향 키 인덱스 pk_product_views_p (view_id) 하나다.
조회 기록을 읽는 SQL은 야간 배치의 「월 상품 조회 순위」뿐이다.

원본 SQL

sql

-- 상세 화면을 열 때마다
INSERT INTO product_views_p (view_id, product_id, customer_id, view_date)
VALUES (seq_view_id.NEXTVAL, :product_id, :customer_id, SYSDATE);

-- 「월 상품 조회 순위」 (야간 배치 · :v_mon = 그달 첫날)
SELECT product_id, COUNT(*) AS view_cnt
  FROM product_views_p
 WHERE view_date >= :v_mon
   AND view_date <  ADD_MONTHS(:v_mon, 1)
 GROUP BY product_id
 ORDER BY view_cnt DESC;

실행계획 학습용 예시

xplan

[AWR · 2025-11-28 10:00 ~ 10:20 · 조회 기록 INSERT 186,400건 · 동시 약 150세션]
  EVENT                            WAITS     TIME(s)   AVG(ms)   %DB time
  buffer busy waits               98,310       688         7       38.6
  enq: HW - contention            21,740       413        19       23.2
  DB CPU                                       402                 22.6
  log file sync                  186,900       176         1        9.9
  enq: TX - index contention         212         2         9        0.1

  Segments by Buffer Busy Waits
  OBJECT                SUBOBJECT    TYPE               BUSY WAITS   %Total
  PRODUCT_VIEWS_P       SYS_P3307    TABLE PARTITION        96,120     97.8
  PK_PRODUCT_VIEWS_P                 INDEX                   1,410      1.4

  V$WAITSTAT (같은 구간 · 블록 종류)
  data block 63% · 1st level bmb 24% · segment header 11% · 기타 2%

[「월 상품 조회 순위」 · 2024년 11월 (복사본)]
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |    280 | 00:00:00.01 |       7 |        |       |
|   1 |  SORT ORDER BY               |                 |      1 |    280 |    280 | 00:00:00.01 |       7 |        |       |
|   2 |   HASH GROUP BY              |                 |      1 |    280 |    280 | 00:00:00.01 |       7 |        |       |
|*  3 |    FILTER                    |                 |      1 |        |   1668 | 00:00:00.01 |       7 |        |       |
|   4 |     PARTITION RANGE ITERATOR |                 |      1 |   1667 |   1668 | 00:00:00.01 |       7 |    KEY |   KEY |
|*  5 |      TABLE ACCESS FULL       | PRODUCT_VIEWS_P |      1 |   1667 |   1668 | 00:00:00.01 |       7 |    KEY |   KEY |
----------------------------------------------------------------------------------------------------------------------------

   3 - filter(:V_MON<ADD_MONTHS(:V_MON,1))
   5 - filter(("VIEW_DATE">=:V_MON AND "VIEW_DATE"<ADD_MONTHS(:V_MON,1)))

문제

AWR을 근거로 무엇이 무엇을 기다리는지 설명하고, 월별 칸으로 나눠 둔 것이 왜 이 경합을 나누지 못하는지 쓰시오.
월 단위 관리를 지키면서 경합을 나누도록 테이블을 고치는 DDL을 쓰고, 해시 키를 무엇으로 할지 이유와 함께 쓰시오.
고친 뒤의 대가를 「월 상품 조회 순위」와 세그먼트 수로 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 월 INTERVAL 은 그대로 두고 칸 안을 view_id 해시 8조각으로 나눈다 · 운영 중에 바꾼다 (DML 을 막지 않는다)
ALTER TABLE product_views_p
  MODIFY PARTITION BY RANGE (view_date) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
  SUBPARTITION BY HASH (view_id) SUBPARTITIONS 8
  ( PARTITION p_2020_01 VALUES LESS THAN (DATE '2020-02-01') )
  ONLINE;

-- 확인 : 조각 수와 첫 익스텐트 (테이블 기본값 INITIAL 64K 를 물려받았는가)
SELECT partition_name, COUNT(*) AS subparts, MAX(initial_extent) AS init_ext
  FROM user_tab_subpartitions
 WHERE table_name = 'PRODUCT_VIEWS_P'
 GROUP BY partition_name;

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

xplan

[부하 시험 · 같은 패턴 재현 · 동시 150세션 · 20분 · INSERT 약 18만 건]
  buffer busy waits        98,310 → 9,870
  enq: HW - contention     21,740 → 1,920
  저장 평균                 0.09 → 0.004초
  Segments by Buffer Busy Waits : 이번 달 칸의 조각 여덟 개가 각 11 ~ 13%

[「월 상품 조회 순위」 · 2024년 11월]
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |    280 | 00:00:00.01 |      14 |        |       |
|   1 |  SORT ORDER BY               |                 |      1 |    280 |    280 | 00:00:00.01 |      14 |        |       |
|   2 |   HASH GROUP BY              |                 |      1 |    280 |    280 | 00:00:00.01 |      14 |        |       |
|*  3 |    FILTER                    |                 |      1 |        |   1668 | 00:00:00.01 |      14 |        |       |
|   4 |     PARTITION RANGE ITERATOR |                 |      1 |   1667 |   1668 | 00:00:00.01 |      14 |    KEY |   KEY |
|   5 |      PARTITION HASH ALL      |                 |      1 |   1667 |   1668 | 00:00:00.01 |      14 |      1 |     8 |
|*  6 |       TABLE ACCESS FULL      | PRODUCT_VIEWS_P |      8 |   1667 |   1668 | 00:00:00.01 |      14 |    KEY |   KEY |
----------------------------------------------------------------------------------------------------------------------------

[세그먼트]  칸마다 1개 → 8개 (복사본 60칸 기준 60개 → 480개 · INITIAL 64K 라 약 30 MB · 8MB 기본값이면 약 3.75 GB)

모범 답안 서술

기다림의 1위는 buffer busy waits(38.6%)이고 2위는 enq: HW - contention(23.2%)이다. 블록 경합의 97.8%가 테이블 칸 하나 SYS_P3307에서 나는데, 이 칸은 이번 달(2025년 11월) 칸이다. 역방향 키로 바꾼 PK는 1.4%뿐이라 인덱스 경합은 이미 풀렸다. 남은 것은 테이블 세그먼트 하나에 150세션이 동시에 행을 넣는 경합이다. 같은 데이터 블록에 넣으려는 세션끼리 버퍼를 기다리고(data block 63%), 빈 블록을 찾으려고 같은 비트맵 블록과 세그먼트 헤더를 들여다보며(1st level bmb 24%, segment header 11%), 세그먼트가 고수위선을 올려 새 블록을 받는 동안에는 HW 락을 기다린다. 월별 칸은 이 경합을 나누지 못한다. 범위 칸은 시간으로 나뉘므로 지금 들어오는 행은 모두 이번 달 칸 하나로 간다. 칸이 몇 개이든 넣는 곳은 언제나 세그먼트 하나다. 월 단위 관리는 범위 칸에 그대로 두고, 그 안을 해시 서브파티션으로 나눈다. MODIFY PARTITION BY RANGE (view_date) INTERVAL (…) SUBPARTITION BY HASH (view_id) SUBPARTITIONS 8 … ONLINE으로 운영 중에 바꾼다. 서브파티션마다 세그먼트가 따로이므로 고수위선과 비트맵 블록, 세그먼트 헤더도 여덟 벌로 나뉘고, 이번 달에 들어오는 행이 여덟 세그먼트로 흩어진다. 조각 수는 2의 거듭제곱(8)으로 한다. 해시 키는 같은 순간에 들어오는 행끼리 값이 달라야 한다. view_id는 시퀀스 번호라 연달아 들어오는 행이 여덟 조각에 고르게 흩어진다. product_id는 블랙프라이데이에 조회가 특가 상품 몇 개에 쏠리므로 그 상품이 든 조각에 다시 몰리고, customer_id는 비회원 조회(약 30%)가 모두 NULL이라 한 조각에 모인다. 대가도 있다. 「월 상품 조회 순위」는 그달 칸에서 여덟 조각을 모두 연다(PARTITION HASH ALL, 1 ~ 8, Starts 8). 조각마다 세그먼트 머리를 읽어 7 → 약 14블록이 된다. 세그먼트는 칸마다 1개에서 8개로 늘어나므로(복사본 기준 60개 → 480개), 조각들이 테이블 기본값 INITIAL 64K를 물려받았는지 확인한다(8MB 기본값이면 약 3.75GB). 부하 시험에서 buffer busy waits는 98,310 → 9,870번, 저장 평균은 0.09 → 0.004초가 되었다.

해설

범위는 관리, 해시는 분산: 동시 입력이 많은 거래 테이블은 범위 하나나 해시 하나보다 범위와 해시를 조합한 복합 파티션으로 나눈다. 범위 칸으로 월 정리를 하고, 해시 조각으로 넣는 곳을 흩는다.
지난해의 역방향 키는 인덱스 맨 오른쪽 블록 경합을 풀었다(LOCK-015와 같은 처방). 테이블 세그먼트 하나에 몰리는 경합은 인덱스를 바꿔서는 풀리지 않는다.
조각 수는 2의 거듭제곱으로 한다(PART-031). 경합이 남으면 16으로 늘린다. 다만 조각이 많을수록 월 조회가 여는 세그먼트도 늘어난다.
MODIFY PARTITION BY … ONLINE이 없는 버전이라면 DBMS_REDEFINITION으로 새 모양의 테이블에 옮겨 바꿔 끼운다. 어느 쪽이든 바꾸는 동안 공간이 두 벌 든다.
해시 서브파티션은 같은 키로 나눈 테이블과의 파티션 와이즈 조인에도 쓰인다. 이 문제처럼 근거(경합)가 있을 때만 둔다(PART-032와 비교).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황