출석 체크 이벤트가 열리는 10시 정각마다 포인트 적립이 3초를 넘긴다

기초DDL이커머스505 / 700

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

상황

이커머스 앱은 매일 10:00에 「출석 체크」 이벤트를 열고, 버튼을 누른 회원에게 포인트를 적립한다. 10:00부터 2분 동안 적립이 몰리는데(약 60세션, 초당 150건), 그때만 적립 API가 3초를 넘긴다. 적립 번호 tx_id는 시퀀스 SQ_POINT_TX에서 받는다. 시퀀스를 만든 외주 개발사의 인수 문서에는 이렇게 적혀 있다. "포인트 거래번호는 건너뛰면 안 되므로 NOCACHE ORDER로 생성함."

테이블과 인덱스

point_transactions: 45,000행 · 210블록 · PK tx_id · customer_id · amount · tx_type(EARN · USE · EXPIRE) · created_at
시퀀스 SQ_POINT_TX: NOCACHE ORDER · 데이터베이스는 단일 인스턴스(RAC 아님)
적립 한 건은 point_transactions INSERT 한 번과 COMMIT이다
포인트 거래번호는 고객 화면과 정산에서 거래를 가리키는 식별 번호로만 쓴다. 번호의 순서나 연속성을 따지는 업무는 없다

원본 SQL

sql

-- 외주 개발사가 만든 시퀀스 (인수 문서)
CREATE SEQUENCE SQ_POINT_TX START WITH 1 NOCACHE ORDER;

-- 적립 API — 버튼 한 번
INSERT INTO point_transactions (tx_id, customer_id, amount, tx_type, created_at)
VALUES (SQ_POINT_TX.NEXTVAL, :customer_id, 10, 'EARN', SYSDATE);
COMMIT;

실행계획 학습용 예시

xplan

[AWR — 10:00 ~ 10:05 · 적립 18,420건]
Top 대기 이벤트
EVENT                          WAITS    TIME(s)   AVG(ms)   %DB time
enq: SQ - contention          16,904     1,183        70       57.9
row cache lock                 4,310       237        55       11.6
DB CPU                                     411                 20.1
log file sync                 18,460        92         5        4.5

SQL ordered by Executions (같은 구간)
EXECUTIONS   SQL
    18,420   INSERT INTO POINT_TRANSACTIONS (TX_ID, …) VALUES (SQ_POINT_TX.NEXTVAL, …)
    18,420   update seq$ set increment$=:2, minvalue=:3, … where obj#=:1        ← 재귀 SQL

[적립 한 건 — 혼자 돌렸을 때 세션 통계]
  recursive calls     5
  db block changes    8   (point_transactions 와 PK 2 · SEQ$ 2 · 언두 4)
  redo size           1,412 bytes (그중 SEQ$ 갱신 몫 약 650)

[DBA_SEQUENCES — 10:05]
SEQUENCE_NAME   CACHE_SIZE   ORDER_FLAG   LAST_NUMBER
SQ_POINT_TX              0   Y                 63,421

문제

AWR의 대기 이벤트와 재귀 SQL, 시퀀스 정의를 근거로 10:00에 적립이 느려지는 원인을 설명하시오.
시퀀스를 고치시오. 인수 문서의 "건너뛰면 안 되므로 NOCACHE ORDER"라는 근거가 맞는지도 판단하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 번호를 1,000개씩 메모리에 꺼내 둔다 — SEQ$ 는 1,000번에 한 번 고친다 · 단일 인스턴스라 ORDER 는 필요 없다
ALTER SEQUENCE SQ_POINT_TX CACHE 1000 NOORDER;

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

xplan

