주문번호 중복은 사라졌는데 점심 피크의 결제 저장이 여전히 느리다

중급DDL버거510 / 700

상황

버거원 POS는 지난달 주문번호를 MAX+1에서 시퀀스 SQ_ORD_NO(CACHE 100)로 바꿨다. 주문번호 중복과 다시 시도는 사라졌다. 그런데 점심 피크(11:30 ~ 12:30)에는 결제 저장이 여전히 평균 0.4초이고, 한가한 오후에는 0.02초다. DBA가 피크 한 시간의 AWR과, 기다리는 세션이 가리키는 블록 주소를 뽑아 왔다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_PK (ORD_NO) · ORD_NO는 시퀀스 번호라 늘 지금까지보다 크다
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · 주문당 평균 2.5행
이 문제의 POS 운영 DB에는 PK 두 개만 있다. ORD_X01 같은 날짜 인덱스는 본사 분석 DB에만 있다
ORD_NO를 쓰는 SQL은 모두 등치 조건이다(영수증 재출력 ORD_NO = :no, 주문상품 조회 ORD_ITEM.ORD_NO = :no). V$SQL에서 ORD_NO로 범위를 읽거나 정렬하는 SQL은 찾지 못했다
피크에 동시에 결제를 저장하는 세션은 약 180개다

원본 SQL

sql

-- 결제 저장 (지난달 판)
INSERT INTO ORD (ORD_NO, ORD_DT, ORD_HMS, SHOP_NO)
VALUES (SQ_ORD_NO.NEXTVAL, TO_CHAR(SYSDATE, 'YYYYMMDD'), TO_CHAR(SYSDATE, 'HH24MISS'), :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, TO_CHAR(SYSDATE, 'YYYYMMDD'), TO_CHAR(SYSDATE, 'HH24MISS'));
COMMIT;

-- 지금 인덱스
-- ORD_PK      (ORD_NO)            UNIQUE
-- ORD_ITEM_PK (ORD_NO, ITEM_ID)   UNIQUE

실행계획 학습용 예시 · 경합 이벤트의 모양은 교재 12화 실측

xplan

[AWR — 11:30 ~ 12:30 · 결제 54,120건]
Top 대기 이벤트
EVENT                            WAITS     TIME(s)   AVG(ms)   %DB time
buffer busy waits              412,380      2,887         7       41.2
enq: TX - index contention      18,760        938        50       13.4
DB CPU                                      1,712                 24.4
log file sync                   54,300        407         7        5.8
enq: TX - row lock contention        0          0                  0.0

Segments by Buffer Busy Waits
OBJECT        TYPE    BUSY WAITS   %Total
ORD_PK        INDEX      241,230     58.5
ORD_ITEM_PK   INDEX      148,870     36.1
ORD           TABLE       19,790      4.8

