매장 하나는 주문 7건인데 200곳 일 마감이 12만 블록을 읽는다

중급PLSQL버거15 / 700

상황

버거원 본사는 매일 23시 30분에 일 마감 프로시저를 돌려 매장마다 그날 주문 수를 SHOP_DAY_CLOSE에 쌓는다. 매장 코드는 옛 POS에서 숫자(1 ~ 200)였고, 프로시저는 그 시절 루프를 그대로 쓴다. 가맹점이 늘면서 마감 시각이 밀린다며 오과장이 TKPROF를 떠 왔다. 아래는 2013년 3월 15일 마감분이다.

테이블과 인덱스

ORD: 1,000,000행 · 22,422블록 · 주문 일시 순으로 쌓였다 · SHOP_NO VARCHAR2(10) 'SH0001' ~ 'SH0200'(200곳에 고르게) · ORD_DT VARCHAR2(8)
2013년 3월 15일 주문 1,344건(매장당 평균 약 7건)
SHOP: 200행 · 5블록 · PK SHOP_PK (SHOP_NO)
인덱스 ORD_X02 (SHOP_NO, ORD_DT): 가맹점주 앱용 · BLEVEL 2 · 리프 약 3,100블록 · ORD_X01 (ORD_DT, ORD_HMS) · 통계 최신
SHOP_DAY_CLOSE (SHOP_NO, CLOSE_DT, ORD_CNT): 하루 200행씩 쌓는 마감 테이블 · 인덱스 없음

원본 SQL

sql

PROCEDURE P_DAY_CLOSE(p_dt VARCHAR2) IS
  v_cnt NUMBER;
BEGIN
  FOR i IN 1 .. 200 LOOP
    SELECT COUNT(*) INTO v_cnt
      FROM ORD
     WHERE TO_NUMBER(SUBSTR(SHOP_NO, 3)) = i
       AND ORD_DT = p_dt;

    INSERT INTO SHOP_DAY_CLOSE (SHOP_NO, CLOSE_DT, ORD_CNT)
    VALUES ('SH' || LPAD(i, 4, '0'), p_dt, v_cnt);
  END LOOP;
  COMMIT;
END;

실행계획 학습용 예시 · TKPROF 요약

xplan

[TKPROF — 루프 안의 SELECT]
SELECT COUNT(*) FROM ORD WHERE TO_NUMBER(SUBSTR(SHOP_NO, 3)) = :B2 AND ORD_DT = :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    200      0.01       0.01          0          0          0           0
Fetch      200      1.02       1.05          0     122400          0         200
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      401      1.03       1.06          0     122400          0         200

[DBMS_XPLAN — 마지막 한 번 (i = 200)]
----------------------------------------------------------------------------------------
| Id  | Operation        | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |      1 |        |      1 |00:00:00.01 |     612 |
|   1 |  SORT AGGREGATE  |         |      1 |      1 |      1 |00:00:00.01 |     612 |
|*  2 |   INDEX SKIP SCAN| ORD_X02 |      1 |      1 |      7 |00:00:00.01 |     612 |
----------------------------------------------------------------------------------------
   2 - access("ORD_DT"=:B1)
       filter(("ORD_DT"=:B1 AND TO_NUMBER(SUBSTR("SHOP_NO",3))=:B2))

문제

매장 하나의 주문은 7건 남짓인데 SELECT 한 번에 612블록을 읽는 이유를 실행계획의 2번 줄로 설명하시오. 하루 마감 전체의 일량도 계산하시오.
프로시저를 고쳐 일량을 줄이시오. 루프를 남긴다면 SELECT의 조건을 어떻게 바꿀지, 루프를 없앤다면 어떤 한 문장으로 바꿀지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE P_DAY_CLOSE(p_dt VARCHAR2) IS
BEGIN
  -- 루프 200번 → 한 문장 : 그날 주문을 한 번 읽어 매장별로 센다 (주문 없는 매장도 0건으로)
  INSERT INTO SHOP_DAY_CLOSE (SHOP_NO, CLOSE_DT, ORD_CNT)
  SELECT s.SHOP_NO, p_dt, COUNT(o.ORD_NO)
    FROM SHOP s
    LEFT JOIN ORD o
      ON o.SHOP_NO = s.SHOP_NO
     AND o.ORD_DT  = p_dt
   GROUP BY s.SHOP_NO;
  COMMIT;
END;

