요일별 시간대 주문 리포트가 같은 22,422블록을 읽는 일자별 리포트보다 세 배 느리다

중급SELECT버거660 / 700

상황

본사 마케팅팀의 「요일별 시간대 주문 수」 리포트는 2년 치 주문 100만 건을 요일과 시간대로 묶는다. 1.7초가 걸리는데, 같은 주문을 일자별로 묶는 리포트는 0.5초다. DBA는 "둘 다 Buffers 22,422로 같은데 왜 세 배 차이가 나는지 모르겠다. recursive calls도 거의 없다"라고 한다. 요일 이름과 시간대 구분은 개발팀 공통 함수 두 개가 만든다. 두 함수 모두 테이블을 읽지 않는다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) '20120101' ~ '20131231'(731일) · ORD_HMS VARCHAR2(6) 09시 ~ 22시 · 두 컬럼 모두 NULL 허용
인덱스 ORD_X01 (ORD_DT, ORD_HMS) 리프 3,774블록: 두 컬럼이 NULL 허용이라 이 리포트는 테이블을 FULL로 읽는다
FN_DOW_NM, FN_HOUR_BAND: PL/SQL 계산만 하는 함수(SQL 없음) · 통계 최신

원본 SQL

sql

CREATE OR REPLACE FUNCTION FN_DOW_NM(p_ymd VARCHAR2) RETURN VARCHAR2 IS
BEGIN
  RETURN TO_CHAR(TO_DATE(p_ymd, 'YYYYMMDD'), 'DY', 'NLS_DATE_LANGUAGE=KOREAN');
END;
/

CREATE OR REPLACE FUNCTION FN_HOUR_BAND(p_hms VARCHAR2) RETURN VARCHAR2 IS
  v_hh PLS_INTEGER := TO_NUMBER(SUBSTR(p_hms, 1, 2));
BEGIN
  RETURN CASE WHEN v_hh < 11 THEN '1.오전'
              WHEN v_hh < 14 THEN '2.점심'
              WHEN v_hh < 17 THEN '3.오후'
              WHEN v_hh < 20 THEN '4.저녁'
              ELSE '5.야간' END;
END;
/

SELECT FN_DOW_NM(ORD_DT) AS dow, FN_HOUR_BAND(ORD_HMS) AS band, COUNT(*) AS ord_cnt
  FROM ORD
 GROUP BY FN_DOW_NM(ORD_DT), FN_HOUR_BAND(ORD_HMS)
 ORDER BY 1, 2;

실행계획 학습용 예시

xplan

[DISPLAY_CURSOR (ALLSTATS LAST)]
---------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     35 |00:00:01.74 |   22422 |          |
|   1 |  SORT GROUP BY      |      |      1 |   1000K|     35 |00:00:01.74 |   22422 | 6144  (0)|
|   2 |   TABLE ACCESS FULL | ORD  |      1 |   1000K|   1000K|00:00:00.11 |   22422 |          |
---------------------------------------------------------------------------------------------------

[V$SQL: 이 문장]       ELAPSED_TIME 1.74 초 · CPU_TIME 1.71 초 · PLSQL_EXEC_TIME 1.12 초 · BUFFER_GETS 22,422
[세션 통계]            recursive calls 3 · execute count 1
[비교: 일자별 리포트]  SELECT ORD_DT, COUNT(*) FROM ORD GROUP BY ORD_DT · 0.51 초 · Buffers 22,422 · PLSQL_EXEC_TIME 0

문제

읽는 블록 수가 같은데 세 배 느린 이유를 설명하시오. recursive calls에 아무것도 잡히지 않는 이유와, 그 시간을 무엇으로 확인할 수 있는지도 쓰시오.
결과가 바뀌지 않게 고치시오. 함수에 DETERMINISTIC을 붙이거나 스칼라 서브쿼리로 감싸는 안이 여기서 효과가 있는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 함수 두 개를 SQL 식으로 옮긴다 (PL/SQL 로 건너가지 않는다 · 시간대 경계는 함수와 같다: 11 · 14 · 17 · 20시)
SELECT TO_CHAR(TO_DATE(ORD_DT, 'YYYYMMDD'), 'DY', 'NLS_DATE_LANGUAGE=KOREAN') AS dow,
       CASE WHEN ORD_HMS < '110000' THEN '1.오전'
            WHEN ORD_HMS < '140000' THEN '2.점심'
            WHEN ORD_HMS < '170000' THEN '3.오후'
            WHEN ORD_HMS < '200000' THEN '4.저녁'
            ELSE '5.야간' END                                         AS band,
       COUNT(*) AS ord_cnt
  FROM ORD
 GROUP BY TO_CHAR(TO_DATE(ORD_DT, 'YYYYMMDD'), 'DY', 'NLS_DATE_LANGUAGE=KOREAN'),
          CASE WHEN ORD_HMS < '110000' THEN '1.오전'
               WHEN ORD_HMS < '140000' THEN '2.점심'
               WHEN ORD_HMS < '170000' THEN '3.오후'
               WHEN ORD_HMS < '200000' THEN '4.저녁'
               ELSE '5.야간' END
 ORDER BY 1, 2;

