15분 칸마다 한 단계씩 띄우는 월초 체인에서 등급 재산정이 칸을 넘기자 월 보정이 옛 등급으로 돌았다

실전PLSQL한빛유통494 / 700

상황

한빛유통의 월초 체인은 매달 1일 05:00부터 15분 간격으로 단계 여덟 개를 하나씩 띄운다. 2019년에 단계마다 스케줄러 작업을 따로 만들고 시작 시각을 박아 둔 구조다. 마감은 07:00(정산팀 출근과 정산 화면 오픈)이고, 끝나는 시각은 늘 06:51 무렵이라 '여유 9분'으로 관리해 왔다. 11월 1일에는 S5(고객 등급 재산정)가 고객 정보 화면이 잡고 있던 행 잠금을 15분 기다려 06:15:41에 끝났다. S6(월 보정)은 06:15:00에 정해진 대로 시작했고, 11월 3일 정산팀이 등급이 바뀐 고객 214명의 10월 보정액이 옛 등급 보정률로 DW에 넘어간 것을 찾아냈다. 박팀장: "시간이 모자라서가 아니라는데, 그럼 뭐가 문제입니까? 숫자로 정리해 주세요."

테이블과 인덱스

T_ORD_BIG 1,000,000행 · 5,826블록 · T_ORD 100,000행 · 896블록 · T_ORD_STLM 100,000행 · 640블록 · M_CUS 2,000행 · 30블록 · T_ITM_EVL 5,000행 · 48블록
단계마다 읽고 쓰는 테이블과 11월 1일 실행 기록은 실행계획 칸에 있다. 단계 하나하나는 이미 고친 판(14화 이후)이다.
서버는 CPU 8개이고 월초 05:00 ~ 07:00에 다른 배치는 없다. 정산 화면은 07:00 전에는 쓰지 않는다.
단계를 담은 프로그램 PRG_S1 ~ PRG_S8이 스케줄러에 등록되어 있다.

원본 SQL

sql

