연습 DB에서는 끝나던 월 집계 배치가 운영에서 ORA-12838로 멈춘다

기초PLSQL유통460 / 700

상황

본사 레거시 유통의 월 마감에 「매장 상품 월 판매 집계」를 새로 만들었다. 매장마다 상품의 판매 금액 순위도 함께 저장한다. 연습 DB(23ai Free)에서 몇 번 돌려 봤을 때는 문제가 없었는데, 12월 1일 운영(19c)에서 처음 돌리자 두 번째 문장에서 ORA-12838로 멈추고 전부 롤백됐다. 배치 담당자: "첫 문장이 끝났으니 그 테이블을 고치는 건 당연히 되는 줄 알았어요."

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · 2023년 11월 20,712건 · IX_CF10_SLE_DT (SLE_DT)
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 11월분 45,370행
CF11_SHP_MON 매장 상품 월 집계(문제 설정): SLE_YM · SHP_NO · ITM_NO · SLE_QTY · SLE_AMT · AMT_RNK · PK (SLE_YM, SHP_NO, ITM_NO). 매장 다섯 곳에 한 달 약 530행이다. 이 배치만 쓴다.
운영은 Oracle 19c, 연습 DB는 23ai Free다.

원본 SQL

sql

PROCEDURE P_SHP_ITM_MON(p_ym VARCHAR2) IS          -- p_ym = '202311'
BEGIN
  -- ① 매장 · 상품별 합계
  INSERT /*+ APPEND */ INTO CF11_SHP_MON (SLE_YM, SHP_NO, ITM_NO, SLE_QTY, SLE_AMT)
  SELECT p_ym, h.SHP_NO, d.ITM_NO, SUM(d.SLE_QTY), SUM(d.SLE_QTY * d.SLE_UPRC)
    FROM CF10 h, CF11 d
   WHERE d.SLE_NO = h.SLE_NO
     AND h.SLE_DT BETWEEN p_ym || '01' AND p_ym || '31'
   GROUP BY h.SHP_NO, d.ITM_NO;

  -- ② 매장 안 판매 금액 순위
  UPDATE CF11_SHP_MON m
     SET AMT_RNK = (SELECT COUNT(*) + 1
                      FROM CF11_SHP_MON x
                     WHERE x.SLE_YM = m.SLE_YM
                       AND x.SHP_NO = m.SHP_NO
                       AND x.SLE_AMT > m.SLE_AMT)
   WHERE m.SLE_YM = p_ym;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN ROLLBACK; RAISE;
END;

실행계획 학습용 예시

xplan

[운영 19c · 12/01 02:10 배치 로그]
  ① INSERT /*+ APPEND */ …   531 rows · 0.21 초 (LOAD AS SELECT)
  ② UPDATE CF11_SHP_MON …
     ORA-12838: cannot read/modify an object after modifying it in parallel
     ORA-06512: at "PUP.P_SHP_ITM_MON", line 12
  → ROLLBACK · CF11_SHP_MON 11월분 0행

[연습 DB 23ai Free · 같은 프로시저 · 같은 데이터]
  ① 531 rows · ② 531 rows updated · COMMIT · 정상 종료

[① 실행계획 — 운영]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                      |                |      1 |        |      0 |00:00:00.21 |    8462 |
|   1 |  LOAD AS SELECT                       | CF11_SHP_MON   |      1 |        |      0 |00:00:00.21 |    8462 |
|   2 |   HASH GROUP BY                       |                |      1 |    535 |    531 |00:00:00.20 |    8401 |
|*  3 |    HASH JOIN                          |                |      1 |  45614 |  45370 |00:00:00.18 |    8401 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF10          |      1 |  20850 |  20712 |00:00:00.01 |     248 |
|*  5 |      INDEX RANGE SCAN                 | IX_CF10_SLE_DT |      1 |  20850 |  20712 |00:00:00.01 |      59 |
|   6 |     TABLE ACCESS FULL                 | CF11           |      1 |   1096K|   1096K|00:00:00.09 |    8153 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("D"."SLE_NO"="H"."SLE_NO")
   5 - access("H"."SLE_DT">=:B1||'01' AND "H"."SLE_DT"<=:B1||'31')

문제

