JOIN-023

같은 대사 SQL이 sqlplus에서는 0.1초인데 야간 배치 안에서는 2초 가까이 걸린다

기초INSERT한빛유통173 / 700

상황

야간 정산 배치의 마지막 단계는 정산과 주문을 맞대어 금액이나 고객이 다른 건을 점검 테이블에 넣는다. 정상이면 0건이다. 오과장이 SQL을 떼어 sqlplus에서 돌리면 0.1초인데, 배치 안에서는 매일 2초 가까이 걸린다. 배치 셸 스크립트 첫머리에는 ALTER SESSION 두 줄이 있고, 옆에 -- 배치가 PGA 를 다 먹어 온라인이 죽은 뒤 (2013.03)라는 주석이 달려 있다. 아래는 배치 세션에서 뜬 실행계획이다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ)
T_ORD_STLM: 100,000행 · 640블록 · 정산 배치가 T_ORD에서 적재 · 인덱스 없음
T_STLM_CHK 점검 테이블 (이 문제에서 쓰는 파생 테이블): CHK_DT · ORD_SEQ · ORD_AMT · STLM_AMT · 인덱스 없음
인스턴스 설정: pga_aggregate_target 512M · workarea_size_policy AUTO · 통계 최신

원본 SQL

sql

-- batch_stlm_chk.sh 첫머리 (2013.03)
ALTER SESSION SET workarea_size_policy = MANUAL;
ALTER SESSION SET hash_area_size = 131072;

INSERT INTO T_STLM_CHK (CHK_DT, ORD_SEQ, ORD_AMT, STLM_AMT)
SELECT TRUNC(SYSDATE), o.ORD_SEQ, o.ORD_AMT, s.STLM_AMT
  FROM T_ORD o, T_ORD_STLM s
 WHERE s.ORD_SEQ = o.ORD_SEQ
   AND (s.STLM_AMT <> o.ORD_AMT OR s.CUS_ID <> o.CUS_ID);

실행계획 학습용 예시 · 교재 06화 work area 재현 방법

xplan

[배치 세션]
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |            |      1 |        |      0 |00:00:01.87 |    1545 |  1702 |   1702 |       |       |          |         |
|   1 |  LOAD TABLE CONVENTIONAL | T_STLM_CHK |      1 |        |      0 |00:00:01.87 |    1545 |  1702 |   1702 |       |       |          |         |
|*  2 |   HASH JOIN              |            |      1 |   9950 |      0 |00:00:01.87 |    1536 |  1702 |   1702 |  6278K|  1229K|  126K (4)|    6144K|
|   3 |    TABLE ACCESS FULL     | T_ORD_STLM |      1 |    100K|    100K|00:00:00.02 |     640 |     0 |      0 |       |       |          |         |
|   4 |    TABLE ACCESS FULL     | T_ORD      |      1 |    100K|    100K|00:00:00.02 |     896 |     0 |      0 |       |       |          |         |
---------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("S"."ORD_SEQ"="O"."ORD_SEQ")
       filter(("S"."STLM_AMT"<>"O"."ORD_AMT" OR "S"."CUS_ID"<>"O"."CUS_ID"))

문제

2번 줄의 Used-Mem 괄호 안 숫자와 Used-Tmp, Reads/Writes가 무엇을 뜻하는지 설명하시오. sqlplus에서는 빠른데 배치에서만 느린 원인도 찾으시오.
배치를 고치고, 2013년의 그 두 줄이 지금은 왜 필요 없는지와 온라인을 지키려면 대신 무엇을 볼지 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- batch_stlm_chk.sh 첫머리의 두 줄을 지운다. AUTO 에서는 PGA_AGGREGATE_TARGET 안에서 오라클이 나눠 준다
-- ALTER SESSION SET workarea_size_policy = MANUAL;   (삭제)
-- ALTER SESSION SET hash_area_size = 131072;          (삭제)

INSERT INTO T_STLM_CHK (CHK_DT, ORD_SEQ, ORD_AMT, STLM_AMT)
SELECT TRUNC(SYSDATE), o.ORD_SEQ, o.ORD_AMT, s.STLM_AMT
  FROM T_ORD o, T_ORD_STLM s
 WHERE s.ORD_SEQ = o.ORD_SEQ
   AND (s.STLM_AMT <> o.ORD_AMT OR s.CUS_ID <> o.CUS_ID);

-- 온라인 보호는 세션마다 묶어 두지 않고 인스턴스 한도로 본다 (DBA 확인 사항)
-- SHOW PARAMETER pga_aggregate_limit

개선 후 실행계획 학습용 예시 · 교재 06화 work area 재현 방법