-- 2019년 등록 · 단계마다 작업 하나, 시작 시각 고정 (S1 05:00, S2 05:15, … S8 06:45)
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(job_name => 'JOB_MON_S1', program_name => 'PRG_S1',
                            repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=5;BYMINUTE=0',  enabled => TRUE);
  DBMS_SCHEDULER.CREATE_JOB(job_name => 'JOB_MON_S2', program_name => 'PRG_S2',
                            repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=5;BYMINUTE=15', enabled => TRUE);
  DBMS_SCHEDULER.CREATE_JOB(job_name => 'JOB_MON_S3', program_name => 'PRG_S3',
                            repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=5;BYMINUTE=30', enabled => TRUE);
  -- … S4 05:45 · S5 06:00 · S6 06:15 · S7 06:30
  DBMS_SCHEDULER.CREATE_JOB(job_name => 'JOB_MON_S8', program_name => 'PRG_S8',
                            repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=6;BYMINUTE=45', enabled => TRUE);
END;

실행계획 학습용 예시

xplan

[11월 1일 — 스케줄러 실행 기록]
  단계  하는 일              정해진 시작  끝         경과       읽는 것 → 쓰는 것
  S1    T_ORD_BIG 재적재      05:00       05:01:10   1분 10초   STG_ORD_BIG → T_ORD_BIG
  S2    T_ORD_BIG 통계 수집    05:15       05:15:50   0분 50초   T_ORD_BIG
  S3    정산 적재 P1          05:30       05:30:20   0분 20초   T_ORD → T_ORD_STLM
  S4    월마감 집계           05:45       05:47:30   2분 30초   T_ORD_BIG → T_MON_SUM
  S5    고객 등급 재산정       06:00       06:15:41  15분 41초   T_ORD_BIG → M_CUS     (평소 0분 40초 · 이날 enq: TX 15분)
  S6    월 보정               06:15       06:15:20   0분 20초   T_ORD_STLM · M_CUS → T_STLM_ADJ
  S7    상품평가 요약          06:30       06:30:05   0분 05초   T_ITM_EVL → T_ITM_EVL_SUM
  S8    DW 전송               06:45       06:51:00   6분 00초   T_MON_SUM · T_ORD_STLM · T_STLM_ADJ → DW
  마감 07:00 · 끝 06:51:00 · 여유 9분 · 실패한 단계 0

[10월 1일 — 평소] 모든 단계가 위와 같고 S5 0분 40초 · 끝 06:51:00

[11월 3일 정산팀 확인]
  등급이 바뀐 고객 214명 · 10월 보정이 옛 등급 보정률로 계산됨 · DW 에 그대로 넘어감

문제

11월 1일 기록으로 체인이 실제로 일한 시간과 걸린 시간을 숫자로 비교하고, 월 보정이 옛 등급으로 돈 이유를 설명하시오. '여유 9분'이라는 관리 지표가 무엇을 놓쳤는지도 쓰시오.
단계마다 읽고 쓰는 테이블로 선후 관계를 정하고, 체인이 가장 일찍 끝나는 경로(임계 경로)와 그때의 끝 시각, 여유를 구하시오. 11월 1일처럼 S5가 15분 41초 걸리면 끝 시각이 언제가 되는지도 구하시오.
시각이 아니라 선후 관계로 단계를 띄우도록 체인을 다시 작성하시오. 함께 도는 단계가 생기는 데 따른 부작용과, 단계가 실패했을 때의 동작도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 옛 작업 여덟 개는 끄고, 체인 하나로 바꾼다 (프로그램 PRG_S1 ~ PRG_S8 은 그대로)
BEGIN
  FOR j IN (SELECT job_name FROM user_scheduler_jobs WHERE job_name LIKE 'JOB_MON_S_') LOOP
    DBMS_SCHEDULER.DISABLE(j.job_name);
  END LOOP;

  DBMS_SCHEDULER.CREATE_CHAIN(chain_name => 'CH_MON_START');
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP('CH_MON_START', 'S1', 'PRG_S1');
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP('CH_MON_START', 'S2', 'PRG_S2');
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP('CH_MON_START', 'S3', 'PRG_S3');
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP('CH_MON_START', 'S4', 'PRG_S4');
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP('CH_MON_START', 'S5', 'PRG_S5');
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP('CH_MON_START', 'S6', 'PRG_S6');
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP('CH_MON_START', 'S7', 'PRG_S7');
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP('CH_MON_START', 'S8', 'PRG_S8');

  -- 읽고 쓰는 테이블로 정한 선후 관계
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE('CH_MON_START', 'TRUE',                          'START S1, S3, S7');  -- 서로 겹치지 않는다
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE('CH_MON_START', 'S1 SUCCEEDED',                  'START S2');
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE('CH_MON_START', 'S2 SUCCEEDED',                  'START S4, S5');      -- 둘 다 T_ORD_BIG 을 읽기만
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE('CH_MON_START', 'S3 SUCCEEDED AND S5 SUCCEEDED', 'START S6');          -- 정산과 새 등급이 다 있어야
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE('CH_MON_START', 'S4 SUCCEEDED AND S6 SUCCEEDED', 'START S8');
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE('CH_MON_START', 'S7 COMPLETED AND S8 SUCCEEDED', 'END');
  -- 어느 단계든 실패하면 뒤 단계를 띄우지 않고 체인을 실패로 끝낸다 (경보 → 원인을 고친 뒤 실패한 단계부터)
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE('CH_MON_START',
       'S1 FAILED OR S2 FAILED OR S3 FAILED OR S4 FAILED OR S5 FAILED OR S6 FAILED OR S8 FAILED', 'END 1');
  DBMS_SCHEDULER.ENABLE('CH_MON_START');

  DBMS_SCHEDULER.CREATE_JOB(job_name        => 'JOB_MON_START',
                            job_type        => 'CHAIN',
                            job_action      => 'CH_MON_START',
                            repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=5;BYMINUTE=0',
                            enabled         => TRUE);
END;

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

xplan

[체인으로 바꾼 뒤 — 같은 단계 경과로 계산한 시간표]
  평소 (S5 0분 40초)
    05:00:00  S1 · S3 · S7 시작        S3 05:00:20 끝 · S7 05:00:05 끝
    05:01:10  S1 끝 → S2               05:02:00 S2 끝 → S4 · S5
    05:02:40  S5 끝 (S3 은 이미 끝)  → S6   05:03:00 S6 끝
    05:04:30  S4 끝 (S6 도 끝)       → S8   05:10:30 S8 끝 · 체인 끝
    임계 경로 S1 → S2 → S4 → S8 = 10분 30초 · 마감까지 여유 1시간 49분 30초

  11월 1일처럼 S5 가 15분 41초
    05:17:41  S5 끝 → S6 · 05:18:01 S6 끝 → S8 · 05:24:01 체인 끝
    임계 경로 S1 → S2 → S5 → S6 → S8 = 24분 01초 · 월 보정은 새 등급으로 계산된다

모범 답안 서술

11월 1일 여덟 단계의 경과를 모두 더하면 11분 55초(S5가 늘어진 이날은 26분 56초)인데, 체인은 05:00부터 06:51까지 111분이 걸렸다. 걸린 시간의 대부분은 다음 칸의 시각을 기다리며 아무 일도 하지 않은 시간이다. 그리고 각 단계는 앞 단계가 끝났는지 보지 않고 정해진 시각에 시작한다. 평소에는 S5가 06:00:40에 끝나 06:15의 S6이 새 등급을 읽었지만, 11월 1일에는 S5가 행 잠금을 기다리다 06:15:41에 끝났고 S6은 06:15:00에 시작해 아직 고쳐지지 않은 M_CUS의 옛 등급으로 보정했다. 에러가 없으니 S8은 그 보정을 그대로 DW로 보냈다. '여유 9분'은 '마지막 칸의 끝'과 마감 사이만 잴 뿐, 칸 안에서 단계가 늘어져 다음 단계와 겹치는 일은 보지 못한다. 시간이 모자란 게 아니라 순서를 지키는 장치가 없었다. 선후 관계는 단계마다 읽고 쓰는 테이블로 정한다. S2는 S1이 쓴 T_ORD_BIG을 읽고, S4와 S5는 S2의 통계가 있는 T_ORD_BIG을 읽는다. S6은 S3이 쓴 T_ORD_STLM과 S5가 쓴 M_CUS를 읽고, S8은 S4와 S6의 결과를 읽는다. S3(T_ORD → T_ORD_STLM)과 S7(T_ITM_EVL)은 S1, S2와 겹치는 테이블이 없다. 그래서 S1, S3, S7은 05:00에 함께 띄우고, S2 뒤에 S4와 S5를 나란히 띄운다. 경로 길이를 재면 S1 → S2 → S4 → S8이 1분 10초 + 50초 + 2분 30초 + 6분 = 10분 30초로 가장 길고(임계 경로), S1 → S2 → S5 → S6 → S8은 9분이다. 체인은 05:10:30에 끝나고, 마감까지의 여유는 9분 → 1시간 49분 30초가 된다. S5가 11월 1일처럼 15분 41초 걸리면 S5를 지나는 경로가 24분 01초로 임계 경로가 되어 05:24:01에 끝나며, S6은 S5가 끝난 뒤에야 시작하므로 새 등급으로 계산한다. 고친 체인은 스케줄러 체인 하나에 단계 여덟 개를 두고, 규칙으로 'S5 성공 그리고 S3 성공이면 S6 시작'처럼 선후 관계를 적는다. 시작 시각은 05:00 하나뿐이다. 부작용도 있다. 함께 도는 단계(S1, S3, S7과 S4, S5)는 CPU와 I/O를 나눠 쓰므로 단독으로 잰 경과보다 조금 늘 수 있다. CPU 8개에 동시에 도는 단계는 많아야 셋이고 S4와 S5는 둘 다 T_ORD_BIG을 읽기만 하므로 감당할 수 있다. 같은 테이블을 고치는 단계끼리는 나란히 두지 않는다. 단계가 실패하면 그 뒤 단계는 시작하지 않고 체인이 실패로 끝나 경보가 울린다. 칸 방식에서는 실패한 단계 뒤의 단계들도 시각이 되면 그대로 돌아 반쯤 틀린 결과를 만들었다. 원인을 고친 뒤에는 실패한 단계부터 다시 돌린다. 단계가 멱등이어야 하는 이유이기도 하다(DML-067).

해설

T3 리포트의 칸: 1번 칸의 '여유 시간'은 체인 전체의 끝으로 재고, 3번 칸에는 단계마다 시작, 끝, 경과와 함께 '읽는 것 → 쓰는 것'을 적는다. 그 표가 있으면 선후 관계와 임계 경로를 바로 그릴 수 있다(교재 14화 T3 배치 SLA 리포트).
몇 달 뒤에 넘치나: 임계 경로 10분 30초에 대해 여유가 1시간 49분 30초이고, 주문은 한 달에 약 2.7%씩 는다. 임계 경로의 단계가 모두 주문 수에 비례한다고 쳐도 한 달에 17초 남짓 늘 뿐이라 넘칠 걱정보다 순서를 지키는 일이 먼저다.
S5의 15분: 등급 재산정이 화면의 행 잠금을 끝없이 기다리지 않도록 잠금 대기 한도를 두는 것도 방법이다. 그러면 S5가 실패로 끝나 체인 규칙이 S6을 막는다. 기다릴지 실패할지는 마감과 재실행 비용으로 정한다.
다른 정답: 스케줄러 체인 대신 단계 작업마다 앞 단계의 성공을 확인하고(단계 기록 테이블) 없으면 기다리거나 실패하게 만들어도 된다. 핵심은 '시각'이 아니라 '앞 단계가 끝났다는 사실'을 조건으로 삼는 것이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황