SQLP 튜닝 연습장

테마

튜닝 연습인덱스 스캔 효율과 테이블 액세스
ACC-069

정산 메뉴 오픈을 앞두고 화면 SQL 다섯 개에 인덱스 다섯 개를 달라는 요청서가 왔다

실전SELECT한빛유통144 / 700

상황 ​

새 정산 메뉴가 다음 달 문을 연다. 메뉴의 SQL 다섯 개는 모두 T_ORD_BIG을 읽는데, 지금 이 테이블에는 인덱스가 하나도 없다. 개발팀 요청서는 SQL마다 하나씩이다. "Q1에 (ORD_DT), Q2에 (ORD_DT, ORD_SEQ), Q3에 (CUS_ID), Q4에 (CUS_ID, ORD_ST), Q5에 (ORD_ST)" 배치 담당 오과장은 일요일 밤 재적재를 걱정한다(인덱스를 끄고 넣은 뒤 다시 짓는데, 인덱스 하나에 2 ~ 4초와 정렬 공간이 든다). 박팀장의 주문은 이렇다. "블록 수로 따져서 최소한으로. SQL마다 몇 블록을 읽는지, 무엇을 안 만드는지까지."

테이블과 인덱스 ​

  • T_ORD_BIG: 1,000,000행 · 5,826블록(블록당 약 172행, 행 평균 37바이트) · ORD_SEQ 순으로 쌓였다 · ORD_DT 하루 913건(전부 자정) · 2024년 6월 27,390건 · 2024년 2분기 83,083건 · ORD_ST COMPLETE 90% · PENDING 8% · CANCEL 2%(2분기 CANCEL 1,644건) · CUS_ID 2,000명(고객당 500건, 최근 1년 약 167건)
  • 같은 날짜의 주문도, 한 고객의 주문도 테이블 전체에 흩어져 있다. 그래서 ORD_DT나 CUS_ID로 시작하는 인덱스는 CLUSTERING_FACTOR가 약 1,000,000이다
  • 인덱스 규모(교재 04화 · 14화 실측과 그 비례): (ORD_DT) 리프 2,653 · (ORD_DT, ORD_SEQ) 3,344 · (CUS_ID) 3,060 · (CUS_ID, ORD_ST) 약 4,310 · (ORD_ST) 2,775 · (ORD_ST, ORD_DT) 약 3,890 · 모두 BLEVEL 2
  • 어림: 인덱스 항목 하나는 '컬럼 값 + 컬럼마다 길이 1바이트 + ROWID 7바이트 + 머리 약 4바이트'이고, 리프 한 블록에 약 7,200바이트를 쓴다(ORD_DT 8 · ORD_SEQ 5 · ORD_AMT 4 · CUS_ID 11 · ORD_ST 9바이트)
  • 낮에는 주문이 하루 약 900건 INSERT된다 · 일요일 밤 재적재는 TRUNCATE → 인덱스 UNUSABLE → APPEND → REBUILD다

원본 SQL ​

sql
-- Q1 「기간 매출」 · 정산 대시보드 · 하루 약 2,000번 · 보통 한 달
SELECT COUNT(*), SUM(ORD_AMT)
  FROM T_ORD_BIG
 WHERE ORD_DT >= :v_from AND ORD_DT < :v_to;

-- Q2 「정산 목록」 다음 페이지 · 하루 약 5,000번 · 11건씩 (키 기반 페이징)
SELECT *
  FROM (SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
          FROM T_ORD_BIG
         WHERE ORD_DT <= :p_dt
           AND (ORD_DT < :p_dt OR ORD_SEQ < :p_seq)
         ORDER BY ORD_DT DESC, ORD_SEQ DESC)
 WHERE ROWNUM <= 11;

-- Q3 「고객 주문 이력」 · 상담원 · 하루 약 6,000번 · 최근 1년
SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE CUS_ID = :v_cus
   AND ORD_DT >= ADD_MONTHS(TRUNC(SYSDATE), -12)
 ORDER BY ORD_DT DESC;

-- Q4 「고객 상태 요약」 · 상담원 · 하루 약 3,000번
SELECT ORD_ST, COUNT(*)
  FROM T_ORD_BIG
 WHERE CUS_ID = :v_cus
 GROUP BY ORD_ST;

-- Q5 「일자별 취소 건수」 · 고객센터 상황판 · 하루 약 1,500번 · 한 분기
SELECT ORD_DT, COUNT(*)
  FROM T_ORD_BIG
 WHERE ORD_ST = 'CANCEL'
   AND ORD_DT >= :v_from AND ORD_DT < :v_to
 GROUP BY ORD_DT;

실행계획 학습용 예시 · 인덱스 규모는 교재 04화 · 14화 실측과 그 비례 ​

xplan
[지금 · 인덱스 없음]  Q1 ~ Q5 모두 TABLE ACCESS FULL · Buffers 5,826 (Q2는 SORT ORDER BY STOPKEY, Q3은 SORT ORDER BY가 붙는다)

[Q3 · CUS_000123 · 지금]
----------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |    167 |00:00:00.06 |    5826 |
|   1 |  SORT ORDER BY     |           |      1 |    167 |    167 |00:00:00.06 |    5826 |
|*  2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |    167 |    167 |00:00:00.06 |    5826 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("CUS_ID"=:V_CUS AND "ORD_DT">=ADD_MONTHS(TRUNC(SYSDATE@!),-12)))

[요청서 인덱스를 INVISIBLE로 만들어 세션에서 잰 값]
        요청 인덱스            옵티마이저가 고른 길                                 Buffers
  Q1    (ORD_DT)              TABLE ACCESS FULL (인덱스로 강제하면 27,465)          5,826
  Q2    (ORD_DT, ORD_SEQ)     INDEX RANGE SCAN DESCENDING + 테이블 11행               15
  Q3    (CUS_ID)              INDEX RANGE SCAN + 테이블 500행 + SORT ORDER BY         504
  Q4    (CUS_ID, ORD_ST)      INDEX RANGE SCAN (테이블에 가지 않는다)                   5
  Q5    (ORD_ST)              TABLE ACCESS FULL (인덱스로 강제하면 5,878)          5,826

문제 ​

  1. 요청서의 인덱스로 Q1 ~ Q5가 위처럼 되는 이유를 설명하시오. 특히 Q1, Q3, Q5가 인덱스를 두고도 많이 읽는 이유를 CLUSTERING_FACTOR와 테이블로 가는 행 수로 설명하시오.
  2. 최소 인덱스 세트를 설계해 DDL을 작성하시오. 세트로 Q1 ~ Q5가 각각 어떤 스캔으로 몇 블록을 읽을지 어림하시오.
  3. 고른 세트와 요청서의 다섯 개를 크기, 일요일 재적재, 낮 INSERT, 하루 읽는 블록으로 비교하시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자