고객 등급 재산정 배치가 작업 테이블을 채우다 ORA-01652로 멈췄다

기초INSERT한빛유통344 / 700

상황

야간 배치 「고객 등급 재산정」의 1단계는 최근 2년 주문을 작업 테이블 W_ORD_GD로 옮기고, 2단계는 그 테이블을 고객별로 묶어 등급을 매긴다. 지난밤 1단계가 ORA-01652: unable to extend temp segment by 128 in tablespace TEMP로 멈췄다. 같은 시각에 다른 큰 배치 둘이 TEMP를 쓰고 있었다. 배치 담당 오과장은 TEMP 테이블스페이스를 늘리자고 한다. 1단계 SQL의 ORDER BY 옆에는 -- 고객순으로 넣어야 2단계가 빠르다 (2017.03 강도현)라는 주석이 있다. 2단계는 2021년에 PL/SQL 루프에서 GROUP BY 한 문장으로 바뀌었다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 2023-01-01 이후 주문은 666,964행이다
W_ORD_GD(작업 테이블, 이 문제용): CUS_ID · ORD_DT · ORD_AMT · ORD_ST · 배치를 시작할 때 TRUNCATE · 인덱스 없음 · 2단계 말고는 아무도 읽지 않는다
2단계 SQL: SELECT CUS_ID, COUNT(*), SUM(ORD_AMT) FROM W_ORD_GD WHERE ORD_ST = 'COMPLETE' GROUP BY CUS_ID(HASH GROUP BY, 고객 2,000명)
02시의 work area 상한(global memory bound)은 약 20MB이고, TEMP 테이블스페이스 4GB를 세 배치가 나눠 쓴다. 통계는 최신이다.

원본 SQL

sql

INSERT /*+ APPEND */ INTO W_ORD_GD (CUS_ID, ORD_DT, ORD_AMT, ORD_ST)
SELECT CUS_ID, ORD_DT, ORD_AMT, ORD_ST
  FROM T_ORD_BIG
 WHERE ORD_DT >= DATE '2023-01-01'
 ORDER BY CUS_ID, ORD_DT        -- 고객순으로 넣어야 2단계가 빠르다 (2017.03 강도현)

실행계획 학습용 예시

xplan