[V$SESSION — 12:04:31 · 기다리는 세션 일부]
SID   EVENT                         P1(file#)  P2(block#)  P3(class#)  하는 문장
212   buffer busy waits                     7     183,422           1  INSERT INTO ORD …
305   buffer busy waits                     7     183,422           1  INSERT INTO ORD …
311   buffer busy waits                     7     183,422           1  INSERT INTO ORD …
128   enq: TX - index contention                                       INSERT INTO ORD_ITEM …
247   buffer busy waits                     8      91,077           1  INSERT INTO ORD_ITEM …

[DBA_EXTENTS 로 찾은 블록]
  7 / 183,422   ORD_PK 의 리프 블록 · 지금 맨 오른쪽 리프 (가장 큰 ORD_NO 가 든 블록)
  8 / 91,077    ORD_ITEM_PK 의 리프 블록 · 지금 맨 오른쪽 리프

[인스턴스 통계 · 같은 구간]
  leaf node splits 1,212 · 그중 leaf node 90-10 splits 1,209

문제

AWR과 블록 주소를 근거로 무엇이 무엇을 기다리는지 설명하시오. 행 락과 무엇이 다른지도 쓰시오.
DDL로 고치시오. 그 DDL이 주는 대가와, 이 문제에서 그 대가를 받아들여도 되는 근거를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 영업이 끝난 02:00 — 두 PK 인덱스의 키를 바이트 역순으로 저장한다 (이웃한 번호가 서로 다른 리프로 간다)
ALTER INDEX ORD_PK REBUILD REVERSE;
ALTER INDEX ORD_ITEM_PK REBUILD REVERSE;

개선 후 실행계획 학습용 예시 · 경합 이벤트의 모양은 교재 12화 실측

xplan

[고친 뒤 — 다음 주 같은 시간대 · 결제 54,870건 (학습용 예시)]
EVENT                            WAITS     TIME(s)   AVG(ms)   %DB time
DB CPU                                      1,806                 61.3
log file sync                   55,010        412         7       14.0
buffer busy waits               21,640         49         2        1.7
enq: TX - index contention         310          4        13        0.1
  결제 저장 평균 0.4초 → 0.03초 · leaf node splits 1,212 → 2,410 (90-10 분할이 50-50 분할로 바뀐다)
  영수증 재출력 ORD_NO = :no   INDEX UNIQUE SCAN ORD_PK · 4블록 그대로

모범 답안 서술

기다림의 1위는 행 락이 아니다. enq: TX - row lock contention은 0이고, buffer busy waits(412,380번, DB time의 41.2%)와 enq: TX - index contention이 위에 있다. 기다리는 세션의 블록 주소(P1, P2)는 거의 모두 7/183,422 하나이고, 그 블록은 ORD_PK의 맨 오른쪽 리프다. ORD_NO는 시퀀스 번호라 늘 지금까지보다 크므로 180세션이 넣는 새 키가 전부 이 리프 블록 하나로 간다. 한 세션이 그 블록을 고치는 동안 다른 세션은 같은 버퍼를 기다린다(버퍼 락). 블록이 차면 90-10으로 나뉘는데(1,212번 중 1,209번), 나뉘는 동안 그 블록에 넣으려던 세션은 enq: TX - index contention을 기다린다. ORD_ITEM_PK도 ORD_NO가 맨 앞이라 같은 일을 겪는다(36.1%). 두 PK 인덱스를 역방향 키로 다시 만든다. 역방향 키 인덱스는 키의 바이트 순서를 뒤집어 저장하므로 1004211과 1004212처럼 이웃한 번호가 서로 다른 리프로 흩어진다. 몰림이 사라져 buffer busy waits가 412,380 → 21,640번, 결제 저장이 평균 0.4초 → 0.03초가 된다. 등치 조건은 뒤집은 값끼리 비교하면 되므로 영수증 재출력(ORD_NO = :no)은 그대로 INDEX UNIQUE SCAN 4블록이다. 대가는 셋이다. ① ORD_NO로 범위를 읽거나 정렬하는 검색을 잃는다. ORD_NO BETWEEN이나 ORDER BY ORD_NO는 인덱스 범위 스캔을 못 하고 인덱스나 테이블을 전부 읽는다. ② 키마다 다른 블록을 고치므로 버퍼 캐시에 올라와 있어야 할 리프 블록이 맨 오른쪽 몇 개에서 인덱스 전체로 늘어난다. ③ 리프가 반씩 나뉘어(50-50) 인덱스가 커지고 redo가 는다(교재 14화에서 한 세션으로 잰 값은 redo 2.4배). 이 문제에서는 V$SQL에서 ORD_NO를 쓰는 SQL이 모두 등치 조건이었으므로 ①을 잃어도 되고, 180세션의 몰림이 ②와 ③보다 비싸다.

해설

해시 파티션 인덱스도 답이다. ORD_PK를 GLOBAL PARTITION BY HASH (ORD_NO) PARTITIONS 16으로 만들면 맨 오른쪽 리프가 16개로 나뉜다. 범위 검색은 16개 파티션을 모두 찾아가는 대가를 치르지만 살아 있다. 다만 파티셔닝은 EE의 유료 옵션이다(교재 14화).
오프라인 REBUILD 중에는 DML이 줄을 선다. 다시 만드는 동안 ORD에 TM 4가 걸려 결제 INSERT가 기다린다. 그래서 영업이 끝난 02:00에 한다.
시퀀스 캐시와는 상관이 없다. CACHE를 키워도 번호는 계속 커지므로 몰림은 그대로다. 몰림을 없애려면 키가 들어가는 자리를 흩어야 한다.
키 앞에 분산 값을 붙이는 방법(매장 번호나 MOD(ORD_NO, 16)을 앞에 둔 결합 키)도 같은 원리다. 흩는 만큼 범위 검색과 정렬을 잃는다는 대가의 구조도 같다(교재 14화).
세션이 하나뿐인 적재 배치에는 보통 인덱스가 더 싸다. 맨 오른쪽 블록을 90-10으로 꽉 채워 붙이므로 redo와 블록 변경이 가장 적다. 역방향 키는 여러 세션이 동시에 넣는 OLTP에서만 값을 한다(교재 12화 §10).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 경합 이벤트의 모양은 교재 12화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 경합 이벤트의 모양은 교재 12화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황