포인트 적립 API는 INSERT 한 줄인데 이벤트 날 cursor mutex 대기가 쌓인다

중급INSERT이커머스575 / 700

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

상황

이커머스 포인트 적립 API(Python, python-oracledb)는 point_transactions에 한 건씩 넣는다. 평소에는 분당 300건쯤이고, 지난 토요일 쿠폰 이벤트 때는 분당 9,000건이었다. 그 두 시간 동안 DB 대기 상위에 cursor: mutex X와 library cache: mutex X가 올랐고 API 응답이 1초를 넘겼다. 개발팀이 말했다. "바인드 변수도 쓰고 SQL은 한 줄뿐이에요. 하드 파싱이 생길 리가 없어요."

테이블과 인덱스

point_transactions: 45,000행 · 210블록 · PK(tx_id)만 · customer_id NUMBER · amount NUMBER · tx_type VARCHAR2(10)
이 문제의 변형: order_id NUMBER(NULL 허용, 쿠폰 적립은 NULL)와 memo VARCHAR2(200)(비어 있거나 10 ~ 150자) 컬럼이 더 있다
시퀀스 seq_point_tx CACHE 1000 · DB 문자셋 AL32UTF8
호출 경로 둘: 주문 확정(고객 번호를 정수로, 주문 번호를 넘긴다)과 쿠폰 이벤트(요청 JSON의 고객 번호를 문자열 그대로, 메시지를 넘긴다)

원본 SQL

sql

# point_api.py
SQL = """INSERT INTO point_transactions (tx_id, customer_id, amount, tx_type, order_id, memo, created_at)
         VALUES (seq_point_tx.NEXTVAL, :cust, :amt, :tp, :ord, :memo, SYSDATE)"""

def earn(cur, cust, amt, ord_id=None, memo=None):
    cur.execute(SQL, cust=cust, amt=amt, tp="EARN", ord=ord_id, memo=memo)

# 주문 확정 :   earn(cur, 1234, 500, ord_id=88123)
# 쿠폰 이벤트 : earn(cur, body["customer_id"], 1000, memo=body["msg"])   # "1234" · 메시지 길이 제각각

실행계획 학습용 예시

xplan

[V$SQLAREA — 토요일 15:00]  VERSION_COUNT 14 · LOADS 23 · EXECUTIONS 412,880 · SHARABLE_MEM 1.9MB
[V$SQL_SHARED_CURSOR — Y 칸 세기 (자식 14개)]  BIND_MISMATCH 9 · BIND_LENGTH_UPGRADEABLE 4

[V$SQL_BIND_CAPTURE — 자식 셋만]
 CHILD  :CUST           :ORD            :MEMO             호출 경로
     0  NUMBER          NUMBER          VARCHAR2(32)      주문 확정 (memo 없음)
     3  VARCHAR2(32)    VARCHAR2(32)    VARCHAR2(128)     쿠폰 · 짧은 메시지 (ord 없음)
     7  VARCHAR2(32)    VARCHAR2(32)    VARCHAR2(2000)    쿠폰 · 긴 메시지

[AWR — 토요일 14:00 ~ 16:00 · Top Timed Events]
  cursor: mutex X              21%
  DB CPU                       38%
  library cache: mutex X        9%
  parse count (hard)           이 문장 23번 · 다른 문장 합계 40번

[한 번 실행의 계획]
---------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                    |      1 |        |      0 |00:00:00.01 |       7 |
|   1 |  LOAD TABLE CONVENTIONAL | POINT_TRANSACTIONS |      1 |        |      0 |00:00:00.01 |       7 |
|   2 |   SEQUENCE               | SEQ_POINT_TX       |      1 |        |      1 |00:00:00.01 |       0 |
---------------------------------------------------------------------------------------------------------

문제

텍스트가 하나인 INSERT에 자식이 14개 생긴 이유를 위 자료로 설명하시오. 하드 파싱이 23번뿐인데도 이벤트 때 mutex 대기가 쌓인 이유도 쓰시오.
자식이 하나로 모이도록 API 코드를 고치시오.

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

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

모범답안

모범 답안 SQL

sql

# point_api.py : 바인드 타입과 크기를 고정한다 (호출 경로와 값에 상관없이 같은 그릇)
import oracledb

SQL = """INSERT INTO point_transactions (tx_id, customer_id, amount, tx_type, order_id, memo, created_at)
         VALUES (seq_point_tx.NEXTVAL, :cust, :amt, :tp, :ord, :memo, SYSDATE)"""