② UPDATE가 운영에서만 ORA-12838로 멈춘 이유를 ① 문장의 적재 방식과 트랜잭션으로 설명하시오.
같은 결과(합계와 매장 안 순위)를 내도록 프로시저를 고치고, 고친 방법이 나은 이유를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE P_SHP_ITM_MON(p_ym VARCHAR2) IS          -- p_ym = '202311'
BEGIN
  -- 합계와 순위를 한 문장에서 — 적재한 테이블을 같은 트랜잭션에서 다시 읽거나 고치지 않는다
  INSERT /*+ APPEND */ INTO CF11_SHP_MON (SLE_YM, SHP_NO, ITM_NO, SLE_QTY, SLE_AMT, AMT_RNK)
  SELECT p_ym, h.SHP_NO, d.ITM_NO,
         SUM(d.SLE_QTY),
         SUM(d.SLE_QTY * d.SLE_UPRC),
         RANK() OVER (PARTITION BY h.SHP_NO ORDER BY SUM(d.SLE_QTY * d.SLE_UPRC) DESC)
    FROM CF10 h, CF11 d
   WHERE d.SLE_NO = h.SLE_NO
     AND h.SLE_DT BETWEEN p_ym || '01' AND p_ym || '31'
   GROUP BY h.SHP_NO, d.ITM_NO;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN ROLLBACK; RAISE;
END;

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

xplan

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                       |                |      1 |        |      0 |00:00:00.22 |    8462 |
|   1 |  LOAD AS SELECT                        | CF11_SHP_MON   |      1 |        |      0 |00:00:00.22 |    8462 |
|   2 |   WINDOW SORT                          |                |      1 |    535 |    531 |00:00:00.21 |    8401 |
|   3 |    HASH GROUP BY                       |                |      1 |    535 |    531 |00:00:00.20 |    8401 |
|*  4 |     HASH JOIN                          |                |      1 |  45614 |  45370 |00:00:00.18 |    8401 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| CF10          |      1 |  20850 |  20712 |00:00:00.01 |     248 |
|*  6 |       INDEX RANGE SCAN                 | IX_CF10_SLE_DT |      1 |  20850 |  20712 |00:00:00.01 |      59 |
|   7 |      TABLE ACCESS FULL                 | CF11           |      1 |   1096K|   1096K|00:00:00.09 |    8153 |
----------------------------------------------------------------------------------------------------------------

[운영 19c] 정상 종료 · 531행 · AMT_RNK 채움 · ② UPDATE와 그 안의 상관 서브쿼리 531번이 사라졌다

모범 답안 서술

①은 /*+ APPEND */ 직접 경로 적재다(실행계획 1번 줄 LOAD AS SELECT). 직접 경로로 넣은 테이블은 그 트랜잭션이 커밋하기 전에는 같은 트랜잭션에서 다시 읽거나 고칠 수 없고, 하려고 하면 ORA-12838이 난다. ② UPDATE는 커밋 전에 같은 CF11_SHP_MON을 고치고, SET의 서브쿼리로 그 테이블을 다시 읽으므로 바로 걸렸다. 예외 처리부가 ROLLBACK 뒤 RAISE했기 때문에 ①의 531행도 되돌아가 11월분이 0행이 됐다. 연습 DB에서는 이 제약이 드러나지 않았다. 교재 14화 §4.4에서 본 것처럼 23ai Free에서는 직접 경로 적재 직후 같은 트랜잭션의 조회가 막히지 않았다. 운영은 19c라서 문서대로 막힌다. 버전에 따라 다른 제약이 있으므로 배치는 운영과 같은 버전에서 시험해야 한다. 고친 판은 합계와 순위를 한 문장에서 만든다. GROUP BY 결과에 RANK() OVER (PARTITION BY SHP_NO ORDER BY SUM(…) DESC)를 붙이면 원본 UPDATE의 'COUNT(*) + 1'과 같은 순위(동률은 같은 순위)가 나온다. 적재한 테이블을 다시 건드리지 않으므로 ORA-12838이 날 자리가 없고, 행마다 자기 테이블을 다시 읽던 상관 서브쿼리(531번)도 사라진다. 일량은 ①의 Buffers 8,462 그대로이고 WINDOW SORT 하나가 더해질 뿐이다. ① 뒤에 COMMIT을 넣고 UPDATE를 하는 방법도 에러는 피한다. 하지만 순위가 비어 있는 중간 상태가 커밋되어 그 사이 화면에 보이고, ②가 실패하면 순위 없는 집계가 남는다. 한 문장으로 끝내는 쪽이 낫다.

해설

다른 정답: 행이 530개뿐이므로 APPEND를 빼고 일반 INSERT로 넣으면 같은 트랜잭션에서 UPDATE해도 된다. 다만 자기 테이블을 행마다 다시 읽는 UPDATE는 그대로 남는다.
ORA-12838의 메시지는 '병렬로 고친 뒤'라고 적혀 있지만 직렬 직접 경로 적재에도 같은 제약이 걸린다. 병렬 DML을 쓰면 버전과 상관없이 커밋 전에는 그 테이블을 읽을 수 없다.
순위 열처럼 같은 행들로 계산할 수 있는 값은 적재 SQL 안에서 분석 함수로 만든다. 넣고 나서 다시 고치는 두 단계는 redo도 두 번 만든다.
교재 14화의 문장 그대로 '제약도 내 버전에서 재 보고 쓴다'. 연습 DB가 운영보다 너그러울 때가 가장 위험하다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황