메인 화면의 인기 상품 칸이 가끔 비고, 제휴사 상품 피드는 가끔 ORA-08103으로 끊긴다

기초PLSQL이커머스537 / 700

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

상황

이커머스 메인 화면의 「인기 상품」 칸은 순위 테이블 product_rank를 읽는다. 순위는 잡이 10분마다 지우고 다시 채운다. 고객센터에는 "인기 상품 칸이 텅 비어 있다"는 캡처가 하루 몇 건씩 들어오는데, 새로 고치면 다시 나온다. 같은 테이블을 읽어 제휴 가격비교 사이트로 보내는 「상품 피드」는 한 번에 2분쯤 걸리고, 하루 스물네 번 가운데 서너 번은 ORA-08103: object no longer exists로 끊긴다. 피드 담당자는 테이블을 지운 적이 없다고 한다.

테이블과 인덱스

product_rank 인기 상품 순위(문제 설정): 약 280행 · 3블록 · 상품마다 한 행(product_id · view_cnt · avg_rating · rnk) · 인덱스 없음
product_views: 100,000행 · 367블록 / reviews: 15,000행 · 775블록 / products: 280행 · 8블록 · PK product_id
메인 화면은 product_rank에서 순위 상위 20개를 초당 약 40번 읽는다
상품 피드는 product_rank를 처음부터 차례로 읽으며(정렬 없음, 순위는 칸으로 보낸다) 행마다 products를 PK로 찾아 붙인다. 20행씩 가져와 제휴사 API로 보내고 응답을 받은 뒤 다음 20행을 가져온다

원본 SQL

sql

-- 순위 갱신 잡 (10분마다)
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE product_rank';
  INSERT INTO product_rank (product_id, view_cnt, avg_rating, rnk)
  SELECT v.product_id, v.cnt, r.avg_rating, RANK() OVER (ORDER BY v.cnt DESC)
    FROM (SELECT product_id, COUNT(*) AS cnt
            FROM product_views
           WHERE view_date >= SYSDATE - 7
           GROUP BY product_id) v
    LEFT JOIN (SELECT product_id, ROUND(AVG(rating), 1) AS avg_rating
                 FROM reviews
                GROUP BY product_id) r
      ON r.product_id = v.product_id;
  COMMIT;
END;

-- 상품 피드 (참고)
SELECT k.product_id, p.name, k.rnk, k.avg_rating
  FROM product_rank k JOIN products p ON p.product_id = k.product_id;

실행계획 학습용 예시

xplan

[10:20 전후 — 잡과 화면과 피드]
시각            순위 갱신 잡 (SID 301)                  메인 화면 (초당 40번)       상품 피드 (SID 288)
10:19:10                                                                            피드 시작 · 20행씩 가져와 보낸다
10:20:00.00     TRUNCATE (0.02초 · 기다림 없음)
10:20:00.02     INSERT … SELECT (0.61초)                0건 · 0건 · … (24번)
10:20:00.63     COMMIT                                  다시 20건
10:20:41                                                                            다음 20행 → ORA-08103: object no longer exists
                                                                                    (보낸 120행 · 못 보낸 160행)

[메인 화면 SQL — 10:20:00.30 의 한 번 (ALLSTATS LAST)]
---------------------------------------------------------------------------------------------
| Id  | Operation               | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |              |      1 |        |      0 |00:00:00.01 |       3 |
|*  1 |  COUNT STOPKEY          |              |      1 |        |      0 |00:00:00.01 |       3 |
|   2 |   VIEW                  |              |      1 |    280 |      0 |00:00:00.01 |       3 |
|*  3 |    SORT ORDER BY STOPKEY|              |      1 |    280 |      0 |00:00:00.01 |       3 |
|   4 |     TABLE ACCESS FULL   | PRODUCT_RANK |      1 |    280 |      0 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------
   1 - filter(ROWNUM<=20)
   3 - filter(ROWNUM<=20)

[V$LOCK — 10:20:00.30 · product_rank]
SID   TYPE  LMODE  REQUEST
301   TM      3       0       (INSERT 중 · TRUNCATE 하는 순간에는 6 을 잠깐 쥐었다)
(메인 화면 · 피드 세션은 이 테이블에 쥔 락이 없다)

[USER_OBJECTS — product_rank 의 DATA_OBJECT_ID]
  10:19 → 88214   ·   10:21 → 88231   (TRUNCATE 할 때마다 바뀐다)

문제