[고친 뒤 — 다음 날 10:00 ~ 10:05 · 적립 18,610건 (학습용 예시)]
EVENT                          WAITS    TIME(s)   AVG(ms)
log file sync                 18,650        95         5
row cache lock                    22         0         1
enq: SQ - contention              19         0         2
  update seq$ 실행 18,420번 → 19번 · 10:00 적립 API 응답 3초 → 0.05초
  다음 병목 후보: PK tx_id 의 맨 오른쪽 리프 블록 (buffer busy waits) — 이번 구간에는 DB time 의 0.6%

모범 답안 서술

NOCACHE 시퀀스는 번호를 하나 줄 때마다 딕셔너리 테이블 SEQ$의 그 시퀀스 행을 고친다. AWR에서 재귀 SQL update seq$가 적립 INSERT와 똑같이 18,420번 실행된 것이 그 증거다. SEQ$를 고치는 동안 다른 세션은 같은 시퀀스의 다음 번호를 받을 수 없으므로, 60세션이 한 줄로 서서 enq: SQ - contention(16,904번, 평균 70ms, DB time의 57.9%)과 딕셔너리 캐시의 row cache lock을 기다린다. 혼자 돌리면 작은 비용이지만 초당 150건이 몰리는 2분 동안에는 줄이 된다. ALTER SEQUENCE SQ_POINT_TX CACHE 1000 NOORDER로 고친다. 번호 1,000개를 메모리에 꺼내 두고 SEQ$는 1,000번에 한 번만 고치므로, 같은 구간의 update seq$가 18,420번 → 19번으로 줄고 enq: SQ - contention이 사라진다(적립 응답 3초 → 0.05초). 인수 문서의 근거는 틀렸다. NOCACHE로도 빈 번호는 생긴다. 적립 INSERT가 ROLLBACK되면 받은 번호는 쓰이지 않고 사라진다. NOCACHE가 막아 주는 것은 캐시에 꺼내 둔 번호가 재시작으로 사라지는 경우뿐이다. ORDER는 RAC에서 여러 인스턴스가 번호를 요청 순서대로 주게 하는 옵션이라 단일 인스턴스에서는 뜻이 없다. 단일 인스턴스에서는 CACHE를 두어도 번호가 요청 순서대로 나간다. 대가는 인스턴스를 재시작하거나 시퀀스가 공유 풀에서 밀려나면 캐시에 남아 있던 번호가 최대 999개까지 빈 번호가 된다는 것이다. 포인트 거래번호는 거래를 가리키는 식별 번호일 뿐 연속성을 따지는 업무가 없으므로 받아들일 수 있다.

해설

CACHE를 얼마로 할지는 피크 속도로 정한다. 기본값 20이면 초당 150건에서 SEQ$를 초당 7번 고친다. 1,000이면 7초에 한 번이다. 크게 잡을수록 재시작 때 잃는 번호가 많아질 뿐 다른 비용은 거의 없다.
ORDER의 진짜 쓰임: RAC에서 NOORDER면 인스턴스마다 자기 캐시에서 번호를 주므로, 노드 1이 1 ~ 1,000을, 노드 2가 1,001 ~ 2,000을 동시에 나눠 준다. 그래서 번호 순서가 시각 순서와 어긋난다. ORDER를 붙이면 인스턴스끼리 번호를 맞추느라 대기가 생긴다. 어느 쪽이든 번호 순서대로 COMMIT된다는 보장은 없다.
캐시를 키우면 다음 병목은 PK 인덱스의 맨 오른쪽 리프 블록이다. 이 구간의 buffer busy waits는 0.6%로 아직 작다. 숫자가 커지면 역방향 키나 해시 파티션 인덱스를 검토한다(교재 12화 §10, 14화).
교재 16화와 17화에서 ADDM이 1위로 올린 Sequence Usage는 시퀀스를 부르지 않는 조각에서도 켜지는 표본 깃발이 근거였다. 그래서 "캐시를 키워라"라는 권고를 따르기 전에, update seq$ 실행 횟수와 enq: SQ - contention처럼 시퀀스를 실제로 부른 흔적을 먼저 확인해야 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황