보정 6,000건을 고치는 주간 배치가 행 수에 비해 CPU를 너무 많이 쓴다

중급PLSQL한빛유통555 / 700

상황

한빛유통은 정산을 월별 보관 테이블(T_ORD_STLM_202201 ~ T_ORD_STLM_202412)에 나눠 둔다. 주간 보정 배치는 보정 요청을 읽어 해당 월 테이블의 정산 금액을 고친다. 월이 테이블 이름에 들어가므로 동적 SQL을 쓴다. 이번 주 보정 6,000건에 9.1초가 걸렸고 그동안 CPU 하나를 거의 다 썼다. 오과장이 물었다. "한 건에 한 행인데, 1초도 안 걸려야 정상 아닌가요?"

테이블과 인덱스

T_ORD_STLM_YYYYMM 월별 정산 보관 테이블: T_ORD_STLM을 월마다 나눈 것 · 2022년 1월 ~ 2024년 12월 36개 · 각 약 2,800행 · 20블록 · PK (STLM_SEQ)
T_STLM_ADJ 보정 요청: STLM_YM VARCHAR2(6) · STLM_SEQ · ADJ_AMT(음수도 있다) · PROC_YN · 이번 주 미처리 6,000건이 14개월에 걸쳐 있다.
CURSOR_SHARING = EXACT

원본 SQL

sql

PROCEDURE P_STLM_ADJ IS
BEGIN
  FOR r IN (SELECT STLM_YM, STLM_SEQ, ADJ_AMT
              FROM T_STLM_ADJ
             WHERE PROC_YN = 'N') LOOP
    EXECUTE IMMEDIATE 'UPDATE T_ORD_STLM_' || r.STLM_YM
                   || ' SET STLM_AMT = STLM_AMT + ' || r.ADJ_AMT
                   || ' WHERE STLM_SEQ = ' || r.STLM_SEQ;
  END LOOP;
  UPDATE T_STLM_ADJ SET PROC_YN = 'Y' WHERE PROC_YN = 'N';
  COMMIT;
END;

실행계획 학습용 예시 · 파싱 비율은 교재 11화 실측과 같은 수준

xplan

[10046 → TKPROF · P_STLM_ADJ 한 번 (보정 6,000건)]
UPDATE T_ORD_STLM_202406 SET STLM_AMT = STLM_AMT + -1200 WHERE STLM_SEQ = 61873

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          2          1           1
Misses in library cache during parse: 1

… (같은 모양의 표가 6,000개)

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     6031      7.38       7.62          0          0          0           0
Execute   6040      0.71       0.79          0      12410      12380       12000
Fetch       61      0.03       0.03          0        190          0        6000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    12132      8.12       8.44          0      12600      12380       18000

Misses in library cache during parse: 6019

[세션 통계]  경과 9.1 초 · parse count (hard) 6,024 · parse time cpu 738 cs · CPU used by this session 812 cs

문제

행 하나씩 6,000번 고치는 일에 9초가 걸린 이유를 TKPROF로 설명하시오.
월별 테이블 구조는 그대로 두고 배치를 고치시오. 동적 SQL에서 이어 붙여도 되는 부분과 바인드 변수로 넘겨야 하는 부분을 가려 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE P_STLM_ADJ IS
  v_tab VARCHAR2(30);
BEGIN
  FOR r IN (SELECT STLM_YM, STLM_SEQ, ADJ_AMT
              FROM T_STLM_ADJ
             WHERE PROC_YN = 'N'
             ORDER BY STLM_YM, STLM_SEQ) LOOP          -- 같은 월끼리 이어서 : 같은 문장이 연달아 실행된다
    -- 이어 붙이는 것은 식별자(월 테이블 이름)뿐이고, 있는 테이블 이름인지 확인한다
    v_tab := DBMS_ASSERT.SQL_OBJECT_NAME('T_ORD_STLM_' || r.STLM_YM);
    -- 값은 바인드 변수로
    EXECUTE IMMEDIATE 'UPDATE ' || v_tab || ' SET STLM_AMT = STLM_AMT + :adj WHERE STLM_SEQ = :seq'
      USING r.ADJ_AMT, r.STLM_SEQ;
  END LOOP;
  UPDATE T_STLM_ADJ SET PROC_YN = 'Y' WHERE PROC_YN = 'N';
  COMMIT;
