카테고리 목록 API가 몰리는 시간마다 row cache lock이 1위로 오르고, 가끔 주문이 반쯤 남는다

실전DDL이커머스562 / 700

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

상황

이커머스의 「카테고리 상품 목록」 API는 MySQL 시절의 프로시저를 옮겨 온 것이다. 요청마다 고른 카테고리의 하위 카테고리를 임시 테이블로 만들고, 상품과 조인한 뒤 그 테이블을 지운다. 세일 기간에는 초당 40번쯤 호출된다. 같은 기간 고객센터에는 "결제는 안 했는데 주문서가 남아 있다"는 문의가 하루 몇 건씩 들어온다. 주문 화면은 주문을 저장하는 도중에 프로모션 대상인지 확인하려고 같은 로직을 부른다.

테이블과 인덱스

categories: 53행 · 4블록 · parent_id로 이어진 계층 · PK(category_id)만 있다
products: 280행 · 8블록 · PK(product_id)만 있다 · 상품은 카테고리마다 5개 남짓
orders: 34,908행 · 193블록 · 주문 화면은 orders에 한 행을 넣은 뒤 결제가 끝나면 커밋하고, 실패하면 ROLLBACK한다.
임시 테이블 이름에는 API 서버가 매기는 요청 번호가 붙는다. CURSOR_SHARING = EXACT

원본 SQL

sql

-- 요청 하나 (요청 번호 48213 · 카테고리 12)
CREATE TABLE TMP_CAT_48213 AS
  SELECT category_id
    FROM categories
   START WITH category_id = 12
 CONNECT BY PRIOR category_id = parent_id;

SELECT p.product_id, p.name
  FROM products p, TMP_CAT_48213 t
 WHERE p.category_id = t.category_id
 ORDER BY p.name;

DROP TABLE TMP_CAT_48213 PURGE;

실행계획 학습용 예시

xplan

[AWR · 세일 첫날 20:00 ~ 21:00 · Load Profile (초당)]
  DB CPU                        1.9
  Executes (SQL)            1,540
  Parses (SQL)                402
  Hard parses (SQL)           131
  Redo size (bytes)     1,184,000

[Top Foreground Events]
  이벤트                              % DB time
  row cache lock                          24.1
  DB CPU                                  21.7
  library cache lock                      13.8
  enq: RO - fast object reuse              9.6
  latch: shared pool                       6.2

[Time Model]
  parse time elapsed                46.3% of DB time
  hard parse elapsed time           44.0% of DB time

[10046 → TKPROF · 요청 하나]
  CREATE TABLE TMP_CAT_48213 AS SELECT …                        Parse 1 · Execute 1 · Misses in library cache during parse: 1
  SELECT p.product_id, p.name FROM products p, TMP_CAT_48213 t …   Parse 1 · Execute 1 · Fetch 1 · Misses in library cache during parse: 1
  DROP TABLE TMP_CAT_48213 PURGE                                Parse 1 · Execute 1 · Misses in library cache during parse: 1
  OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS                   Execute 61 (딕셔너리 OBJ$, TAB$, SEG$, COL$ 등에 INSERT와 DELETE)
  요청 하나 경과 0.21초 (그중 상품을 읽고 내보내는 시간 0.002초)

[주문 화면 · 결제가 실패한 주문 하나의 순서]
  INSERT INTO orders …                   (아직 커밋 전)
  프로모션 확인 → CREATE TABLE TMP_CAT_…
  결제 실패 → ROLLBACK                   → orders 행이 남아 있다

문제

요청 하나에 0.2초가 걸리고 세일 시간에 row cache lock과 library cache lock이 위로 오르는 이유를 위 자료로 설명하시오. 주문이 반쯤 남는 일과는 어떻게 이어지는지도 쓰시오.
대안 셋(임시 테이블 이름을 고정한다, GTT를 미리 만들어 둔다, 임시 테이블 없이 한 문장으로 쓴다)을 비교하시오.
하나를 골라 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- 임시 테이블 없이 한 문장 · 카테고리는 바인드 변수 (요청이 몇 번 오든 문장은 하나 · DDL도 커밋도 없다)
SELECT p.product_id, p.name
  FROM products p
 WHERE p.category_id IN (SELECT c.category_id
                           FROM categories c
                          START WITH c.category_id = :cat_id
                        CONNECT BY PRIOR c.category_id = c.parent_id)
 ORDER BY p.name;

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

xplan

