결제 대기 화면에 맞춰 인덱스를 만들었는데 FULL로 돌고, 한 번 열 때마다 recursive calls가 8만 번 찍힌다

중급SELECT한빛유통682 / 700

상황

주문관리팀의 「최근 영업일 결제 대기」 화면은 최근 n영업일 동안 들어온 결제 대기 주문을 보여 준다. 담당자들이 하루 300번쯤 열고, 한 번에 0.9초가 걸린다. 지난주 이 화면을 위해 (ORD_ST, ORD_DT) 인덱스를 만들었지만 계획은 그대로다. 기준일은 회사 공통 함수 FN_BIZ_DAY_FROM이 영업일 달력을 읽어 구한다. 개발자는 "함수는 오른쪽 값에만 썼고 컬럼은 가공하지 않았다"라고 한다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT DATE(전부 자정, 2022-01-01 ~ 2024-12-31, 하루 약 912건) · ORD_ST PENDING 8%(80,000행)
행은 주문일과 상관없는 순서로 쌓여 있어서 같은 날의 주문이 서로 다른 블록에 흩어져 있다
인덱스 IX_ORD_BIG_ST_DT (ORD_ST, ORD_DT): 지난주 생성 · 리프 2,950블록 · 통계 최신(히스토그램 없음)
T_BIZ_CAL 영업일 달력(이 문제에서 쓰는 테이블): 1,096행 · 5블록 · CAL_DT DATE · BIZ_YN · PK (CAL_DT) · 함수 안 SQL은 호출마다 3블록
이 문제의 오늘은 2024-12-31(화)이고, 최근 3영업일의 시작일은 2024-12-27(금)이다

원본 SQL

sql

CREATE OR REPLACE FUNCTION FN_BIZ_DAY_FROM(p_days NUMBER) RETURN DATE IS
  v_dt DATE;
BEGIN
  SELECT MIN(CAL_DT) INTO v_dt
    FROM (SELECT CAL_DT
            FROM T_BIZ_CAL
           WHERE CAL_DT <= TRUNC(SYSDATE)
             AND BIZ_YN = 'Y'
           ORDER BY CAL_DT DESC)
   WHERE ROWNUM <= p_days;
  RETURN v_dt;
END;
/

-- :days = 3
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_ST = 'PENDING'
   AND ORD_DT >= FN_BIZ_DAY_FROM(:days)
 ORDER BY ORD_DT DESC, ORD_SEQ DESC;

실행계획 학습용 예시

xplan

[DISPLAY_CURSOR (ALLSTATS LAST) · :days = 3]
-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |    367 |00:00:00.91 |    5826 |          |
|   1 |  SORT ORDER BY     |           |      1 |   4000 |    367 |00:00:00.91 |    5826 | 38912  (0)|
|*  2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |   4000 |    367 |00:00:00.91 |    5826 |          |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("ORD_ST"='PENDING' AND "ORD_DT">="FN_BIZ_DAY_FROM"(:DAYS)))

[세션 통계: 화면 한 번]   recursive calls 80,012 · session logical reads 245,861
[V$SQL: 함수 안 문장 (dep=1) · 화면 한 번의 증가분]
  SELECT MIN(CAL_DT) FROM (SELECT CAL_DT FROM T_BIZ_CAL WHERE CAL_DT <= TRUNC(SYSDATE) …) WHERE ROWNUM <= :B1
  EXECUTIONS 80,000 · BUFFER_GETS 240,000 (한 번에 3블록)

문제

인덱스가 있는데 FULL로 처리되고 함수 안 SQL이 8만 번 실행된 이유를 실행계획의 E-Rows와 Predicate Information으로 설명하시오.
기준일 규칙(FN_BIZ_DAY_FROM)은 그대로 쓰면서 인덱스 범위 스캔이 되도록 고치시오. 함수를 스칼라 서브쿼리로 감싸는 안과 견주어 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

DECLARE
  v_from DATE;
BEGIN
  -- ① 기준일은 화면을 열 때 한 번만 구한다 (2024-12-27)
  v_from := FN_BIZ_DAY_FROM(:days);

  -- ② 구한 날짜를 넘긴다: 정적 SQL 의 PL/SQL 변수는 바인드 변수(:B1)가 되고, 옵티마이저가 값을 엿본다
  OPEN :rc FOR
    SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
      FROM T_ORD_BIG
     WHERE ORD_ST = 'PENDING'
       AND ORD_DT >= v_from
     ORDER BY ORD_DT DESC, ORD_SEQ DESC;
END;

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

xplan

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |      1 |        |    367 |00:00:00.01 |     372 |          |
|   1 |  SORT ORDER BY                |                  |      1 |    365 |    367 |00:00:00.01 |     372 | 38912  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID | T_ORD_BIG        |      1 |    365 |    367 |00:00:00.01 |     372 |          |
|*  3 |    INDEX RANGE SCAN           | IX_ORD_BIG_ST_DT |      1 |    365 |    367 |00:00:00.01 |       5 |          |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ORD_ST"='PENDING' AND "ORD_DT">=:B1)

