같은 SQL을 같은 순서로 스크립트에서 돌리면 4초, 프로시저로 돌리면 21초 걸린다

실전PLSQL한빛유통569 / 700

상황

연말에 1년치 「일별 등급별 매출」을 다시 만드는 재집계 프로시저 P_REAGG_GRADE_DAY는 365일과 등급 셋(A, B, C)을 돌며 하루치 한 등급을 집계해 요약 테이블에 넣는다(1,095번). 오과장이 같은 SELECT를 같은 순서로 SQL*Plus 스크립트에 풀어 돌려 보니 4.3초였는데, 프로시저는 21초가 걸렸다. "프로시저가 뭘 더 하는 것도 아닌데요." 박팀장은 루프를 그대로 둘지, 등급마다 문장을 나눌지, 한 문장으로 바꿀지 비교해서 정해 달라고 했다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 하루 약 913건 · 행은 ORD_DT 순서와 상관없이 흩어져 있다 · 2023년 약 333,000건
M_CUS: 2,000행 · 30블록 · CUS_GD A 20명(1%) · B 180명(9%) · C 1,800명(90%) · FREQUENCY 히스토그램
인덱스 IX_ORD_BIG_DT (ORD_DT) · IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT)(이 문제용) · PK_M_CUS (CUS_ID)
T_GRADE_DAY_SUM(요약): 등급과 날짜마다 한 행 · 인덱스 없음 · statistics_level = TYPICAL · session_cached_cursors = 50

원본 SQL

sql

PROCEDURE P_REAGG_GRADE_DAY(p_year IN NUMBER) IS
  v_from DATE := TO_DATE(p_year || '0101', 'YYYYMMDD');
  v_dt   DATE;
  v_cnt  NUMBER;
  v_amt  NUMBER;
BEGIN
  DELETE FROM T_GRADE_DAY_SUM WHERE SUM_DT >= v_from AND SUM_DT < ADD_MONTHS(v_from, 12);
  FOR d IN 0 .. 364 LOOP
    v_dt := v_from + d;
    FOR g IN (SELECT 'A' AS gd FROM dual UNION ALL SELECT 'B' FROM dual UNION ALL SELECT 'C' FROM dual) LOOP
      SELECT COUNT(*), NVL(SUM(o.ORD_AMT), 0)
        INTO v_cnt, v_amt
        FROM M_CUS c, T_ORD_BIG o
       WHERE c.CUS_GD = g.gd
         AND o.CUS_ID = c.CUS_ID
         AND o.ORD_DT = v_dt;
      INSERT INTO T_GRADE_DAY_SUM (SUM_DT, CUS_GD, ORD_CNT, ORD_AMT)
      VALUES (v_dt, g.gd, v_cnt, v_amt);
    END LOOP;
  END LOOP;
  COMMIT;
END;
-- 호출 : EXEC P_REAGG_GRADE_DAY(2023)

실행계획 학습용 예시 · 실행 방식에 따른 ACS 차이는 교재 11화 실측과 같은 방향

xplan

[프로시저 한 번 뒤 · 집계 SELECT · V$SQL]
 CHILD  PARSE_CALLS  EXECUTIONS  BUFFER_GETS  IS_BIND_SENSITIVE  IS_BIND_AWARE  V$SQL_CS_HISTOGRAM
     0            1       1,095    2,556,095  Y                  N              0:730  1:365  2:0

[SQL*Plus로 풀어 돌린 것 · 같은 SELECT 텍스트 · 같은 순서]
 CHILD  PARSE_CALLS  EXECUTIONS  BUFFER_GETS  IS_BIND_AWARE  계획           V$SQL_SHARED_CURSOR
     0            3           3        7,003  N              NESTED LOOPS   —
     1          364         364       36,036  Y              NESTED LOOPS   BIND_EQUIV_FAILURE (A)
     2          364         364      237,328  Y              NESTED LOOPS   BIND_EQUIV_FAILURE (B)
     3          364         364      345,800  Y              HASH JOIN      BIND_EQUIV_FAILURE (C)
 합계 626,167블록 · 4.3초   (프로시저 2,556,095블록 · 21초)

