SQLP 튜닝 연습장

테마

튜닝 연습Lock과 동시성 제어
LOCK-016

타임세일이 열리는 10시 정각마다 주문 등록이 0.03초에서 0.6초로 늘어난다

실전DDL한빛유통511 / 700

상황 ​

한빛유통 쇼핑몰은 매주 금요일 10:00에 타임세일을 연다. 10:00부터 5분 동안 주문 등록 세션이 120개 안팎으로 몰리고, 그동안 주문 등록이 0.03초에서 0.6초로 늘어난다. 주문번호는 이미 시퀀스(SQ_ORD_SEQ, CACHE 1000)로 받고 있어서 행 락 대기는 없다. 박팀장의 주문이다. "인덱스마다 무엇으로 바꿀지, 그러면 어떤 화면이 얼마나 손해를 보는지까지 적어 와라."

테이블과 인덱스 ​

  • T_ORD 주문: 100,000행 · 896블록 · ORD_SEQ 시퀀스 번호 · ORD_DT DATE(주문 시각, 시분초 있음, SYSDATE로 넣는다) · CUS_ID(고객 2,000명)
  • 인덱스 셋: PK_T_ORD (ORD_SEQ) · IX_T_ORD_DT (ORD_DT) · IX_T_ORD_CUS (CUS_ID, ORD_DT) · 통계 최신
  • V$SQL에서 이 인덱스들을 쓰는 SQL은 넷이다
    • Q1 주문 상세 WHERE ORD_SEQ = :seq · 초당 약 300번 · PK_T_ORD 3블록
    • Q2 정산 적재 WHERE ORD_SEQ > :last_seq · 매일 02:00 한 번 · 약 3,000행 · PK_T_ORD 범위 스캔 38블록
    • Q3 실시간 주문 현황판 SELECT COUNT(*) … WHERE ORD_DT >= SYSDATE - 10/1440 · 40대가 10초마다 · IX_T_ORD_DT 범위 스캔 32블록
    • Q4 내 주문 목록 WHERE CUS_ID = :c ORDER BY ORD_DT DESC · IX_T_ORD_CUS
  • 파티셔닝 옵션은 아직 승인되지 않았다(강도현 2021-03-30 메모 "파티션은 유상 옵션이라 승인 못 받음")

원본 SQL ​

sql
-- 주문 등록 (타임세일 때 120세션이 동시에)
INSERT INTO T_ORD (ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT)
VALUES (SQ_ORD_SEQ.NEXTVAL, :cus_id, SYSDATE, 'PENDING', :amt);

-- 지금 인덱스
CREATE UNIQUE INDEX PK_T_ORD ON T_ORD (ORD_SEQ);
CREATE INDEX IX_T_ORD_DT  ON T_ORD (ORD_DT);
CREATE INDEX IX_T_ORD_CUS ON T_ORD (CUS_ID, ORD_DT);

-- 이 인덱스들을 쓰는 SQL
SELECT * FROM T_ORD WHERE ORD_SEQ = :seq;                                            -- Q1
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT FROM T_ORD WHERE ORD_SEQ > :last_seq;         -- Q2
SELECT COUNT(*) FROM T_ORD WHERE ORD_DT >= SYSDATE - 10/1440;                        -- Q3
SELECT ORD_SEQ, ORD_DT, ORD_AMT FROM T_ORD WHERE CUS_ID = :c ORDER BY ORD_DT DESC;    -- Q4

실행계획 학습용 예시 ​

xplan
[AWR — 금요일 10:00 ~ 10:05 · 주문 등록 14,800건 · 동시 약 120세션]
EVENT                            WAITS     TIME(s)   AVG(ms)   %DB time
buffer busy waits              186,300      1,240         7       44.8
enq: TX - index contention       9,870        514        52       18.6
DB CPU                                        612                 22.1
log file sync                   14,830        118         8        4.3
enq: TX - row lock contention        0          0                  0.0

Segments by Buffer Busy Waits
OBJECT          TYPE    BUSY WAITS   %Total
PK_T_ORD        INDEX       87,560     47.0
IX_T_ORD_DT     INDEX       82,350     44.2
T_ORD           TABLE       11,240      6.0
IX_T_ORD_CUS    INDEX        5,150      2.8

[Q3 현황판 — ALLSTATS LAST · 10:04]
-----------------------------------------------------------------------------------------
| Id  | Operation         | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |      1 |        |      1 |00:00:00.01 |      32 |
|   1 |  SORT AGGREGATE   |             |      1 |      1 |      1 |00:00:00.01 |      32 |
|*  2 |   INDEX RANGE SCAN| IX_T_ORD_DT |      1 |  11900 |  11900 |00:00:00.01 |      32 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ORD_DT">=SYSDATE@!-.0069444444444444444444444444444444444444)

문제 ​

  1. AWR을 근거로 경합이 어느 인덱스의 어느 블록에서 왜 생기는지 설명하시오. IX_T_ORD_CUS의 경합이 왜 작은지도 쓰시오.
  2. 세 인덱스마다 역방향 키 인덱스, 해시 파티션 인덱스, 키 앞에 분산 값 두기, 그대로 두기 가운데 무엇을 고를지 Q1 ~ Q4에 주는 영향으로 정하고, DDL과 바뀌는 SQL을 쓰시오.
  3. 고친 뒤 SQL마다 읽는 블록이 어떻게 바뀌는지와 그 밖의 대가를 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자