def earn(cur, cust, amt, ord_id=None, memo=None):
    cur.setinputsizes(cust=oracledb.DB_TYPE_NUMBER, amt=oracledb.DB_TYPE_NUMBER,
                      tp=10, ord=oracledb.DB_TYPE_NUMBER, memo=200)
    cur.execute(SQL, cust=int(cust), amt=int(amt), tp="EARN", ord=ord_id, memo=memo)

# 이벤트처럼 몰릴 때는 묶어서 : 같은 그릇으로 executemany
def earn_many(cur, rows):
    cur.setinputsizes(cust=oracledb.DB_TYPE_NUMBER, amt=oracledb.DB_TYPE_NUMBER,
                      tp=10, ord=oracledb.DB_TYPE_NUMBER, memo=200)
    cur.executemany(SQL, [dict(cust=int(r["cust"]), amt=int(r["amt"]), tp="EARN",
                               ord=r.get("ord"), memo=r.get("memo")) for r in rows])

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

xplan

[다음 이벤트(분당 9,200건) · 같은 시간대]
  VERSION_COUNT              14 → 1   (V$SQL_BIND_CAPTURE · :CUST NUMBER · :ORD NUMBER · :MEMO VARCHAR2(2000))
  LOADS                      23 → 1
  cursor: mutex X            21% → 1% 미만
  API 응답 p95               1.3초 → 0.04초

모범 답안 서술

텍스트는 하나지만 바인드 값의 타입과 그릇이 호출마다 달랐다. python-oracledb는 미리 정해 주지 않으면 값을 보고 바인드 타입을 정한다. 주문 확정 경로는 고객 번호를 정수로 넘겨 :CUST가 NUMBER이고, 쿠폰 경로는 JSON의 문자열 "1234"를 그대로 넘겨 VARCHAR2다. 값이 없는(None) 자리는 문자열 타입으로 넘어가므로, 쿠폰 경로의 :ORD도 VARCHAR2가 된다. 오라클은 바인드 타입이 다르면 기존 자식을 같이 쓰지 않으므로 경로마다 자식이 갈린다(BIND_MISMATCH 9개). 여기에 :MEMO의 그릇이 더해진다. 드라이버는 문자열 바인드의 크기를 그때 값의 길이로 잡고, 오라클은 이를 32, 128, 2000바이트 구간으로 올려 잡는다. 메시지가 짧다가 길어지면 큰 그릇의 자식을 새로 만든다(BIND_LENGTH_UPGRADEABLE 4개). 두 가지가 곱해져 자식이 14개가 되었다. 하드 파싱은 23번뿐이지만 문제는 실행마다 생긴다. 실행할 때마다 오라클은 그 부모의 자식 목록을 mutex를 잡고 뒤져 타입과 그릇이 맞는 자식을 찾는다. 자식이 14개면 그 목록이 길고, 분당 9,000번이 같은 부모에 몰리니 cursor: mutex X를 두고 줄을 선다. 그 사이 새 조합이 오면 mutex를 잡은 채 새 자식을 만든다(LOADS 23). 고친 코드는 setinputsizes로 고객 번호, 금액, 주문 번호를 NUMBER로, tp와 memo를 컬럼 크기의 문자열로 고정한다. 고객 번호는 int()로 바꿔 넘긴다. 이제 None이 와도 NUMBER 자리로 넘어가고 메시지 길이와 상관없이 그릇이 같아서, 어느 경로든 같은 자식을 쓴다(VERSION_COUNT 14 → 1). 몰리는 이벤트에는 executemany로 묶어 넣으면 실행 호출 자체도 준다.

해설

같은 일이 PL/SQL 변수(PARSE-019), ODP.NET의 Size(PARSE-029), JDBC의 setNull 타입에서도 생긴다. 바인드 타입과 크기는 SQL 텍스트의 일부는 아니지만 커서를 같이 쓰는 조건이다.
memo=200은 문자 200자이므로 AL32UTF8에서 그릇이 2000바이트 구간이 된다. 짧은 메시지만 오는 시간에도 큰 그릇을 쓰지만, 자식이 하나로 고정되는 이득이 크다.
하드 파싱이 거의 없는데 library cache: mutex X가 1위인 경우(PARSE-015)는 원인이 다르다. 자식 수가 아니라 한 문장의 파스와 실행 호출이 몰리는 것이 원인이다. VERSION_COUNT와 V$SQL_SHARED_CURSOR를 먼저 보고 가른다.
INSERT는 계획이 하나뿐이라 자식이 늘어도 일량(7블록)은 같다. 그래서 실행계획만 보면 원인이 보이지 않고, 대기 이벤트와 자식 수를 함께 봐야 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황