[프로시저 안 · C 등급 하루 한 번 · 'ALLSTATS LAST +PEEKED_BINDS']
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |      1 |        |      1 |00:00:00.02 |    6252 |
|   1 |  SORT AGGREGATE               |                   |      1 |      1 |      1 |00:00:00.02 |    6252 |
|   2 |   NESTED LOOPS                |                   |      1 |      9 |    822 |00:00:00.02 |    6252 |
|   3 |    NESTED LOOPS               |                   |      1 |      9 |    822 |00:00:00.01 |    5430 |
|*  4 |     TABLE ACCESS FULL         | M_CUS             |      1 |     20 |   1800 |00:00:00.01 |      30 |
|*  5 |     INDEX RANGE SCAN          | IX_ORD_BIG_CUS_DT |   1800 |      1 |    822 |00:00:00.01 |    5400 |
|   6 |    TABLE ACCESS BY INDEX ROWID| T_ORD_BIG         |    822 |      1 |    822 |00:00:00.01 |     822 |
-----------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
   1 - :B2 (VARCHAR2(32), CSID=873): 'A'
   2 - :B1 (DATE): 01/01/2023 00:00:00

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("C"."CUS_GD"=:B2)
   5 - access("O"."CUS_ID"="C"."CUS_ID" AND "O"."ORD_DT"=:B1)

[SQL*Plus의 자식 3 · C 등급 하루 한 번]
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |      1 |        |      1 |00:00:00.01 |     950 |
|   1 |  SORT AGGREGATE               |                   |      1 |      1 |      1 |00:00:00.01 |     950 |
|*  2 |   HASH JOIN                   |                   |      1 |    822 |    822 |00:00:00.01 |     950 |
|*  3 |    TABLE ACCESS FULL          | M_CUS             |      1 |   1800 |   1800 |00:00:00.01 |      30 |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG |      1 |    913 |    913 |00:00:00.01 |     920 |
|*  5 |     INDEX RANGE SCAN          | IX_ORD_BIG_DT     |      1 |    913 |    913 |00:00:00.01 |       5 |
-----------------------------------------------------------------------------------------------------------

문제

같은 SELECT, 같은 순서인데 SQL*Plus에서는 C 등급이 해시 조인으로 바뀌고 프로시저에서는 끝까지 NL로 남은 이유를 위 자료로 설명하시오.
루프를 그대로 두는 안, 등급마다 문장을 나누는 안, 한 문장으로 바꾸는 안을 일량과 부작용으로 비교하시오.
고른 안으로 프로시저를 고치시오. 결과(날짜와 등급마다 한 행, 주문이 없으면 0)가 바뀌지 않게 하시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE P_REAGG_GRADE_DAY(p_year IN NUMBER) IS
  v_from DATE := TO_DATE(p_year || '0101', 'YYYYMMDD');
BEGIN
  DELETE FROM T_GRADE_DAY_SUM WHERE SUM_DT >= v_from AND SUM_DT < ADD_MONTHS(v_from, 12);

  -- 1,095 번의 집계 → 한 문장 : T_ORD_BIG 을 한 번 읽고 날짜와 등급으로 묶는다
  -- 주문이 없는 날과 등급도 0 으로 남기려고 날짜 365 × 등급 3 의 틀에 바깥 조인한다
  INSERT INTO T_GRADE_DAY_SUM (SUM_DT, CUS_GD, ORD_CNT, ORD_AMT)
  WITH days AS (SELECT v_from + LEVEL - 1 AS SUM_DT FROM dual CONNECT BY LEVEL <= 365),
       grades AS (SELECT 'A' AS CUS_GD FROM dual UNION ALL SELECT 'B' FROM dual UNION ALL SELECT 'C' FROM dual),
       s AS (SELECT o.ORD_DT, c.CUS_GD, COUNT(*) AS ORD_CNT, SUM(o.ORD_AMT) AS ORD_AMT
               FROM T_ORD_BIG o, M_CUS c
              WHERE o.CUS_ID = c.CUS_ID
                AND o.ORD_DT >= v_from
                AND o.ORD_DT <  v_from + 365
              GROUP BY o.ORD_DT, c.CUS_GD)
  SELECT d.SUM_DT, g.CUS_GD, NVL(s.ORD_CNT, 0), NVL(s.ORD_AMT, 0)
    FROM days d
   CROSS JOIN grades g
    LEFT JOIN s ON s.ORD_DT = d.SUM_DT AND s.CUS_GD = g.CUS_GD;
  COMMIT;
END;

개선 후 실행계획 학습용 예시 · 실행 방식에 따른 ACS 차이는 교재 11화 실측과 같은 방향

xplan

[고친 프로시저 한 번 (2023) · 세션 통계]
  경과 시간                    0.42 초      (21 초)
  session logical reads        5,947        (2,556,095)
  집계 SQL 실행 횟수           1            (1,095)
  계획  T_ORD_BIG FULL(5,826) + M_CUS FULL(30) → HASH JOIN → HASH GROUP BY → 바깥 조인(1,095행)
  T_GRADE_DAY_SUM              1,095행 · 원본 프로시저 결과와 MINUS 양방향 0건

