야간 클레임 정산 INSERT에 지난주 베이스라인을 올렸는데 한 번도 쓰이지 않고 밤마다 계획이 오락가락한다

실전INSERT한빛유통650 / 700

상황

정산팀 야간 배치 「클레임 취소 정산」은 01:20에 클레임 시스템에서 받은 고객 목록을 W_CLM_CUS에 싣고 바로 통계를 모은 뒤, 01:30에 그 고객들의 취소 주문을 T_CLM_STLM에 넣는다. 배치 로그를 보면 이 INSERT가 어떤 밤은 0.12초, 어떤 밤은 0.38초다. 지난주 오과장이 빨랐던 10월 3일의 계획을 AWR에서 꺼내 베이스라인으로 올렸지만, 그 뒤로도 느린 밤이 그대로 나온다. 배치 프레임워크는 장애 때 어느 실행의 SQL인지 찾으려고 모든 SQL 앞에 추적 주석을 붙여 보낸다(2024년 2월에 넣었다). 박팀장은 고칠 방법을 견줘 달라고 했다.

테이블과 인덱스

W_CLM_CUS(작업 테이블 · M_CUS에서 파생): 매일 밤 비우고 다시 싣는다 · 40 ~ 300명 · 1 ~ 2블록 · 적재 직후 통계 수집
클레임 고객에는 취소 고객 40명(고객당 500건, 전부 CANCEL)이 언제나 모두 들어 있고, 나머지는 취소 주문이 없는 고객이다
T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_ST에 FREQUENCY 히스토그램(CANCEL 2%) · IX_ORD_BIG_CUS_ST (CUS_ID, ORD_ST)
T_CLM_STLM(정산 클레임 · T_ORD_STLM과 같은 꼴): 인덱스 없음 · 날마다 2만 건 안팎이 쌓인다

원본 SQL

sql

-- 배치 프레임워크의 SQL 실행부 (모든 단계가 이 프로시저로 SQL 을 보낸다)
PROCEDURE RUN_STEP(p_job VARCHAR2, p_step VARCHAR2, p_body VARCHAR2) IS
  v_sql VARCHAR2(32767);
BEGIN
  v_sql := '/* JOB=' || p_job || ' STEP=' || p_step
        || ' RUN=' || TO_CHAR(SYSDATE, 'YYYYMMDD-HH24MISS') || ' */ ' || p_body;
  EXECUTE IMMEDIATE v_sql;
END;

-- 단계 02 의 본문 (p_body)
INSERT INTO T_CLM_STLM (CLM_DT, CUS_ID, ORD_SEQ, ORD_DT, ORD_AMT)
SELECT TRUNC(SYSDATE), o.CUS_ID, o.ORD_SEQ, o.ORD_DT, o.ORD_AMT
  FROM W_CLM_CUS w, T_ORD_BIG o
 WHERE o.CUS_ID = w.CUS_ID
   AND o.ORD_ST = 'CANCEL'

실행계획 학습용 예시

xplan

[DBA_HIST_SQLSTAT · 단계 02 INSERT · 밤마다]
 날짜   SQL_ID          EXACT_MATCHING_SIGNATURE  PLAN_HASH_VALUE  클레임 고객  BUFFER_GETS  ELAPSED
 10-03  1a7k2c9x0m4qd   15226180973381201537         4190263358          300        6,479   0.12초
 10-04  9cd3v8n1q7w2s    8703361925470018214         2385510647           40       20,813   0.38초
 10-05  3mq0x6k2c8v1b   11902485617730264401         4190263358          180        6,477   0.12초
 10-06  6tz8d1w4k9c0n    2648100733152846627         2385510647           40       20,813   0.38초
 · 밤마다 SQL_ID와 서명이 다르다. V$SQL의 SQL_TEXT 앞부분: /* JOB=CLM_STLM STEP=02 RUN=20251006-013000 */ INSERT INTO T_CLM_STLM …

[DBA_SQL_PLAN_BASELINES]  10-07 09:10 오과장이 LOAD_PLANS_FROM_AWR로 올림 · SQL_ID 1a7k2c9x0m4qd · PLAN_HASH_VALUE 4190263358
 SIGNATURE 15226180973381201537 · ENABLED YES · ACCEPTED YES · LAST_EXECUTED (없음)

[느린 밤 · 10-06 · 클레임 고객 40명]
Plan hash value: 2385510647

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                     |                   |      1 |        |      0 |00:00:00.38 |   20813 |
|   1 |  LOAD TABLE CONVENTIONAL             | T_CLM_STLM        |      1 |        |      0 |00:00:00.38 |   20813 |
|   2 |   NESTED LOOPS                       |                   |      1 |    400 |  20000 |00:00:00.29 |   20163 |
|   3 |    TABLE ACCESS FULL                 | W_CLM_CUS         |      1 |     40 |     40 |00:00:00.01 |       3 |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG        |     40 |     10 |  20000 |00:00:00.27 |   20160 |
|*  5 |     INDEX RANGE SCAN                 | IX_ORD_BIG_CUS_ST |     40 |     10 |  20000 |00:00:00.01 |     160 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("O"."CUS_ID"="W"."CUS_ID" AND "O"."ORD_ST"='CANCEL')

