5분마다 매장 전광판 300대가 한꺼번에 3~4초씩 멈추고, 가끔 숫자가 0으로 깜빡인다

실전PLSQL한빛유통559 / 700

상황

한빛유통 본사와 매장의 대시보드(전광판 300대와 영업 화면)는 5분마다 집계 테이블 T_ORD_DASH를 읽어 화면을 새로 그린다. 집계 테이블은 스케줄러 잡이 5분마다 비우고 다시 채운다. 잡은 0.4초 만에 끝나는데, 잡이 끝난 직후 전광판이 한꺼번에 3~4초씩 멈추고 가끔은 숫자가 0으로 한 번 깜빡인다. 박팀장이 대안 셋을 들고 왔다. 개발팀은 임시 테이블(GTT)로 바꾸자고 하고, DBA는 테이블 두 개를 번갈아 채우고 시노님을 바꿔 끼우자고 하며, 오과장은 DELETE 후 INSERT로 바꾸자고 한다. "셋 중 무엇으로 갈지, 근거와 함께 오후까지 주세요."

테이블과 인덱스

T_ORD_DASH 대시보드 집계: T_ORD_BIG에서 최근 30일을 일자 × 고객 등급 × 상태로 묶은 것 · 270행 · 4블록 · PK (BS_DT, CUS_GD, ORD_ST)
T_ORD_BIG: 1,000,000행 · 5,826블록 · M_CUS: 2,000행 · 30블록
대시보드 문장 3개는 모두 바인드 변수로 쓰여 있다. UNION ALL 여섯 갈래와 분석 함수가 섞인 긴 문장이라 하드 파싱 한 번에 0.7~0.9초가 걸린다.
전광판은 매시 0분, 5분, 10분처럼 잡과 같은 시각에 일제히 새로 고친다. 평소 대시보드 문장 실행은 초당 약 120번이다.
CURSOR_SHARING = EXACT

원본 SQL

sql

-- 스케줄러 잡 DASH_REFRESH : 5분마다 (매시 0분, 5분, 10분 …)
PROCEDURE P_DASH_REFRESH(p_from DATE) IS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE T_ORD_DASH';
  INSERT /*+ APPEND */ INTO T_ORD_DASH (BS_DT, CUS_GD, ORD_ST, ORD_CNT, ORD_AMT)
  SELECT TRUNC(o.ORD_DT), c.CUS_GD, o.ORD_ST, COUNT(*), SUM(o.ORD_AMT)
    FROM T_ORD_BIG o, M_CUS c
   WHERE o.ORD_DT >= p_from
     AND c.CUS_ID = o.CUS_ID
   GROUP BY TRUNC(o.ORD_DT), c.CUS_GD, o.ORD_ST;
  COMMIT;
END;

-- 대시보드 문장 셋 가운데 하나 (앞부분)
SELECT d.BS_DT, d.CUS_GD, SUM(d.ORD_AMT) AS ORD_AMT,
       SUM(SUM(d.ORD_AMT)) OVER (PARTITION BY d.CUS_GD ORDER BY d.BS_DT) AS CUM_AMT
  FROM T_ORD_DASH d
 WHERE d.ORD_ST = :st
 GROUP BY d.BS_DT, d.CUS_GD
 UNION ALL
 …

실행계획 학습용 예시

xplan

[V$SQLAREA · 18:00 · 대시보드 문장 셋 (09:00부터 누적)]
  문장              EXECUTIONS    LOADS   INVALIDATIONS
  등급별 누적         1,312,400      109             108
  상태별 오늘           998,700      109             108
  일자별 추이           986,100      109             108

[10046 → TKPROF · 등급별 누적 문장 · 잡이 끝난 직후 첫 실행]
  Parse  count 1 · cpu 0.78 · elapsed 0.81
  Misses in library cache during parse: 1

[ASH · 10:05:00 ~ 10:05:05 · 초별 활성 세션]
  초        cursor: pin S wait on X   library cache lock   CPU   합계
  :00.4                          0                    3     2      5     ← TRUNCATE
  :01                          212                    0     6    218
  :02                          196                    0     5    201
  :03                          141                    0     4    145
  :04                           12                    0     3     15

[전광판 로그 · 10:05:00.2 ~ 10:05:00.6 에 읽은 화면]  T_ORD_DASH 0건 → 매출 0으로 표시 (하루 30~40번)

[잡 한 번]  0.4초 · TRUNCATE 0.05초 + INSERT(APPEND) 0.33초 · redo 약 20KB

문제

잡은 0.4초 만에 끝나는데 전광판이 3~4초씩 멈추는 이유와, 숫자가 0으로 깜빡이는 이유를 위 자료로 설명하시오.
대안 셋(GTT, 테이블 둘과 시노님 교체, DELETE 후 INSERT)을 효과와 부작용으로 평가하시오.
하나를 골라 잡을 고치고, 고른 방법의 대가를 숫자로 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 스케줄러 잡 DASH_REFRESH : DDL 없이 한 트랜잭션으로 바꿔 채운다
PROCEDURE P_DASH_REFRESH(p_from DATE) IS
BEGIN
  DELETE FROM T_ORD_DASH;                              -- 270행 · DML이라 커서를 무효화하지 않는다
  INSERT INTO T_ORD_DASH (BS_DT, CUS_GD, ORD_ST, ORD_CNT, ORD_AMT)
  SELECT TRUNC(o.ORD_DT), c.CUS_GD, o.ORD_ST, COUNT(*), SUM(o.ORD_AMT)
    FROM T_ORD_BIG o, M_CUS c
   WHERE o.ORD_DT >= p_from
     AND c.CUS_ID = o.CUS_ID
   GROUP BY TRUNC(o.ORD_DT), c.CUS_GD, o.ORD_ST;     -- 직접 경로 힌트는 뗀다 (DELETE는 HWM을 내리지 않는다)
  COMMIT;                                              -- 대시보드는 커밋 전까지 이전 집계를 본다