-- (루프를 남겨야 한다면) 가공을 컬럼이 아니라 루프 변수 쪽으로 옮긴다
--   WHERE SHOP_NO = 'SH' || LPAD(i, 4, '0') AND ORD_DT = p_dt    → ORD_X02 두 컬럼 다 access · 한 번에 3블록

개선 후 실행계획 학습용 예시 · TKPROF 요약

xplan

[한 문장 판 — 2013-03-15]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                        |                |      1 |        |      0 |00:00:00.01 |      71 |
|   1 |  LOAD TABLE CONVENTIONAL                | SHOP_DAY_CLOSE |      1 |        |      0 |00:00:00.01 |      71 |
|   2 |   HASH GROUP BY                         |                |      1 |    200 |    200 |00:00:00.01 |      40 |
|*  3 |    HASH JOIN OUTER                      |                |      1 |   1370 |   1344 |00:00:00.01 |      40 |
|   4 |     INDEX FULL SCAN                     | SHOP_PK        |      1 |    200 |    200 |00:00:00.01 |       1 |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED | ORD            |      1 |   1370 |   1344 |00:00:00.01 |      39 |
|*  6 |      INDEX RANGE SCAN                   | ORD_X01        |      1 |   1370 |   1344 |00:00:00.01 |       7 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("O"."SHOP_NO"="S"."SHOP_NO")
   6 - access("O"."ORD_DT"=:B1)

모범 답안 서술

2번 줄은 ORD_X02의 INDEX SKIP SCAN이고, access에는 ORD_DT=:B1만 있으며 SHOP_NO 조건은 filter로 밀려나 있다. ORD_X02는 SHOP_NO로 먼저 정렬되어 있는데, 조건은 선두 컬럼 SHOP_NO를 SUBSTR과 TO_NUMBER로 가공한 값을 묻는다. 그 값은 인덱스에 없으므로 매장 하나를 찾아가지 못하고, 선두 컬럼 값 200개마다 한 번씩 건너뛰며 그날 항목을 찾은 뒤 가공한 값을 하나씩 대어 본다. 매장 하나의 7건을 세려고 매번 200곳을 다 들르는 셈이라 한 번에 612블록이다. 루프가 200번 돌므로 TKPROF의 query는 200 × 612 = 122,400블록이 된다. 같은 프로시저의 INSERT는 이미 숫자 i로 'SH' || LPAD(i, 4, '0')를 만들어 넣고 있다. 이 변환을 SELECT에도 쓰면, 즉 SHOP_NO = 'SH' || LPAD(i, 4, '0') AND ORD_DT = p_dt로 바꾸면 컬럼은 가공되지 않고 ORD_X02의 두 컬럼이 다 access가 되어 한 번에 3블록, 하루 600블록이다. 더 나은 답은 루프를 없애는 것이다. 그날 주문을 한 번만 읽어 매장별로 세는 INSERT … SELECT 한 문장이면 SQL 실행도 400번(SELECT 200, INSERT 200)에서 1번이 된다. 주문이 없는 매장도 0건으로 남겨야 하므로 SHOP을 기준으로 ORD를 LEFT JOIN한다. ORD_X01로 그날 1,344건(인덱스 7블록, 날짜순으로 모인 테이블 약 32블록)만 읽으므로 마감 전체가 122,400 → 약 71블록이다.

해설

함정: 루프를 한 문장으로 바꾸면서 FROM ORD … GROUP BY SHOP_NO만 쓰면 그날 주문이 없는 매장은 마감 테이블에 행이 생기지 않는다. 원래 프로시저는 0건 매장까지 200행을 다 넣었다.
SKIP SCAN이 보이면 선두 컬럼에 조건이 없거나, 이번처럼 선두 컬럼이 가공되어 access를 잃은 것이다. 선두 컬럼의 종류가 200개라 그나마 612블록에서 그쳤다(IDX-006).
코드 체계가 바뀌었을 때 변환은 바인드 쪽에서 하거나, 옛 코드와 새 코드를 잇는 매핑 컬럼을 SHOP에 두고 조인한다. 컬럼 쪽에서 변환하면 그 컬럼의 인덱스를 모두 잃는다.
루프 안의 SQL은 실행 횟수만큼 일이 곱해진다. 한 번에 612블록은 작아 보여도 Execute 200이 붙으면 12만 블록이다(교재 15화, DML 단원).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · TKPROF 요약 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · TKPROF 요약 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황