[빠른 밤 · 10-03 · 클레임 고객 300명]
Plan hash value: 4190263358

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |            |      1 |        |      0 |00:00:00.12 |    6479 |          |
|   1 |  LOAD TABLE CONVENTIONAL | T_CLM_STLM |      1 |        |      0 |00:00:00.12 |    6479 |          |
|*  2 |   HASH JOIN              |            |      1 |   3000 |  20000 |00:00:00.09 |    5829 | 1491K (0)|
|   3 |    TABLE ACCESS FULL     | W_CLM_CUS  |      1 |    300 |    300 |00:00:00.01 |       3 |          |
|*  4 |    TABLE ACCESS FULL     | T_ORD_BIG  |      1 |  20000 |  20000 |00:00:00.08 |    5826 |          |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."CUS_ID"="W"."CUS_ID")
   4 - filter("O"."ORD_ST"='CANCEL')

문제

10월 7일에 올린 베이스라인이 한 번도 쓰이지 않은 이유를 위 자료로 설명하시오. 클레임 고객이 40명인 밤에만 NL을 고르는 이유도 쓰시오.
네 가지 안(추적 주석을 세션 정보로 옮기고 베이스라인을 다시 올린다, 힌트로 조인 방법을 정한다, force_match 프로파일을 쓴다, 통계로 추정을 고친다)을 비교하시오.
고른 안으로 프레임워크와 단계 02를 고쳐 쓰시오. 그 대가와 대장에 남길 것도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 프레임워크 : 추적 정보는 SQL 글자가 아니라 세션 정보로 남긴다 (V$SESSION · ASH · AWR 의 MODULE / ACTION)
PROCEDURE RUN_STEP(p_job VARCHAR2, p_step VARCHAR2, p_body VARCHAR2) IS
BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE(module_name => p_job, action_name => p_step);
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO('RUN=' || TO_CHAR(SYSDATE, 'YYYYMMDD-HH24MISS'));
  EXECUTE IMMEDIATE p_body;                    -- 밤마다 같은 글자
  DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
END;

-- ② 단계 02 : 클레임 고객 수와 상관없이 해시 조인 (힌트 대장 등록 · 아래 전제가 깨지면 다시 본다)
INSERT INTO T_CLM_STLM (CLM_DT, CUS_ID, ORD_SEQ, ORD_DT, ORD_AMT)
SELECT /*+ LEADING(w o) USE_HASH(o) FULL(o) */
       TRUNC(SYSDATE), o.CUS_ID, o.ORD_SEQ, o.ORD_DT, o.ORD_AMT
  FROM W_CLM_CUS w, T_ORD_BIG o
 WHERE o.CUS_ID = w.CUS_ID
   AND o.ORD_ST = 'CANCEL';

-- ③ 10월 7일에 올린 베이스라인은 다시는 쓰이지 않으므로 지운다
-- DECLARE n PLS_INTEGER; BEGIN n := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => '&old_handle'); END;

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

xplan

Plan hash value: 2817406650

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |            |      1 |        |      0 |00:00:00.12 |    6479 |          |
|   1 |  LOAD TABLE CONVENTIONAL | T_CLM_STLM |      1 |        |      0 |00:00:00.12 |    6479 |          |
|*  2 |   HASH JOIN              |            |      1 |    400 |  20000 |00:00:00.09 |    5829 | 1491K (0)|
|   3 |    TABLE ACCESS FULL     | W_CLM_CUS  |      1 |     40 |     40 |00:00:00.01 |       3 |          |
|*  4 |    TABLE ACCESS FULL     | T_ORD_BIG  |      1 |  20000 |  20000 |00:00:00.08 |    5826 |          |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."CUS_ID"="W"."CUS_ID")
   4 - filter("O"."ORD_ST"='CANCEL')

모범 답안 서술

