상품 상세 화면이 열릴 때마다 조회 로그 1년 치를 훑는다

중급DDL이커머스93 / 700

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

상황

상품 상세 화면 오른쪽 위에는 「최근 1년 조회 n회」가 뜬다. 상세 화면은 하루 약 4만 번 열린다. 이 숫자를 세는 SQL은 밤마다 도는 「상품별 일 조회수」 집계 배치를 위해 만든 (view_date, product_id) 인덱스를 타는데, 한 번에 58블록을 읽는다. product_views는 상세 화면이 열릴 때마다 한 행씩 INSERT되는 로그 테이블이다.

테이블과 인덱스

product_views 상품 조회 로그: 100,000행 · 367블록 · view_date DATE(시분초 있음) · 이 문제에서는 2020 ~ 2024년에 고르게 분포한다고 본다(하루 약 55건, 최근 1년 약 20,000행)
상품 280개 · 상품당 평균 357행(최근 1년 평균 71행) · 아래 예시 상품은 최근 1년 74행
인덱스: PK (view_id) · IX_PV_DT_PRD (view_date, product_id): 집계 배치용 · BLEVEL 1 · 리프 약 280블록(리프 한 블록에 약 360항목) · 통계 최신
집계 배치(매일 02:00): view_date >= TRUNC(SYSDATE) - 1 AND view_date < TRUNC(SYSDATE)로 어제 하루를 상품별로 센다 · 하루 한 번

원본 SQL

sql

SELECT COUNT(*) AS view_cnt
  FROM product_views
 WHERE product_id = :p
   AND view_date >= ADD_MONTHS(TRUNC(SYSDATE), -12)
   AND view_date <  TRUNC(SYSDATE) + 1

실행계획 학습용 예시 · 행 수와 블록 수는 schemas 그대로 · 날짜 분포는 문제에 적은 가정

xplan

---------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |      1 |00:00:00.01 |      58 |
|   1 |  SORT AGGREGATE    |              |      1 |      1 |      1 |00:00:00.01 |      58 |
|*  2 |   INDEX RANGE SCAN | IX_PV_DT_PRD |      1 |     71 |     74 |00:00:00.01 |      58 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("VIEW_DATE">=ADD_MONTHS(TRUNC(SYSDATE@!),-12) AND "PRODUCT_ID"=:P AND
              "VIEW_DATE"<TRUNC(SYSDATE@!)+1)
       filter("PRODUCT_ID"=:P)

문제

상품 하나의 74건을 세는 데 58블록을 읽는 이유를 2번 줄로 설명하시오. 하루 4만 번이면 얼마인지도 계산하시오.
인덱스를 다시 설계해 DDL을 작성하시오. 새 인덱스를 더할지 기존 인덱스를 바꿀지 정하고, 집계 배치가 어떻게 되는지와 로그 테이블이라는 점을 근거로 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 상품이 = 로, 기간이 범위로 들어온다 — '이 상품의 최근 1년' 이 한 구간
CREATE INDEX IX_PV_PRD_DT ON product_views (product_id, view_date) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 상세 화면 SQL 과 집계 배치를 세션에서 잰다

ALTER INDEX IX_PV_PRD_DT VISIBLE;     -- T2 승인 뒤
ALTER INDEX IX_PV_DT_PRD INVISIBLE;   -- 배치가 며칠 FULL 로 문제없이 도는지 본 뒤
DROP INDEX IX_PV_DT_PRD;              -- 하나를 더하지 않고 바꾼다 (INSERT 가 고치는 인덱스 수는 그대로)

개선 후 실행계획 학습용 예시 · 행 수와 블록 수는 schemas 그대로 · 날짜 분포는 문제에 적은 가정

xplan