xplan

[AUTO 로 돌린 배치 세션]
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |            |      1 |        |      0 |00:00:00.09 |    1545 |     0 |      0 |       |       |          |         |
|   1 |  LOAD TABLE CONVENTIONAL | T_STLM_CHK |      1 |        |      0 |00:00:00.09 |    1545 |     0 |      0 |       |       |          |         |
|*  2 |   HASH JOIN              |            |      1 |   9950 |      0 |00:00:00.09 |    1536 |     0 |      0 |  6278K|  1229K| 6402K (0)|         |
|   3 |    TABLE ACCESS FULL     | T_ORD_STLM |      1 |    100K|    100K|00:00:00.02 |     640 |     0 |      0 |       |       |          |         |
|   4 |    TABLE ACCESS FULL     | T_ORD      |      1 |    100K|    100K|00:00:00.02 |     896 |     0 |      0 |       |       |          |         |
---------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("S"."ORD_SEQ"="O"."ORD_SEQ")
       filter(("S"."STLM_AMT"<>"O"."ORD_AMT" OR "S"."CUS_ID"<>"O"."CUS_ID"))

모범 답안 서술

2번 줄 HASH JOIN의 Used-Mem이 126K (4)다. 괄호 안 숫자는 패스 수이고, (4)는 build를 메모리에 다 올리지 못해 TEMP에 내렸다가 다시 올리기를 네 번 한 multipass라는 뜻이다. Used-Tmp 6,144K는 그때 TEMP에 쓴 양이고, Reads/Writes 1,702는 TEMP를 오간 블록 수다. Buffers는 1,545로 두 테이블을 한 번씩 읽은 만큼이라 sqlplus에서 돌려도 같다. 1.87초의 대부분은 이 TEMP 왕복에 들었다. 원인은 배치 세션 첫머리의 두 줄이다. workarea_size_policy = MANUAL이면 work area 크기를 세션 파라미터가 정하고, hash_area_size = 131072가 해시 조인 하나에 128KB만 준다. 이 해시 조인은 optimal로 끝내려면 6,278K(OMem), onepass라도 1,229K(1Mem)가 필요하다. 128KB는 그 어느 쪽에도 못 미쳐 multipass가 됐다. sqlplus 세션은 인스턴스 기본값(AUTO)이라 optimal로 끝난다. 두 줄을 지워 AUTO로 돌린다. 그러면 오라클이 PGA_AGGREGATE_TARGET 512M 안에서 work area를 나눠 주고, 이 해시 조인은 6,402K (0)로 메모리 안에서 끝난다. Reads와 Writes는 0이 되고 A-Time은 1.87 → 0.09초로 준다. Buffers는 1,545 그대로다. 2013년의 두 줄은 그때 배치가 PGA를 다 써서 온라인이 멈춘 사고의 처방이었고, 그때는 맞았다. 하지만 세션마다 크기를 묶어 두면 데이터가 늘어도 work area가 그대로라 해마다 느려진다. 온라인을 지키는 일은 인스턴스 쪽에서 한다. AUTO로 두고 PGA_AGGREGATE_TARGET과 PGA_AGGREGATE_LIMIT, 그리고 배치 시간대의 V$PGASTAT을 DBA와 함께 확인한다.

해설

세 등급 읽는 법: (0) optimal은 메모리에서 끝, (1) onepass는 한 번 내렸다 올림, (2) 이상은 multipass다. 판정은 괄호로 하고, Buffers로는 보이지 않는다 (교재 06화 §6 'T0 측정규약의 첫 사각지대').
재현 방법이 곧 원인: 06화가 work area 등급을 재현할 때 쓴 방법도 ALTER SESSION SET workarea_size_policy = MANUAL이었다. 운영 스크립트에 그 줄이 남아 있으면 운영에서 같은 일이 매일 벌어진다. 배치 스크립트 첫머리의 ALTER SESSION은 정기 점검 항목에 넣는다.
다른 정답: MANUAL을 두고 hash_area_size만 크게 올려도 이 SQL은 빨라진다. 하지만 세션마다 고정 크기를 잡으므로 같은 세션의 다른 정렬과 해시, 동시에 도는 배치 수만큼 메모리가 곱해진다. 2013년 사고와 같은 위험을 다시 들이는 셈이다.
판정 순서: 계획에 work area 줄이 있는지, 등급이 무엇인지, 필요한 크기가 무엇에 비례하는지, 해시나 정렬을 없앨 수 있는지를 차례로 본다 (교재 09화 §6).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 06화 work area 재현 방법 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 06화 work area 재현 방법 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황