모범 답안 서술

원인: 프로시저의 집계 SELECT는 PARSE_CALLS 1, 실행 1,095번이다. PL/SQL은 정적 SQL의 커서를 세션에 캐시해 두고 열어 둔 채 다시 실행하므로 파스 호출이 한 번뿐이다. 첫 실행(2023-01-01, A 등급)에서 'A'를 엿보고(Peeked Binds) "고객 20명이니 고객마다 인덱스로 찾아가자"는 NL 계획을 세웠다. 그 뒤로 C 등급도 같은 계획으로 고객 1,800명을 하나씩 찾아가서 하루에 6,252블록을 읽는다(5번 줄 Starts 1,800). CUS_GD에 히스토그램이 있어 문장은 IS_BIND_SENSITIVE = Y이고, 일량도 0번 칸(A와 B)과 1번 칸(C)으로 섞였다(0:730 1:365). 그런데 ACS가 자식을 다시 고르는 때는 파스할 때다. 파스 호출이 없으니 IS_BIND_AWARE는 끝까지 N이고 자식도 하나다. SQL*Plus로 풀어 돌린 스크립트는 실행마다 파스 호출을 했다. 첫날 A, B, C 세 번에서 칸이 섞이자 다음 파스부터 bind aware가 되어 등급마다 자식을 새로 만들었다(BIND_EQUIV_FAILURE). C 등급의 자식 3은 'C'를 엿보고 해시 조인을 골라 하루 950블록이다. 같은 SQL, 같은 순서인데 2,556,095블록과 626,167블록, 21초와 4.3초로 갈린 까닭이 이것이다. 세 안의 비교: ① 루프를 그대로 두면 2,556,095블록이다. 커서 캐시를 꺼서 파스를 강제하는 방법은 다른 문장의 파스 비용까지 늘리고, ACS가 고쳐도 첫날의 나쁜 실행은 남는다. ② 등급마다 CUS_GD = 'A', 'B', 'C' 상수 문장을 두면 문장 셋이 각자 맞는 계획(A, B는 NL, C는 해시)을 탄다. 약 62만 블록(626,167에서 첫날 몫만 빠진 수준)으로 준다. 하지만 실행은 여전히 1,095번이고, 날마다 T_ORD_BIG의 하루치를 세 번씩 다시 읽는다. ③ 한 문장으로 바꾸면 T_ORD_BIG을 한 번 읽고(FULL 5,826) M_CUS와 해시 조인한 뒤 날짜와 등급으로 묶는다. 약 5,900블록, 0.42초다. 실행이 1,095번 → 1번이 되어 피킹할 값도 등급 하나에 묶이지 않는다. 고른 안: ③을 고른다. 다만 원본은 주문이 없는 날이나 등급에도 0인 행을 넣었다. GROUP BY만 쓰면 그런 행이 빠지므로, 날짜 365개와 등급 셋의 틀에 바깥 조인(LEFT JOIN)해서 0으로 채운다. 한 문장이라 실패하면 통째로 롤백되고, 언두는 요약 1,095행 몫이라 작다. 고친 뒤 결과를 원본 프로시저의 결과와 MINUS 양방향으로 맞춰 0건을 확인한다.

해설

교재 11화 실습/02 [E]가 같은 현상을 쟀다. 100건 화면을 PL/SQL에서 커서를 열어 둔 채 다시 실행하자 나쁜 실행(8,333블록)이 다섯 번 되풀이되었고 파스 호출은 1번이었다. SQL*Plus에서는 한 번 뒤에 고쳐졌다.
커서 캐시는 파스 비용을 줄이는 이득이 크다(PARSE s3). 그 대가로 ACS를 잃는다는 것을 알고, 값마다 계획이 달라야 하는 문장은 PL/SQL 안에서도 상수 문장이나 한 문장으로 미리 갈라 둔다.
EXECUTE IMMEDIATE로 바꾸면 실행마다 파스 호출(소프트 파싱)이 생겨 ACS가 움직일 수는 있다. 그러나 나쁜 실행을 한 번 겪은 뒤에야 고쳐지고, 1,095번의 소프트 파싱 비용이 더해진다.
히스토그램을 지워 등급 셋의 E-Rows를 같게(1/3) 만들면 계획이 해시로 고정되어 C는 낫지만 A가 하루 99 → 950블록이 된다. 값마다 계획이 다른 것은 데이터의 성질이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 실행 방식에 따른 ACS 차이는 교재 11화 실측과 같은 방향 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 실행 방식에 따른 ACS 차이는 교재 11화 실측과 같은 방향 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황