앱 출시를 앞두고 보니 조회 기록 한 줄을 넣는 INSERT가 블록을 열여섯 개씩 고친다

기초DDL이커머스470 / 700

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

상황

고객이 상품 상세 화면을 열 때마다 product_views에 한 줄이 들어간다. 지금은 하루 약 1,100번인데, 다음 달 모바일 앱이 나오면 하루 1만 번이 넘을 것으로 본다. 지난달 마케팅 분석팀이 조회 분석 화면을 만들면서 인덱스 다섯 개를 한꺼번에 요청했고, 그대로 만들어졌다. 출시 점검을 하던 운영 DBA: "한 줄 넣는 INSERT가 실행마다 블록을 열여섯 개씩 고쳐요. 지난달까지는 네 개였어요." 박팀장이 인덱스를 어떻게 할지 정리해 달라고 했다.

테이블과 인덱스

product_views: 100,000행 · 367블록 · view_date 2024-09-01 ~ 2024-11-29 · 하루 약 1,100행이 시간 순서대로 들어온다. 지우거나 고치는 일은 없다.
인덱스 여섯 개: PK_PRODUCT_VIEWS (view_id) · 분석팀이 요청한 IX_PV_PROD (product_id) · IX_PV_CUST (customer_id) · IX_PV_DATE (view_date) · IX_PV_PROD_DATE (product_id, view_date) · IX_PV_CUST_DATE (customer_id, view_date)
분석팀 SQL은 셋이다. Q1 상품별 최근 30일 조회 수(product_id = :p AND view_date >= TRUNC(SYSDATE) - 30, 하루 약 1,700번), Q2 고객이 최근 본 상품 20개(customer_id = :c ORDER BY view_date DESC FETCH FIRST 20 ROWS ONLY, 하루 약 600번), B1 밤 01:00에 한 번 도는 어제 조회 집계(view_date가 어제인 행을 product_id별로 센다).
통계는 최신이다.

원본 SQL

sql

-- 지난달 분석팀 요청 (그대로 반영)
CREATE INDEX IX_PV_PROD      ON product_views (product_id);
CREATE INDEX IX_PV_CUST      ON product_views (customer_id);
CREATE INDEX IX_PV_DATE      ON product_views (view_date);
CREATE INDEX IX_PV_PROD_DATE ON product_views (product_id, view_date);
CREATE INDEX IX_PV_CUST_DATE ON product_views (customer_id, view_date);

-- 상품 상세 화면 (화면을 열 때마다 한 번)
INSERT INTO product_views (view_id, product_id, customer_id, view_date)
VALUES (:view_id, :product_id, :customer_id, SYSDATE);

실행계획 학습용 예시 · 인덱스 하나당 비용은 교재 14화 실측과 같은 크기

xplan

[시험 DB · 같은 INSERT 1,000번 (한 번에 한 행, 화면과 같은 모양) · 세션 통계]
  인덱스                       redo size     db block changes    db block gets
  PK만 (지난달 전)              0.35 MB          4,120               3,840
  지금 6개                      1.58 MB         16,310              16,420

[AWR · 최근 7일 · 이 INSERT]
  executions 7,742 · 실행당 db block gets 3.8 (지난달) → 16.4 (지금) · 실행당 redo 약 350 → 1,630 bytes

[DBA_INDEX_USAGE · 최근 30일]
  INDEX_NAME          TOTAL_ACCESS_COUNT   LAST_USED
  PK_PRODUCT_VIEWS                     0   —
  IX_PV_PROD                           0   —
  IX_PV_CUST                           0   —
  IX_PV_DATE                          30   오늘 01:00 (B1)
  IX_PV_PROD_DATE                 51,880   오늘 10:41 (Q1)
  IX_PV_CUST_DATE                 18,204   오늘 10:40 (Q2)

[B1 · 어제 조회 집계 · 두 가지 길]
  IX_PV_DATE 경유      INDEX RANGE SCAN · A-Rows 1,104 · Buffers 14 · 0.01 초
  TABLE ACCESS FULL    A-Rows 1,104 · Buffers 367 · 0.01 초

문제

한 줄 넣는 INSERT가 블록을 네 개에서 열여섯 개로 고치게 된 이유를 인덱스 개수로 설명하시오.
분석팀의 Q1, Q2, B1을 그대로 받으면서 INSERT 비용을 줄이도록 인덱스를 정리하시오. 지울 인덱스마다 근거를 쓰고, 지우기 전에 확인할 방법도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 앞 컬럼이 같은 결합 인덱스가 같은 조건을 받는다 → 단일 컬럼 인덱스 둘은 지운다
--   (승인 전이라면 ALTER INDEX … INVISIBLE 로 한 주 지켜본 뒤)
DROP INDEX IX_PV_PROD;
DROP INDEX IX_PV_CUST;