[세일 둘째 날 20:00 ~ 21:00 · 고친 뒤 (학습용 예시)]
  Hard parses (SQL)                               0.1 / 초
  row cache lock · library cache lock · enq: RO    Top 이벤트에서 사라짐
  Redo size                                       약 9,000 bytes / 초
  요청 하나                                       0.003초
  반쯤 남은 주문                                   0건

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |            |      1 |        |     31 |00:00:00.01 |      12 |
|   1 |  SORT ORDER BY                             |            |      1 |     28 |     31 |00:00:00.01 |      12 |
|*  2 |   HASH JOIN RIGHT SEMI                     |            |      1 |     28 |     31 |00:00:00.01 |      12 |
|   3 |    VIEW                                    | VW_NSO_1   |      1 |      2 |      6 |00:00:00.01 |       4 |
|*  4 |     CONNECT BY NO FILTERING WITH START-WITH|            |      1 |        |      6 |00:00:00.01 |       4 |
|   5 |      TABLE ACCESS FULL                     | CATEGORIES |      1 |     53 |     53 |00:00:00.01 |       4 |
|   6 |    TABLE ACCESS FULL                       | PRODUCTS   |      1 |    280 |    280 |00:00:00.01 |       8 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("P"."CATEGORY_ID"="CATEGORY_ID")
   4 - access("C"."PARENT_ID"=PRIOR "C"."CATEGORY_ID")
       filter("C"."CATEGORY_ID"=:CAT_ID)

모범 답안 서술

왜 느리고 무엇을 기다리나: 요청 하나가 문장 셋(CREATE, SELECT, DROP)을 보내는데, 테이블 이름에 요청 번호가 붙어서 셋 다 처음 보는 텍스트다. 그래서 요청마다 하드 파싱이 세 번이고(초당 131번, Time Model의 hard parse 44%), 그중 둘은 DDL이다. 테이블을 만들고 지울 때마다 딕셔너리(OBJ$, TAB$, SEG$ 등)에 행을 넣고 지우며, 그동안 딕셔너리 캐시 항목을 잠그고(row cache lock) 라이브러리 캐시에 객체를 올리고 내리면서(library cache lock) 다른 요청과 다툰다. DROP은 그 테이블의 블록을 디스크로 내보내는 일까지 기다린다(enq: RO - fast object reuse). 요청 하나 0.21초 가운데 상품을 실제로 읽는 시간은 0.002초뿐이다. 주문이 반쯤 남는 까닭: DDL은 실행하기 전에 그 세션의 트랜잭션을 커밋한다(암시적 커밋). 주문 화면이 주문을 INSERT한 뒤 이 로직을 부르면 CREATE TABLE이 그 INSERT를 확정해 버린다. 그 뒤 결제가 실패해 ROLLBACK해도 주문서는 이미 커밋돼 있다. 성능 문제가 정합성 문제까지 만든 것이다. 대안 비교: 이름을 고정하면(TMP_CAT) 문장 텍스트는 같아지지만, 동시에 들어온 두 요청이 같은 이름의 테이블을 만들다 ORA-00955로 부딪치고, 요청마다 DDL과 암시적 커밋을 하는 것도 그대로다. GTT를 배포할 때 한 번만 만들어 두면 요청마다 DDL이 없고, 행은 넣은 세션에만 보이므로 요청끼리 섞이지 않는다. 다만 INSERT와 SELECT 두 문장을 보내야 하고 임시 테이블의 건수 추정을 챙겨야 한다. 임시 테이블 없이 계층 조회를 서브쿼리로 넣으면 문장이 하나이고 DDL도 커밋도 없다. 이것을 고른다. 고친 문장은 카테고리를 바인드 변수 :cat_id로 받아 요청이 몇 번 오든 커서 하나를 쓴다. 53행짜리 카테고리 트리를 펼쳐 상품과 세미 조인하는 데 12블록이다. 하드 파싱은 초당 131 → 0.1, 요청 하나는 0.21 → 0.003초가 되고 주문 트랜잭션도 깨지지 않는다.

해설

MySQL의 임시 테이블 습관을 그대로 옮기면 생기는 일이다. MySQL의 CREATE TEMPORARY TABLE은 세션 전용이고 커밋도 일으키지 않지만, 오라클의 CREATE TABLE은 딕셔너리를 고치는 DDL이고 앞의 트랜잭션을 커밋한다(교재 12화 §9: DDL은 실패해도 커밋한다).
중간 결과가 꼭 필요하면 GTT를 배포할 때 한 번만 만든다. ON COMMIT DELETE ROWS면 트랜잭션이 끝날 때 행이 저절로 비워진다. 요청마다 만들고 지우는 것은 테이블이 아니라 행이어야 한다.
계층 조회 결과를 한 문장 안에서 여러 번 써야 하면 WITH 절로 한 번 정의해 둔다. 이것도 문장 하나 안의 일이라 DDL이 없다.
요청마다 이름이 다른 테이블은 TKPROF와 V$SQL에서 문장을 묶어 보기도 어렵게 한다. 같은 일을 하는 SQL은 같은 텍스트여야 공유도 되고 추적도 된다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황