[그저께 밤 · 성공한 실행]
----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | Used-Tmp|
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |           |      1 |        |      0 |00:00:09.84 |    6136 |       |       |          |         |
|   1 |  LOAD AS SELECT                  | W_ORD_GD  |      1 |        |      0 |00:00:09.84 |    6136 |       |       |          |         |
|   2 |   OPTIMIZER STATISTICS GATHERING |           |      1 |    667K|    667K|00:00:07.95 |    5839 |       |       |          |         |
|   3 |    SORT ORDER BY                 |           |      1 |    667K|    667K|00:00:07.71 |    5839 |    31M|  1820K|   20M (1)|      29M|
|*  4 |     TABLE ACCESS FULL            | T_ORD_BIG |      1 |    667K|    667K|00:00:00.21 |    5826 |       |       |          |         |
----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("ORD_DT">=TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

[지난밤]  ORA-01652 · 3번 줄이 정렬 묶음(run)을 TEMP에 내려놓다가 실패 · 그 시각 TEMP 사용 3.98GB / 4GB(다른 배치 둘이 3.95GB)

문제

1단계가 TEMP를 쓰는 까닭을 실행계획 3번 줄로 설명하고, 그 정렬이 지금의 2단계에 쓸모가 있는지 판단하시오.
2단계에 같은 결과를 넘기면서 1단계가 TEMP를 쓰지 않도록 고치고, TEMP를 늘리자는 안과 비교해 무엇이 다른지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 2단계(HASH GROUP BY)는 순서를 쓰지 않는다 · 2017년 루프를 위해 넣었던 정렬을 뗀다
INSERT /*+ APPEND */ INTO W_ORD_GD (CUS_ID, ORD_DT, ORD_AMT, ORD_ST)
SELECT CUS_ID, ORD_DT, ORD_AMT, ORD_ST
  FROM T_ORD_BIG
 WHERE ORD_DT >= DATE '2023-01-01';

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

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |           |      1 |        |      0 |00:00:02.07 |    6123 |
|   1 |  LOAD AS SELECT                  | W_ORD_GD  |      1 |        |      0 |00:00:02.07 |    6123 |
|   2 |   OPTIMIZER STATISTICS GATHERING |           |      1 |    667K|    667K|00:00:00.55 |    5826 |
|*  3 |    TABLE ACCESS FULL             | T_ORD_BIG |      1 |    667K|    667K|00:00:00.20 |    5826 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("ORD_DT">=TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

모범 답안 서술

3번 줄 SORT ORDER BY가 666,964행을 고객순으로 정렬한다. 필요한 메모리(OMem)는 31MB인데 02시의 상한은 약 20MB라 20M (1), 곧 one-pass로 돌며 넘친 29MB를 TEMP에 정렬 묶음(run)으로 내려놓는다. 지난밤에는 다른 배치 둘이 TEMP 4GB 가운데 3.95GB를 쓰고 있어서 이 묶음을 내려놓을 자리가 없었고 ORA-01652로 멈췄다. 성공한 날에도 9.8초 가운데 7초 넘게가 이 정렬이다. 이 정렬은 지금의 2단계에 쓸모가 없다. 2단계는 HASH GROUP BY라서 행이 어떤 순서로 들어오든 고객별 해시 칸에 더해 갈 뿐 순서를 쓰지 않는다. 게다가 힙 테이블은 넣은 순서대로 읽힌다고 약속하지 않으므로, 순서가 필요한 쪽은 읽을 때 ORDER BY를 써야 한다. 주석은 2017년의 2단계, 곧 W_ORD_GD를 읽다가 고객이 바뀌는 지점에서 합계를 끊던 PL/SQL 루프를 위한 것이었다. 2021년에 2단계가 GROUP BY 한 문장으로 바뀌면서 1단계의 ORDER BY는 이유를 잃은 채 남았다. ORDER BY를 떼면 같은 행이 같은 테이블에 들어가고 2단계 결과도 같다. 정렬 줄이 사라져 TEMP를 쓰지 않고(29M → 0), 1단계는 9.8초 → 2.1초가 된다. TEMP를 늘리는 안은 오늘 밤의 실패는 막아도 필요 없는 정렬 667K행과 one-pass의 쓰기, 읽기를 그대로 둔다. 2년 치 범위라 주문이 늘수록 정렬도 커지고, 세 배치가 TEMP를 동시에 쓰는 구조도 그대로다. 줄일 수 있는 TEMP 사용자를 먼저 없애고, 그래도 모자라면 그때 늘린다.

해설

주석에도 유효기간이 있다: '고객순으로 넣어야 빠르다'는 2017년의 루프가 저장 순서에 기대던 시절의 판단이었다. 그 루프는 ORDER BY 없이 읽었으니 사실은 그때도 위험했다. 판단의 근거가 바뀌면 그 판단으로 넣은 코드도 다시 본다(교재 06화 힌트 유효기간 대장과 같은 생각이다).
정렬이 정말 필요한 적재도 있다: 적재한 테이블을 고객번호 인덱스로 범위 조회한다면 고객순 적재로 클러스터링 팩터를 좋게 만드는 것이 목적일 수 있다(교재 04화). 그때는 정렬 비용과 조회 이득을 숫자로 견주어 정하고 이유를 주석에 적는다. 이 배치의 2단계는 테이블 전체를 해시로 묶으므로 해당하지 않는다.
2단계가 ORD_ST = 'COMPLETE'만 읽으므로 1단계에서부터 COMPLETE만 옮기면 적재량도 10% 준다. 다만 다른 단계가 나머지 상태를 쓰는지 먼저 확인한다.
TEMP를 누가 얼마나 쓰는지는 V$TEMPSEG_USAGE와 V$SQL_WORKAREA_ACTIVE로 본다. 늘리기 전에 사용자 목록부터 뽑는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황