-- 밤 01:00 집계 한 번을 위한 인덱스 → 367블록 전체 스캔 한 번이면 된다
DROP INDEX IX_PV_DATE;

-- 남는 것: PK_PRODUCT_VIEWS · IX_PV_PROD_DATE (Q1) · IX_PV_CUST_DATE (Q2)

개선 후 실행계획 학습용 예시 · 인덱스 하나당 비용은 교재 14화 실측과 같은 크기

xplan

[시험 DB · 같은 INSERT 1,000번 · 인덱스 셋]
  redo size          1.58 MB → 0.84 MB
  db block changes   16,310  → 약 9,000
  db block gets      16,420  → 약 8,700

[분석팀 SQL]
  Q1  INDEX RANGE SCAN IX_PV_PROD_DATE  (지우기 전과 같은 계획)
  Q2  INDEX RANGE SCAN DESCENDING IX_PV_CUST_DATE  (같은 계획)
  B1  TABLE ACCESS FULL product_views · Buffers 14 → 367 · 0.01 초 (하루 한 번)

모범 답안 서술

인덱스 하나는 정렬된 대장 한 권이다. INSERT는 테이블 블록에 행을 넣는 것으로 끝나지 않고, 인덱스마다 정렬된 제자리(리프 블록)를 찾아 키를 끼우고 그 변경의 undo와 redo를 남긴다. 인덱스가 PK 하나일 때 1,000행에 블록 변경 4,120, redo 0.35MB였는데, 다섯 개가 늘자 16,310과 1.58MB가 됐다. 인덱스 하나마다 행당 블록 변경 약 2.4개, redo 약 250바이트가 붙는다(교재 14화의 인덱스 하나당 비용과 같은 크기다). 실행당 블록이 네 개에서 열여섯 개가 된 이유가 이것이다. 분석팀 SQL에 필요한 인덱스는 둘뿐이다. Q1은 IX_PV_PROD_DATE, Q2는 IX_PV_CUST_DATE를 쓴다. IX_PV_PROD (product_id)와 IX_PV_CUST (customer_id)는 앞 컬럼이 같은 결합 인덱스가 같은 조건을 그대로 받아 주는 중복 인덱스라서 사용 이력도 30일 동안 0이다. 지운다. IX_PV_DATE는 밤 01:00 B1이 하루 한 번 쓴다. 인덱스로 읽으면 14블록, 전체 스캔으로 읽어도 367블록에 0.01초다. 하루 한 번 아끼는 353블록을 위해 하루 1만 번의 INSERT가 인덱스 하나를 더 고칠 이유가 없으므로 이것도 지운다. 정리하면 인덱스 여섯 개가 셋으로 줄고, 1,000행 기준 redo는 1.58 → 0.84MB, 블록 변경은 16,310 → 약 9,000으로 준다. 앱 출시 뒤 하루 1만 번이면 하루 redo가 약 7MB 덜 나온다. 지우기 전에는 DBA_INDEX_USAGE의 사용 이력과 AWR 실행계획에서 인덱스 이름을 찾아보고, 먼저 INVISIBLE로 바꿔 한 주 동안 Q1, Q2, B1의 계획이 그대로인지 지켜본 뒤 지운다.

해설

함정: 인덱스는 조회에서만 비용을 따지기 쉽다. 하지만 이 테이블은 고치거나 지우는 일 없이 넣기만 하는 로그다. 넣는 횟수가 열 배로 늘면 인덱스 하나의 몫도 열 배가 된다(교재 14화 §2.1, 인덱스 0개 → 3개에서 블록 변경 157배).
DBA_INDEX_USAGE는 기본이 표본 수집이라 0이 곧 '안 쓴다'는 뜻은 아니다. 이 문제에서는 Q1과 Q2가 결합 인덱스를 쓰는 것이 계획으로도 확인되므로 근거가 둘이다.
다른 답: B1을 위해 IX_PV_DATE를 남겨도 틀리지는 않다. 다만 시간 순서로 쌓이는 로그는 날짜 범위가 고수위선 근처 블록에 몰려 있어 전체 스캔도 싸다. 테이블이 수십만 블록으로 커지면 그때 일 단위 파티션을 검토한다.
인덱스를 지우는 일은 DDL이라 그 테이블을 쓰는 커서가 무효가 된다. 사용이 적은 시간에 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 인덱스 하나당 비용은 교재 14화 실측과 같은 크기 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 인덱스 하나당 비용은 교재 14화 실측과 같은 크기 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황