END;

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

xplan

[고친 뒤 하루 (학습용 예시)]
  문장              EXECUTIONS    LOADS   INVALIDATIONS
  등급별 누적         1,311,900        1               0
  상태별 오늘           998,200        1               0
  일자별 추이           985,800        1               0

  ASH 10:05:00 ~ 10:05:05      초별 활성 세션 5 ~ 9 · cursor: pin S wait on X 없음
  잡 한 번                     0.5초 · redo 약 140KB · undo 약 60KB
  T_ORD_DASH                   하루 내내 4블록
  0 깜빡임                     없음

[참고 · DELETE 뒤에 APPEND를 남겨 두면]  갱신마다 HWM 위에 4블록씩 쌓여 하루 1,152블록 · 대시보드 문장의 FULL이 그만큼 길어진다

모범 답안 서술

원인: TRUNCATE는 DDL이고, DDL은 그 테이블에 기대는 커서를 모두 무효화한다. 대시보드 문장 셋의 INVALIDATIONS가 108, LOADS가 109다. 9시부터 18시까지 5분마다 잡이 돌 때마다 무효화되고 다시 하드 파싱됐다는 뜻이다. 전광판 300대는 잡과 같은 시각에 일제히 새로 고치므로, 무효화된 커서를 300개 세션이 같은 순간에 찾는다. 한 세션이 하드 파싱(0.7~0.9초)을 하는 동안 나머지는 cursor: pin S wait on X로 기다리고, 문장 셋을 차례로 그렇게 기다리니 3~4초가 된다. 숫자가 0으로 깜빡이는 것은 TRUNCATE가 그 자리에서 커밋되기 때문이다. TRUNCATE와 INSERT의 커밋 사이(0.4초)에 읽은 화면은 빈 테이블을 본다. GTT: 쓸 수 없다. GTT의 행은 넣은 세션에게만 보이므로, 잡 세션이 채운 집계를 전광판 세션은 한 건도 보지 못한다. 테이블 둘과 시노님 교체: 시노님을 바꿔 끼우는 CREATE OR REPLACE SYNONYM도 DDL이라 시노님에 기대는 커서를 무효화한다. 비우는 대상이 쉬는 테이블로 바뀌어 0 깜빡임은 사라지지만, 5분마다 같은 줄이 생기는 것은 그대로다. 테이블과 관리할 것도 둘로 는다. DELETE 후 INSERT: 이것을 고른다. DDL이 없으므로 커서는 무효화되지 않고(LOADS 1, INVALIDATIONS 0), 전광판이 몰려도 소프트 파싱으로 끝난다. 한 트랜잭션이라 대시보드는 커밋 전까지 이전 집계를 보므로 0 깜빡임도 사라진다. 대가는 270행만큼의 undo와 redo로, 잡 한 번에 약 60KB와 140KB(지금은 redo 약 20KB)이고 잡 시간은 0.4 → 0.5초다. 이 크기에서는 무시할 만하다. 다만 APPEND 힌트는 뗀다. DELETE는 HWM을 내리지 않으므로 직접 경로로 넣으면 갱신마다 HWM 위에 새 블록이 쌓여 하루에 1,152블록까지 커진다.

해설

큰 테이블을 비울 때는 TRUNCATE가 맞다. 100만 행을 DELETE하면 undo와 redo가 크다(DML 단원). 이 문제는 270행짜리 작은 테이블을 수백 세션이 쉬지 않고 읽는 경우라 셈이 거꾸로 된다. 비울 행 수와 함께 그 테이블을 읽는 세션 수를 본다.
커서를 무효화하는 것은 TRUNCATE만이 아니다. 인덱스 생성과 재생성, 컬럼 추가, NO_INVALIDATE => FALSE를 준 통계 수집, 파티션 교환도 기대는 커서를 무효화한다. 바쁜 시간대의 DDL은 수백 세션의 하드 파싱을 한꺼번에 부른다.
12.2부터 일부 DDL에는 지연 무효화(CURSOR_INVALIDATION = DEFERRED)를 쓸 수 있지만, 어느 DDL에 듣는지는 버전마다 다르다. 쓰려면 이 테이블과 이 버전에서 먼저 잰다.
전광판이 같은 초에 일제히 새로 고치는 것도 줄을 키운다. 새로 고침 시각을 몇 초씩 흩어 두면 고친 뒤에도 순간 부하가 고르게 퍼진다.
바뀐 행만 고치는 MERGE도 DDL이 없는 답이다. 집계 행 대부분이 그대로라면 undo와 redo가 더 작지만, 사라진 조합을 지우는 처리를 따로 넣어야 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황