매장 실시간 매출판을 연 뒤로 점심 결제가 서로 다른 매장끼리 줄을 선다

중급DDL버거540 / 700

상황

버거 본사는 이번 달 「매장 실시간 매출판」을 열었다. 결제할 때마다 POS가 SHOP의 그 매장 행에 오늘 결제 건수와 금액을 더한다. 그 뒤로 점심 피크의 결제 완료가 평균 1.6초에서 3.9초로 늘었다. 개발팀은 이렇게 말한다. "매장마다 자기 행만 고치니 락이 걸릴 리가 없어요. POS가 두 대인 매장끼리만 가끔 기다릴 텐데요." DBA가 피크 한 시간의 기록을 뽑아 왔다.

테이블과 인덱스

SHOP 매장: 200행 · 5블록 · SHOP_PK (SHOP_NO) · TODAY_CNT와 TODAY_AMT는 원래 있던 컬럼으로, 밤 배치가 하루 한 번 채우던 칸이다
지난달 DBA가 "읽기만 하는 마스터라 빈자리가 아깝다"며 SHOP을 PCTFREE 0으로 MOVE했다. 데이터가 든 블록은 셋이다(블록 1,203 · 1,204 · 1,205에 68 · 68 · 64행). INI_TRANS 1 · 블록마다 ITL 2칸
결제는 주문과 주문상품을 INSERT하고, SHOP 행을 UPDATE한 뒤, 카드 승인 응답(1 ~ 2초)을 받고 COMMIT한다
점심 피크에는 매장 200곳이 분당 300건 남짓 결제한다

원본 SQL

sql

-- 결제 저장 (이번 달 판)
INSERT INTO ORD (ORD_NO, ORD_DT, ORD_HMS, SHOP_NO)
VALUES (SQ_ORD_NO.NEXTVAL, :dt, :hms, :shop_no)
RETURNING ORD_NO INTO v_no;
INSERT INTO ORD_ITEM (ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS)
VALUES (v_no, :item_id, :qty, :dt, :hms);                -- 상품마다

UPDATE SHOP                                               -- 실시간 매출판 (이번 달 추가)
   SET TODAY_CNT = TODAY_CNT + 1, TODAY_AMT = TODAY_AMT + :amt
 WHERE SHOP_NO = :shop_no;

-- 카드 승인 응답을 기다린다 (1 ~ 2초)
COMMIT;

-- 지난달 정비
ALTER TABLE SHOP MOVE PCTFREE 0;
ALTER INDEX SHOP_PK REBUILD;

실행계획 학습용 예시 · ITL 대기의 모양은 교재 12화 실측(실습/04 [I1] · [I2])

xplan

[AWR — 점심 11:30 ~ 12:30 · 결제 18,240건]
EVENT                               WAITS     TIME(s)   AVG(ms)   %DB time
enq: TX - allocate ITL entry        6,912     9,815      1,420       61.8
DB CPU                                        2,410                  15.2
enq: TX - row lock contention         214       171        799        1.1
log file sync                      18,300       142          8        0.9

Segments by ITL Waits                  Segments by Row Lock Waits
OBJECT   TYPE    ITL WAITS              OBJECT   TYPE    ROW LOCK WAITS
SHOP     TABLE       6,912              SHOP     TABLE        214

[V$SESSION — 12:04:10 · 기다리는 세션 일부]
SID   EVENT                            SECS   BLOCKING_SESSION   하는 문장                            고치는 매장   행이 든 블록
305   enq: TX - allocate ITL entry        1   212                UPDATE SHOP … WHERE SHOP_NO = :B1    SH0031       7 / 1,203
318   enq: TX - allocate ITL entry        1   212                UPDATE SHOP …                        SH0057       7 / 1,203
412   enq: TX - allocate ITL entry        2   190                UPDATE SHOP …                        SH0140       7 / 1,205
(212 는 SH0012 · 블록 1,203 · 190 은 SH0161 · 블록 1,205 — 둘 다 카드 승인 응답을 기다리는 중 · SQL*Net message from client)

[V$LOCK — 같은 시각 · 305 와 212]
SID   TYPE  대상 / 이름표       LMODE  REQUEST  BLOCK
305   TM    SHOP                  3       0       0
305   TX    212 의 트랜잭션        0       4       0
212   TM    SHOP                  3       0       0
212   TX    212 의 트랜잭션        6       0       1

[DBA_TABLES · SHOP]   PCT_FREE 0 · INI_TRANS 1 · 데이터 블록 3 · 블록마다 빈자리 거의 0

문제

AWR과 V$SESSION, V$LOCK을 근거로 서로 다른 매장의 결제가 무엇을 기다리는지 설명하시오. 행 락 대기와 어떻게 다른지도 쓰시오.
DDL로 고치시오. ALTER TABLE SHOP INITRANS 16 한 줄로는 왜 안 되는지와 그 DDL의 대가도 쓰시오.
결제 쪽 SQL에서 줄을 더 줄일 방법을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 1. 영업이 끝난 02:00 — 데이터 블록 셋을 다시 지어 ITL 칸과 빈자리를 함께 잡는다
ALTER TABLE SHOP MOVE ONLINE PCTFREE 30 INITRANS 16;       -- 12.2 이상 · 인덱스를 유지하며 옮긴다
--   ONLINE 이 안 되는 버전 : ALTER TABLE SHOP MOVE PCTFREE 30 INITRANS 16;  ALTER INDEX SHOP_PK REBUILD;

-- 2. 결제 저장 — SHOP 행은 카드 승인 뒤, COMMIT 바로 앞에서 고친다
INSERT INTO ORD (ORD_NO, ORD_DT, ORD_HMS, SHOP_NO)
VALUES (SQ_ORD_NO.NEXTVAL, :dt, :hms, :shop_no)
RETURNING ORD_NO INTO v_no;
INSERT INTO ORD_ITEM (ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS)
VALUES (v_no, :item_id, :qty, :dt, :hms);