[상세 화면 — IX_PV_PRD_DT]
---------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE    |              |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN | IX_PV_PRD_DT |      1 |     71 |     74 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------
   2 - access("PRODUCT_ID"=:P AND "VIEW_DATE">=ADD_MONTHS(TRUNC(SYSDATE@!),-12) AND
              "VIEW_DATE"<TRUNC(SYSDATE@!)+1)

[집계 배치 — 하루 한 번] IX_PV_PRD_DT INDEX FAST FULL SCAN 약 283 (또는 TABLE ACCESS FULL 367)

모범 답안 서술

2번 줄의 access에 "PRODUCT_ID"=:P가 있지만 filter에도 또 나온다. 인덱스는 view_date로 먼저 정렬되어 있고, view_date가 '최근 1년'이라는 범위로 들어왔다. 범위 조건 뒤의 product_id는 구간을 줄이지 못하고 filter로 거를 뿐이다. view_date에는 시분초가 있어서 시작점을 좁히는 효과도 없다. 그래서 전 상품의 1년 치 약 2만 항목(리프 약 56블록)을 읽고 이 상품의 74건만 남긴다(Buffers 58). 상세 화면이 하루 4만 번 열리므로 하루 약 232만 블록이다. 상품은 =, 기간은 범위로 들어오므로 (product_id, view_date)로 만들면 '이 상품의 최근 1년'이 한 구간이 되어 리프 한 블록만 읽는다. 58 → 2블록, 하루 약 232만 → 8만 블록이다. 새 인덱스를 더하지 않고 기존 인덱스를 바꾼다. 기존 인덱스는 하루 한 번 도는 집계 배치만 쓴다. 그 배치는 테이블 전체가 367블록이라 FULL로 읽거나 새 인덱스를 FAST FULL SCAN(약 283블록)으로 읽어도 하루 한 번이면 충분하다. 반대로 product_views는 상세 화면이 열릴 때마다 한 행씩 INSERT되는 로그 테이블이라, 인덱스가 하나 늘면 하루 4만 번의 INSERT가 고칠 인덱스도 하나씩 는다. 드물게 도는 배치를 위해 정한 순서가 하루 4만 번 도는 화면을 망치게 두지 않는다. 절차는 이렇다. 새 인덱스를 INVISIBLE로 만들어 세션에서 상세 화면과 배치를 재고, VISIBLE로 바꾼다. 그다음 기존 인덱스를 INVISIBLE로 돌려 배치가 며칠 동안 문제없이 도는지 보고, DBA_INDEX_USAGE로 다른 SQL이 쓰지 않았는지 확인한 뒤 지운다.

해설

ACC-004와 같은 갈림길(바꿀까, 더할까)이다. 그쪽은 기존 인덱스를 월말에 전 부서가 여는 SQL이 써서 SKIP SCAN으로 견딜지 재 봐야 했다. 이쪽은 기존 인덱스를 쓰는 SQL이 하루 한 번 도는 배치뿐이고, 테이블이 작아서 FULL로 충분하다.
로그 테이블의 날짜 선두 인덱스는 새 값이 늘 맨 오른쪽 리프에 들어가므로, INSERT가 몰리면 그 블록에서 경합이 생기기도 한다. product_id 선두로 바꾸면 INSERT가 상품마다 흩어진다(LOCK 단원의 오른쪽 블록 경합).
숫자가 하루 늦어도 된다면, 밤 배치가 상품별 1년 조회수를 미리 집계해 두고 화면은 상품 한 행만 읽는 방법도 있다. 다만 '최근 1년'이 하루 늦게 반영된다는 점을 화면 주인과 합의해야 한다.
view_date에는 시분초가 있어서 날짜를 IN 목록으로 바꾸는 방법은 쓸 수 없다. 자정 값으로 IN 목록을 만들면 자정에 딱 맞는 조회만 세어 결과가 0에 가깝다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 행 수와 블록 수는 schemas 그대로 · 날짜 분포는 문제에 적은 가정 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 행 수와 블록 수는 schemas 그대로 · 날짜 분포는 문제에 적은 가정 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황