개선 후 실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     35 |00:00:00.57 |   22422 |          |
|   1 |  SORT GROUP BY      |      |      1 |   1000K|     35 |00:00:00.57 |   22422 | 6144  (0)|
|   2 |   TABLE ACCESS FULL | ORD  |      1 |   1000K|   1000K|00:00:00.11 |   22422 |          |
---------------------------------------------------------------------------------------------------
[V$SQL]  ELAPSED_TIME 0.57 초 · PLSQL_EXEC_TIME 0 · 결과 35행 (원래 SQL과 MINUS 양방향 0행)

[다른 판 (같은 100만 행)]             경과      PLSQL_EXEC_TIME   함수 호출
 원래 (행마다 두 함수)               1.74 초        1.12 초        2,000,000
 함수에 PRAGMA UDF                   0.95 초        0.36 초        2,000,000
 먼저 묶고 부르기 (10,234그룹)        0.62 초        0.01 초           20,468
 SQL 식으로 옮김                     0.57 초           0                   0

모범 답안 서술

두 리포트 모두 ORD를 FULL로 한 번 읽으므로 Buffers는 22,422로 같다. 차이는 블록이 아니라 함수 호출이다. GROUP BY FN_DOW_NM(ORD_DT), FN_HOUR_BAND(ORD_HMS)는 묶기 전에 행마다 두 함수를 부르므로 100만 행에 200만 번 호출된다. SQL 엔진이 PL/SQL 함수를 부를 때마다 엔진을 건너가는 컨텍스트 스위칭이 일어나고, 한 번은 작아도(교재 15화 실측 약 0.53µs) 200만 번이면 1초가 넘는다. V$SQL의 PLSQL_EXEC_TIME 1.12초가 그 몫이다. recursive calls에는 아무것도 잡히지 않는다. recursive calls는 PL/SQL이 SQL을 실행할 때 쌓이는데, 이 두 함수 안에는 SQL이 없다. 그래서 Buffers로도 recursive calls로도 보이지 않고, V$SQL의 PLSQL_EXEC_TIME이나 DBMS_HPROF 계층 프로파일로 봐야 한다. 고치는 방법은 두 함수의 규칙을 SQL 식으로 옮기는 것이다. 요일은 TO_CHAR(TO_DATE(…), 'DY', 'NLS_DATE_LANGUAGE=KOREAN'), 시간대는 ORD_HMS 문자열 비교의 CASE로 쓰면 PL/SQL로 건너가지 않는다. ORD_HMS는 여섯 자리 고정이라 문자열 비교가 함수의 시각 비교와 같다. Buffers는 22,422 그대로이고 경과는 1.74 → 0.57초가 된다. 결과 35행은 원래 SQL과 같다(MINUS 양방향 0행). DETERMINISTIC을 붙이거나 스칼라 서브쿼리로 감싸는 안은 여기서 거의 듣지 않는다. 두 함수는 인자만으로 답이 정해지므로 DETERMINISTIC이 거짓 약속은 아니다. 하지만 캐시의 열쇠는 입력 값이고, 날짜는 731종, 시각(ORD_HMS)은 5만 종이 넘어 캐시 칸에 다 들지 못한다. SQL 식으로 옮긴 판의 대가는 규칙이 함수와 SQL 두 곳에 생긴다는 것이다. 함수를 하나뿐인 규칙으로 남겨야 한다면 PRAGMA UDF(0.95초)나, 날짜와 시(時)로 먼저 묶은 1만여 그룹에만 함수를 부르는 방법(0.62초)이 차선이다.

해설

T0(Buffers로 말한다)가 못 보는 일이다. 같은 블록을 읽어도 CPU가 다르면 시간이 다르다. 읽은 블록 수가 같은데 시간이 다르면 V$SQL의 CPU_TIME과 PLSQL_EXEC_TIME부터 본다(교재 07화에서도 스칼라 서브쿼리 캐시를 들여다보는 CPU가 Buffers에 보이지 않았다).
PRAGMA UDF(12c)는 SQL에서 부르는 데 맞춰 함수를 컴파일해 한 번의 비용을 줄인다(교재 15화 실측 0.53 → 0.13 ~ 0.2µs). 횟수는 그대로이고, PL/SQL에서 부를 때는 오히려 느려질 수 있다.
먼저 묶고 부르는 판은 GROUP BY ORD_DT, SUBSTR(ORD_HMS, 1, 2)로 10,234그룹을 만든 뒤 그룹마다 함수를 부르고 다시 묶는다. 규칙은 함수 하나에 남고 호출은 2만 번으로 준다.
ORD_X01이 있어도 이 리포트는 인덱스만으로 처리할 수 없다. 두 컬럼이 NULL 허용이라 인덱스에 없는 행이 있을 수 있기 때문이다. 둘 중 하나라도 NOT NULL이면 INDEX FAST FULL SCAN(리프 3,774블록)으로 블록도 줄일 수 있다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황