END;

개선 후 실행계획 학습용 예시 · 파싱 비율은 교재 11화 실측과 같은 수준

xplan

[같은 6,000건 (학습용 예시)]
UPDATE T_ORD_STLM_202406 SET STLM_AMT = STLM_AMT + :adj WHERE STLM_SEQ = :seq

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    431      0.04       0.05          0        862        431         431
Misses in library cache during parse: 1

… (월마다 표 하나 · 14개)

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       27      0.02       0.02          0          0          0           0
Execute   6040      0.52       0.58          0      12410      12380       12000
Fetch       61      0.03       0.03          0        190          0        6000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     6128      0.57       0.63          0      12600      12380       18000

Misses in library cache during parse: 16

[세션 통계]  경과 0.8 초 · parse count (hard) 17 · parse time cpu 2 cs

모범 답안 서술

OVERALL TOTALS를 보면 Parse 줄의 cpu가 7.38초, Execute 줄이 0.71초다. 행을 고치는 일(한 건에 3블록)보다 문장을 파싱하는 일에 열 배 넘는 CPU를 썼다. Misses in library cache during parse: 6019이므로 건마다 하드 파싱을 했다. 금액과 번호까지 문자열로 이어 붙였기 때문에 6,000건이 모두 다른 텍스트, 곧 처음 보는 문장이 됐다. 동적 SQL에서 이어 붙일 수밖에 없는 것은 테이블 이름 같은 식별자뿐이다. 식별자는 바인드 변수로 넘길 수 없지만, 이 배치에서는 월이 14가지라 문장도 14개로 끝난다. 금액과 번호는 USING으로 바인드한다. 이어 붙이는 테이블 이름은 DBMS_ASSERT.SQL_OBJECT_NAME으로 실제로 있는 객체 이름인지 확인해서, 보정 요청에 엉뚱한 문자열이 들어와도 SQL이 바뀌지 않게 한다. 보정 요청은 ORDER BY STLM_YM으로 같은 월끼리 이어서 읽는다. PL/SQL은 바로 앞에 실행한 동적 SQL과 텍스트가 같으면 커서를 다시 쓰므로, 월이 바뀔 때만 파스 호출이 생긴다. 고친 뒤 하드 파싱은 6,019 → 16, Parse cpu는 7.38 → 0.02초, 경과 시간은 9.1 → 0.8초다.

해설

PL/SQL의 EXECUTE IMMEDIATE ... USING은 같은 문장을 연달아 부르면 파스 호출이 1번이다(교재 11화 §2: 3만 번 불러도 1번). 월이 뒤섞여 들어오면 월이 바뀔 때마다 파스 호출이 생긴다.
더 줄이려면 월마다 한 문장으로 처리한다. 월별 FORALL이나 UPDATE ... WHERE STLM_SEQ IN (SELECT ...) 같은 집합 처리면 실행 호출이 6,000 → 14번이 된다(DML 단원, 교재 15화).
이어 붙인 숫자는 다른 문제도 만든다. 음수 금액은 STLM_AMT + -1200처럼 들어가 우연히 돌아가지만, 소수점이 쉼표인 NLS 설정에서 소수가 들어오면 문장이 깨진다. 바인드 변수는 이런 일이 없다.
월마다 테이블을 나누는 구조를 월별 Range 파티션 테이블 하나로 바꾸면 동적 SQL 자체가 필요 없다(PART 단원). 이 문제는 구조를 그대로 둔 채 할 수 있는 일을 묻는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 파싱 비율은 교재 11화 실측과 같은 수준 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 파싱 비율은 교재 11화 실측과 같은 수준 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황