PG사 요청으로 정산 파일 레코드를 300바이트로 늘린 뒤부터 추출 배치가 TEMP를 쓴다

중급SELECT한빛유통342 / 700

상황

매월 1일 03시에 도는 「PG 대사 파일 추출」은 그해 1월 1일부터 지난달 말까지의 구매 완료 주문을 주문번호순으로 고정 길이 레코드로 만들어 파일로 내린다. 지난달 PG사가 레코드 형식을 100바이트에서 300바이트(뒤쪽 207바이트는 예비 영역 공백)로 바꿔 달라고 했고, 개발자는 SQL의 마지막 RPAD 하나만 고쳤다. 이번 달 1일 추출은 0.9초에서 4.1초로 늘었고 TEMP를 85MB 썼다. 같은 시각에 다른 배치들이 돌아 work area 하나의 상한은 약 40MB다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_SEQ NUMBER(유일) · CUS_ID 'CUS_000123' · ORD_DT DATE(전부 자정) · ORD_ST(COMPLETE 90%) · ORD_AMT
2024-01-01 ~ 2024-11-30의 구매 완료 주문은 275,089건이다(테이블의 약 28%). 이 범위를 읽기에 알맞은 인덱스는 없고, ORD_SEQ로 시작하는 인덱스도 없다.
파일의 레코드는 주문번호순이어야 한다(PG사 대사 규약). 통계는 최신이다.

원본 SQL

sql

SELECT RPAD(TO_CHAR(ORD_SEQ), 20)
    || RPAD(CUS_ID, 40)
    || TO_CHAR(ORD_DT, 'YYYYMMDD')
    || LPAD(TO_CHAR(ORD_AMT), 15, '0')
    || RPAD(ORD_ST, 10)
    || RPAD(' ', 207) AS REC
  FROM T_ORD_BIG
 WHERE ORD_DT >= DATE '2024-01-01'
   AND ORD_DT <  DATE '2024-12-01'
   AND ORD_ST = 'COMPLETE'
 ORDER BY ORD_SEQ

실행계획 학습용 예시

xplan