-- 카드 승인 응답을 기다린다 (1 ~ 2초) — 이 동안 SHOP 블록의 칸을 쥐지 않는다

UPDATE SHOP
   SET TODAY_CNT = TODAY_CNT + 1, TODAY_AMT = TODAY_AMT + :amt
 WHERE SHOP_NO = :shop_no;
COMMIT;

개선 후 실행계획 학습용 예시 · ITL 대기의 모양은 교재 12화 실측(실습/04 [I1] · [I2])

xplan

[다음 주 같은 시간 · 결제 18,410건 (학습용 예시)]
EVENT                               WAITS     TIME(s)   AVG(ms)   %DB time
DB CPU                                        2,455                  71.4
log file sync                      18,470       145          8        4.2
enq: TX - row lock contention          61        12        197        0.3
enq: TX - allocate ITL entry            0         0                   0.0
  결제 완료 평균 3.9초 → 1.5초 · SHOP 데이터 블록 3 → 5 (블록마다 약 40행 · ITL 16칸)

모범 답안 서술

1위 대기는 enq: TX - allocate ITL entry다(6,912번, 평균 1.42초, DB time의 61.8%). 행 락 대기(row lock contention)는 214번뿐이고 POS가 두 대인 매장의 몫이다. V$SESSION에서 305는 SH0031을, 막은 212는 SH0012를 고친다. 서로 다른 매장(행)인데 두 행이 같은 블록 1,203에 있다. V$LOCK을 보면 305는 212의 트랜잭션을 REQUEST 4로 요청한다. 남이 잠근 행을 원하는 TX 6이 아니라, 그 트랜잭션이 끝나 칸이 비기를 기다리는 모양이다. 블록을 고치는 트랜잭션은 블록 머리의 ITL 칸 하나에 자기 이름표를 적는다. SHOP은 INITRANS 1이라 블록마다 칸이 2개뿐이고, 원래는 블록의 빈자리를 써서 칸을 늘린다. 그런데 지난달 PCTFREE 0으로 MOVE해 블록이 꽉 차 있으므로 칸을 늘릴 빈자리가 없다. 블록 하나에 매장이 68곳이나 있으니, 같은 블록의 세 번째 매장이 결제하는 순간 앞의 둘 중 하나가 COMMIT할 때까지 기다린다. 앞사람은 카드 승인 응답(1 ~ 2초)을 받은 뒤에야 COMMIT하므로 기다림이 평균 1.42초다. ALTER TABLE SHOP INITRANS 16 한 줄로는 안 된다. INITRANS는 앞으로 새로 만들 블록에만 적용되고, 지금 데이터가 든 블록 셋의 칸은 그대로다. 그래서 MOVE로 블록을 다시 지으며 INITRANS 16과 PCTFREE 30을 함께 준다. 칸 16개가 미리 잡히고, 빈자리 30%로 칸을 더 늘릴 수도 있다. 대가는 블록당 담는 행이 68에서 40 남짓으로 줄어 데이터 블록이 3에서 5로 느는 것이다. 200행짜리 테이블이라 무시할 만하다. MOVE는 행을 옮겨 ROWID가 바뀌므로 ONLINE 없이 하면 SHOP_PK가 UNUSABLE이 된다. 12.2 이상이면 MOVE ONLINE으로 인덱스를 유지하며 옮기고, 아니면 영업이 끝난 뒤 옮기고 바로 REBUILD한다. 결제 쪽에서는 SHOP UPDATE를 카드 승인 뒤, COMMIT 바로 앞으로 옮긴다. 그러면 칸을 쥐는 시간이 1 ~ 2초에서 몇 ms로 줄어, 같은 블록에 동시에 머무는 트랜잭션 자체가 준다. POS 두 대끼리의 행 락 대기도 함께 준다(214 → 61번).

해설

읽기만 하는 마스터라는 것은 기록되지 않은 전제였다. PCTFREE 0은 그 전제에서는 맞는 처방이었다. 실시간 매출판이 결제마다 SHOP을 고치면서 전제가 깨졌고, 아무도 정비 기록과 새 기능을 이어 보지 않았다.
매장 행을 여러 블록으로 흩는 방법도 있다. PCTFREE를 크게 주거나 MINIMIZE RECORDS_PER_BLOCK으로 블록당 행 수를 줄이면 한 블록에 몰리는 트랜잭션 자체가 준다. 200행짜리 뜨거운 테이블이라 공간은 아깝지 않다.
근본적으로는 결제마다 공용 행을 고치는 설계가 문제다. 매출판이 주문을 1분마다 집계하거나 결제 기록을 쌓아 따로 더하면 결제가 SHOP을 잠그지 않는다. 다만 화면이 실시간이어야 한다는 요구와 맞춰 봐야 한다.
인덱스 블록에도 ITL 칸이 있다. 리프가 꽉 찬 인덱스를 여러 세션이 함께 고치면 같은 대기가 인덱스 세그먼트에서 난다. 그때는 ALTER INDEX … REBUILD INITRANS n으로 고친다.
교재 12화의 연습 DB에서도 모양이 같았다. 꽉 찬 블록의 서로 다른 세 행을 세 세션이 고치자 셋째가 allocate ITL entry, TX REQUEST 4로 기다렸고, INITRANS 3으로 다시 만들자 아무도 기다리지 않았다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · ITL 대기의 모양은 교재 12화 실측(실습/04 [I1] · [I2]) ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · ITL 대기의 모양은 교재 12화 실측(실습/04 [I1] · [I2]) ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황