베이스라인이 쓰이지 않은 이유: SQL plan baseline은 정규화한 SQL 글자의 서명으로 문장을 알아본다. 프레임워크가 모든 SQL 앞에 실행 시각이 든 주석(RUN=20251006-013000)을 붙이므로 밤마다 글자가 다르고, SQL_ID도 EXACT_MATCHING_SIGNATURE도 밤마다 다르다. 10월 7일에 올린 베이스라인은 10월 3일 글자의 서명(15226180973381201537)에 붙어 있어서, 그 뒤의 어떤 밤의 문장도 알아보지 못했다. LAST_EXECUTED가 비어 있고 느린 밤의 계획에 Note가 없는 것이 그 증거다. 40명인 밤에만 NL인 이유: 옵티마이저는 클레임 고객 수와 T_ORD_BIG의 취소 비율을 따로 셈해 곱한다. 40명이면 40 × 500 × 2% = 400건으로 보고, 400건이면 고객마다 IX_ORD_BIG_CUS_ST로 찾아가는 NL이 싸다고 골랐다. 그러나 클레임 고객에는 취소 고객 40명이 언제나 모두 들어 있으므로 실제는 2만 건이다. NL은 흩어진 2만 행을 하나씩 읽어 20,813블록이다. 300명인 밤에는 300 × 500 × 2% = 3,000건으로 보아 해시 조인을 골랐고, 실제는 같은 2만 건이라 6,479블록이다. 일은 같은데 고객 수가 추정만 바꿔 계획이 오락가락했다. 두 테이블 사이의 상관이라 컬럼 그룹이나 히스토그램으로는 고칠 수 없다. 네 가지 안: ① 주석을 세션 정보로 옮기고 베이스라인을 다시 올리면 SPM이 제대로 쓰인다. 다만 글자가 바뀌므로 새 글자에 해시 계획이 한 번 나와야 올릴 수 있고(300명인 밤을 기다리거나 시험 DB에서 만들어 옮긴다), 그 전까지는 40명인 밤에 NL이 나온다. ② 힌트로 조인 방법을 정하면 첫날부터 확실하다. 배치 코드는 어차피 고쳐 배포하므로 계획 의도를 코드에 남길 수 있다. 대신 힌트는 검사 없이 그대로 실행되므로 유효기간을 대장에 적는다. ③ force_match 프로파일은 리터럴을 바인드처럼 바꿔 묶을 뿐 주석은 리터럴이 아니라 묶이지 않는다. SQL 패치도 SQL_ID에 붙는데 SQL_ID가 밤마다 바뀐다. ④ 통계로는 고칠 수 없다. 두 테이블 사이의 상관이다. 고른 안: 주석은 ①처럼 세션 정보로 옮기고, 조인 방법은 ②의 힌트로 정한다. 프레임워크는 DBMS_APPLICATION_INFO.SET_MODULE과 SET_CLIENT_INFO로 작업, 단계, 실행 시각을 세션에 남기고 SQL은 글자 그대로 보낸다. 단계 02에는 LEADING(w o) USE_HASH(o) FULL(o)를 넣는다. 40명인 밤도 20,813 → 6,479블록이다. 대가와 대장: 프레임워크를 고치면 모든 단계의 SQL 글자가 바뀌므로 다른 단계의 SQL에 걸려 있던 베이스라인, 프로파일, 패치가 있다면 모두 다시 확인한다. 대신 글자가 고정되면 AWR의 SQL 이력이 SQL_ID 하나로 이어져 밤마다 견줄 수 있고, 추적 정보는 MODULE과 ACTION으로 ASH와 V$SESSION에 그대로 남는다. 힌트 대장에는 그날의 규모(클레임 고객 40 ~ 300명, 취소 주문 2만 건)와 전제(클레임 고객이 취소 고객을 모두 포함한다), 재검토 조건(취소 주문이 10만 건을 넘거나 T_ORD_BIG에 파티션이 생기면 다시 잰다)을 적는다. 10월 7일의 베이스라인은 지운다.

해설

SQL 글자를 바꾸는 것은 모두 계획 안정성 장치를 끊는다. 베이스라인, 패치, (force_match가 아닌) 프로파일은 글자에 붙고, AWR 이력과 커서 공유도 글자를 따른다. 추적 정보는 글자가 아니라 MODULE, ACTION, CLIENT_INFO에 둔다.
힌트를 고른 이유를 적는다. 18화의 방침은 '고르는 일은 옵티마이저, 지키는 일은 베이스라인'이다. 이 단계는 옵티마이저가 두 테이블 사이의 상관을 알 방법이 없어서 고르는 일부터 틀린다. 그래서 코드를 고치는 김에 계획 의도를 코드에 남겼다. 베이스라인을 고른다면 새 글자로 해시 계획이 나온 날 LOAD_PLANS_FROM_CURSOR_CACHE로 올린다.
같은 SQL_ID로 묶이지 않는 이력은 감시도 못 한다. 리터럴 SQL은 FORCE_MATCHING_SIGNATURE로 묶을 수 있지만(OPT-052), 주석이 다른 문장은 그것으로도 묶이지 않는다.
이 INSERT는 하룻밤에 한 번만 돈다. 같은 커서를 두 번 쓰지 않으니 Statistics Feedback의 덕도 보지 못한다(OPT-053).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황