[이번 달 1일 03:00 · 레코드 300바이트]
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | Used-Tmp|
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |    275K|00:00:04.12 |    5833 |       |       |          |         |
|   1 |  SORT ORDER BY     |           |      1 |    275K|    275K|00:00:04.12 |    5833 |    86M|  2654K|   40M (1)|      85M|
|*  2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |    275K|    275K|00:00:00.16 |    5826 |       |       |          |         |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("ORD_ST"='COMPLETE' AND "ORD_DT">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "ORD_DT"<TO_DATE(' 2024-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

[지난달 1일 · 레코드 100바이트]  1번 줄 OMem 31M · Used-Mem 28M (0) · Used-Tmp 없음 · A-Time 0.9초

문제

레코드 길이 하나를 바꿨을 뿐인데 정렬에 드는 메모리가 31MB에서 86MB로 늘어난 까닭을 정렬이 줄 세우는 행의 모양으로 설명하시오.
파일 내용과 순서가 바뀌지 않게 SQL을 고쳐 정렬이 다시 메모리 안에서 끝나게 하고, 고친 SQL이 의도대로 돌았는지 실행계획의 어느 줄로 확인할지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 줄 세우기는 원래 컬럼으로 (행 약 47바이트) · 300바이트 레코드는 정렬이 끝난 뒤에 만든다
SELECT RPAD(TO_CHAR(ORD_SEQ), 20)
    || RPAD(CUS_ID, 40)
    || TO_CHAR(ORD_DT, 'YYYYMMDD')
    || LPAD(TO_CHAR(ORD_AMT), 15, '0')
    || RPAD(ORD_ST, 10)
    || RPAD(' ', 207) AS REC
  FROM (SELECT /*+ NO_MERGE */ ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT, ORD_ST
          FROM T_ORD_BIG
         WHERE ORD_DT >= DATE '2024-01-01'
           AND ORD_DT <  DATE '2024-12-01'
           AND ORD_ST = 'COMPLETE'
         ORDER BY ORD_SEQ)

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

xplan

[이번 달 1일과 같은 부하]
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | Used-Tmp|
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |    275K|00:00:00.74 |    5826 |       |       |          |         |
|   1 |  VIEW               |           |      1 |    275K|    275K|00:00:00.74 |    5826 |       |       |          |         |
|   2 |   SORT ORDER BY     |           |      1 |    275K|    275K|00:00:00.69 |    5826 |    15M|  1490K|   13M (0)|         |
|*  3 |    TABLE ACCESS FULL| T_ORD_BIG |      1 |    275K|    275K|00:00:00.15 |    5826 |       |       |          |         |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("ORD_ST"='COMPLETE' AND "ORD_DT">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "ORD_DT"<TO_DATE(' 2024-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

모범 답안 서술

1번 줄 SORT ORDER BY가 줄 세우는 것은 정렬 키 ORD_SEQ만이 아니라 SELECT 절의 결과 전체다. 이 SQL은 RPAD와 ||로 300바이트 레코드를 먼저 만들고, 그 문자열을 행째로 들고 주문번호순으로 정렬한다. 275,089행 × 약 310바이트라서 필요한 메모리(OMem)가 86MB가 되었다. 레코드가 100바이트이던 지난달에는 31MB라서 상한 40MB 안에서 optimal로 끝났지만, 이번에는 상한을 넘어 40M (1), 곧 one-pass로 돌며 85MB를 TEMP(Used-Tmp)에 썼다가 다시 읽었다. 4.1초의 대부분이 이 쓰기와 읽기다. 예비 영역 207바이트의 공백까지 정렬에 실려 다닌 셈이다. 고친 SQL은 인라인 뷰 안에서 원래 컬럼 다섯 개(행 약 47바이트)로 주문번호순 정렬을 하고, 바깥에서 정렬된 행마다 300바이트 레코드를 만든다. 파일 내용과 순서는 같다. 정렬에 필요한 메모리가 86M → 15M로 줄어 optimal(13M (0))로 끝나고 TEMP는 쓰지 않는다. 시간은 4.1초 → 0.7초, Buffers는 5,833 → 5,826(TEMP를 쓰는 동안 붙던 7블록이 빠진다)이다. 인라인 뷰가 바깥 쿼리에 합쳐지면(뷰 병합) 다시 문자열을 들고 정렬할 수 있으므로 NO_MERGE 힌트로 막는다. 의도대로 돌았는지는 실행계획에서 1번 줄 VIEW가 2번 줄 SORT ORDER BY 위에 있는지, 2번 줄의 OMem이 15M 안팎인지로 확인한다.

해설

정렬은 SELECT 절의 결과를 들고 다닌다. 고정 길이 레코드, 화면용으로 이어 붙인 문자열, 긴 설명 컬럼처럼 넓은 값은 정렬이 끝난 뒤에 만들거나 붙인다. 이 문제의 답은 정렬하는 행 수가 아니라 행의 너비를 줄이는 것이다.
파일을 쓰는 프로그램이 공백을 채울 수 있다면 SQL은 원래 컬럼만 ORDER BY ORD_SEQ로 내보내고 레코드 조립은 프로그램에 맡겨도 된다. 정렬의 크기는 고친 SQL과 같다.
NO_MERGE 없이도 이 뷰가 병합되지 않는 경우가 많지만, 그것은 옵티마이저의 선택이라 버전이나 통계에 따라 바뀔 수 있다. 의도가 실행계획에 걸려 있는 곳에는 힌트로 의도를 적고 이유를 주석과 힌트 대장에 남긴다(교재 06화 힌트 유효기간 대장).
PGA를 올려 상한을 90MB 넘게 하는 것도 답처럼 보이지만, 추출 범위는 달마다 한 달 치씩 늘어 같은 상한에서 다시 모자라고, 같은 시각의 다른 배치가 쓸 메모리를 가져간다(교재 09화 §6).
바깥 쿼리에 ORDER BY를 다시 쓰면 문자열을 든 정렬이 한 번 더 생긴다. 바깥은 순서를 바꾸는 연산 없이 그대로 내보낸다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황