메인 화면이 0건을 받은 이유와 피드가 ORA-08103으로 끊긴 이유를 TRUNCATE가 어떤 문장인지와 이어서 설명하시오. 피드와 메인 화면이 읽는 중인데도 TRUNCATE가 기다리지 않은 이유도 쓰시오.
순위를 갱신하는 동안에도 화면과 피드가 이전 순위를 끝까지 읽도록 잡을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- 순위 갱신 잡 — 지우기와 채우기를 한 트랜잭션으로 · COMMIT 은 맨 끝 한 번
BEGIN
  DELETE FROM product_rank;
  INSERT INTO product_rank (product_id, view_cnt, avg_rating, rnk)
  SELECT v.product_id, v.cnt, r.avg_rating, RANK() OVER (ORDER BY v.cnt DESC)
    FROM (SELECT product_id, COUNT(*) AS cnt
            FROM product_views
           WHERE view_date >= SYSDATE - 7
           GROUP BY product_id) v
    LEFT JOIN (SELECT product_id, ROUND(AVG(rating), 1) AS avg_rating
                 FROM reviews
                GROUP BY product_id) r
      ON r.product_id = v.product_id;
  COMMIT;                -- 이 순간 새 순위로 한꺼번에 바뀐다
END;

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

xplan

[고친 뒤 — 같은 10:20 (학습용 예시)]
  잡           DELETE 280행 + INSERT 280행 · 0.64초 · undo 약 60KB · redo 약 120KB
  메인 화면    0건 0번 · COMMIT 전까지는 이전 순위 20건 (CR 블록 · Buffers 3 → 6 안팎)
  상품 피드    ORA-08103 0번 · 시작한 시점의 순위 280행을 끝까지 보낸다

모범 답안 서술

TRUNCATE는 DML이 아니라 DDL이다. 실행하는 순간 확정되고 되돌릴 수 없다. 그래서 10:20:00.00에 순위가 비는 순간 다른 세션에도 빈 테이블이 보이고, 0.61초 뒤 INSERT가 COMMIT될 때까지 메인 화면은 0건을 받는다. 초당 40번 읽으니 10분마다 24번쯤 빈 칸이 뜬다. SELECT는 테이블에 TM 락을 쥐지 않는다(V$LOCK에도 메인 화면과 피드의 줄이 없다). 그래서 TM 6이 필요한 TRUNCATE도 읽는 세션을 기다리지 않고 바로 진행한다. TRUNCATE는 행을 하나씩 지우는 대신 세그먼트를 새로 시작하므로 DATA_OBJECT_ID가 88214에서 88231로 바뀐다. 피드는 10:19:10에 시작해 옛 세그먼트를 20행씩 읽고 있었으므로, 다음 블록을 읽으러 갔을 때 그 세그먼트가 없어 ORA-08103을 받았다. 테이블은 지워지지 않았지만 피드가 읽던 세그먼트는 사라졌다. 고친 잡은 DELETE와 INSERT를 한 트랜잭션으로 묶고 맨 끝에 한 번만 COMMIT한다. DELETE는 DML이라 COMMIT 전까지 다른 세션은 이전 순위를 읽는다(읽기 일관성, CR 블록). 메인 화면은 빈 칸을 보지 않고, 피드는 시작한 시점의 순위를 끝까지 읽는다. 세그먼트도 그대로라 ORA-08103이 나지 않는다. 대가는 DELETE가 남기는 언두와 redo다. 280행이라 언두 약 60KB, redo 약 120KB로 작다. 테이블이 커서 DELETE가 부담이면, 같은 모양의 테이블 두 개를 번갈아 채우고 시노님을 바꾸는 방법을 쓴다.

해설

TRUNCATE를 잡 맨 앞에 두는 습관은 행이 많을 때 빠르기 때문이다. 하지만 읽는 사람이 있는 테이블이라면 빠른 만큼 위험하다. 빈 테이블이 확정된 채 드러나고, 읽던 세션은 세그먼트를 잃는다. 읽는 사람이 없는 작업 테이블에만 쓴다.
DELETE 대신 MERGE로 바뀐 순위만 고치면 언두와 redo가 더 준다. 다만 순위에서 빠진 상품을 지우는 문장이 따로 있어야 한다.
큰 테이블이라면 product_rank_a와 product_rank_b를 번갈아 채운 뒤 CREATE OR REPLACE SYNONYM으로 이름을 바꿔 건다. 시노님을 바꾸는 것도 DDL이라 그 이름을 쓰는 커서가 한 번씩 다시 파싱되지만, 이미 열린 커서는 옛 테이블을 끝까지 읽는다. 파티션 테이블이라면 칸 교환(EXCHANGE PARTITION)이 같은 일을 한다.
ORA-08103은 TRUNCATE뿐 아니라 읽는 도중에 DROP, MOVE처럼 세그먼트를 바꾸는 DDL을 해도 난다. 읽는 쪽이 락을 쥐지 않으니 DDL은 기다려 주지 않는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황