[세션 통계]  recursive calls 3 (기준일 한 번) · session logical reads 375

[세 판 비교 · 같은 화면 한 번]
 판                                      함수 호출   E-Rows   계획                Buffers(문장)   함수 안 SQL   경과
 원래 (WHERE에 함수)                       80,000     4,000   FULL                     5,826       240,000   0.91 초
 스칼라 서브쿼리 (SELECT … FROM dual)            1     4,000   FULL                     5,826             3   0.09 초
 먼저 구해서 바인드                              1       365   INDEX RANGE SCAN           372             3   0.01 초

모범 답안 서술

Predicate Information 2번 줄은 "ORD_DT">="FN_BIZ_DAY_FROM"(:DAYS)다. 컬럼은 가공되지 않았지만, 옵티마이저는 파싱할 때 이 함수가 어떤 날짜를 돌려줄지 알 수 없다. 값을 모르는 범위 조건에는 기본 선택도 5%를 쓰므로 PENDING 80,000행의 5%인 E-Rows 4,000이 나왔다. 행이 주문일과 상관없이 흩어져 있어서 4,000행을 인덱스로 찾아가면 4,000번 가까이 서로 다른 블록을 읽어야 하고, 옵티마이저는 FULL(5,826블록을 멀티블록으로 읽는다)이 싸다고 판단했다. 실제로 남는 행은 367행이다. FULL이 되자 함수는 filter에서 행마다 평가된다. 옵티마이저는 비싼 사용자 함수 조건을 뒤에 두므로 ORD_ST = 'PENDING'을 통과한 80,000행마다 함수가 호출되었고, 그때마다 함수 안 SELECT가 재귀 SQL로 돌았다(recursive calls 80,012, 함수 안 SQL 240,000블록). 화면 한 번의 논리 읽기 245,861은 5,826 + 240,000에 가깝다. 같은 기준일을 8만 번 다시 구한 것이다. 기준일은 화면을 열 때 한 번 구하면 되는 값이다. 먼저 FN_BIZ_DAY_FROM을 한 번 불러 변수에 담고, 그 날짜를 바인드 변수로 넘긴다. PL/SQL이면 정적 SQL 안의 변수가 바인드 변수가 되고, 옵티마이저는 첫 실행 때 값을 엿봐 '최근 5일'임을 안다. E-Rows가 365로 맞아 IX_ORD_BIG_ST_DT를 범위 스캔하고, 논리 읽기는 245,861 → 375, 경과는 0.91 → 0.01초가 된다. 기준일 규칙은 함수 하나에 그대로 남는다. 함수를 스칼라 서브쿼리 (SELECT FN_BIZ_DAY_FROM(:days) FROM dual)로 감싸도 E-Rows는 4,000 그대로라 FULL 5,826블록이 남는다. 서브쿼리는 한 번만 실행되어 함수 호출은 80,000 → 1로 줄지만(0.09초), 파싱할 때 값을 모르는 것은 같기 때문이다. 호출 문제는 풀어도 접근 경로는 풀지 못한다. DETERMINISTIC이나 결과 캐시도 답이 아니다. 이 함수는 T_BIZ_CAL과 SYSDATE를 읽으므로 DETERMINISTIC은 거짓 약속이고, 결과 캐시는 시계라는 숨은 입력 때문에 날짜가 바뀌어도 옛 기준일을 돌려준다(PLSQL-002). 둘 다 E-Rows도 바꾸지 못한다.

해설

"오른쪽에만 썼다"의 함정: 컬럼을 가공하지 않으면 인덱스를 쓸 수는 있다. 하지만 값을 모르는 함수는 E-Rows를 기본값으로 만들고, 그 추정이 계획을 정한다. 인덱스가 골라지면 함수는 범위의 시작점을 구할 때만 불리지만, FULL이 골라지면 filter에서 행마다 불린다.
조건 평가 순서: 함수가 100만 번이 아니라 8만 번 불린 것은 옵티마이저가 싼 조건(ORD_ST = 'PENDING')을 먼저 평가했기 때문이다. 조건이 함수 하나뿐이었다면 100만 번이었다.
바인드 피킹의 대가: 첫 실행 값으로 계획이 정해진다. :days가 3 ~ 5인 화면이라 늘 같은 계획이 맞지만, 같은 SQL을 '최근 200영업일'에도 쓰면 첫 값에 따라 계획이 달라질 수 있다(교재 10화, 11화).
화면이 Java라면 기준일을 구하는 호출을 한 번 더 보내거나, 목록 SQL을 위처럼 PL/SQL 블록으로 감싸 한 번에 보낸다. 왕복이 한 번 늘어도 8만 번의 재귀 SQL보다 훨씬 싸다. 기준일을 리터럴로 박으면 날마다 다른 문장이 된다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황