대시보드의 「월별 주문 금액 분포」가 새로 고칠 때마다 TEMP를 22MB 쓴다

기초SELECT한빛유통357 / 700

상황

경영지원팀 대시보드에 지난달 「월별 주문 금액 분포」 칸이 생겼다. 2022 ~ 2024년 36개월의 달마다 주문 금액 중앙값과 상위 10% 경계(90번째 백분위수)를 꺾은선으로 그린다. 평균은 고액 주문 몇 건에 끌려가니 분포를 보자는 박팀장의 요청이었다. 화면은 07시부터 19시까지 5분마다 새로 고친다. 그 뒤로 DBA가 뽑는 TEMP 사용 상위 목록에 이 SQL이 매번 오른다. 새로 고칠 때마다 TEMP를 22MB 쓰고 0.6초가 걸린다. 조민우는 "추세만 보는 그래프인데 원 단위까지 맞아야 하나"라고 묻는다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT DATE(전부 자정, 2022-01-01 ~ 2024-12-31) · ORD_AMT 1만 ~ 50만 원에 고르게 퍼져 있다(100원 단위) · 한 달 약 2만 7천 건
이 화면에 쓸 인덱스는 없다. 낮 시간 work area 하나의 상한(global memory bound)은 약 20MB다. 통계는 최신이다.
같은 두 숫자를 매월 1일 08시의 월 마감 보고서(임원 보고용)도 지난달 한 달 치로 쓴다.

원본 SQL

sql

SELECT TRUNC(ORD_DT, 'MM') AS ORD_MM,
       MEDIAN(ORD_AMT)                                       AS AMT_P50,
       PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY ORD_AMT) AS AMT_P90
  FROM T_ORD_BIG
 GROUP BY TRUNC(ORD_DT, 'MM')
 ORDER BY ORD_MM

실행계획 학습용 예시 · 정확한 백분위수와 근사 백분위수의 메모리 차이는 교재 환경 23ai의 100만 행 실측 비율

xplan

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |     36 |00:00:00.61 |    5831 |  4928 |   4928 |       |       |          |         |
|   1 |  SORT GROUP BY     |           |      1 |   1096 |     36 |00:00:00.61 |    5831 |  4928 |   4928 |    65M|  1709K|   20M (1)|      22M|
|   2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.05 |    5826 |     0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

문제

1번 줄이 TEMP를 쓰는 까닭을 중앙값과 백분위수를 구하는 방식, 그리고 OMem과 Used-Mem으로 설명하시오.
대시보드 그래프의 정렬 부담을 줄이도록 SQL을 고치고, 고친 뒤에도 그대로 남는 일량이 무엇인지 쓰시오.
고친 방식을 써도 되는 곳과 쓰면 안 되는 곳을 이 회사의 두 화면으로 구분하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 대시보드 추세 그래프 : 근사 백분위수 · DETERMINISTIC 으로 새로 고칠 때마다 같은 값이 나오게 한다
SELECT TRUNC(ORD_DT, 'MM') AS ORD_MM,
       APPROX_MEDIAN(ORD_AMT DETERMINISTIC)                                 AS AMT_P50,
       APPROX_PERCENTILE(0.9 DETERMINISTIC) WITHIN GROUP (ORDER BY ORD_AMT) AS AMT_P90
  FROM T_ORD_BIG
 GROUP BY TRUNC(ORD_DT, 'MM')
 ORDER BY ORD_MM;

-- 월 마감 보고서 : 정확한 값 · 지난달 한 달 치만 (약 2만 7천 줄 정렬이라 메모리 안에서 끝난다)
SELECT MEDIAN(ORD_AMT)                                       AS AMT_P50,
       PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY ORD_AMT) AS AMT_P90
  FROM T_ORD_BIG
 WHERE ORD_DT >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
   AND ORD_DT <  TRUNC(SYSDATE, 'MM');

개선 후 실행계획 학습용 예시 · 정확한 백분위수와 근사 백분위수의 메모리 차이는 교재 환경 23ai의 100만 행 실측 비율

xplan

[대시보드 · 고친 뒤]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |      1 |        |     36 |00:00:00.13 |    5826 |       |       |          |
|   1 |  SORT GROUP BY APPROX|           |      1 |   1096 |     36 |00:00:00.13 |    5826 |   243K|   243K|  215K (0)|
|   2 |   TABLE ACCESS FULL  | T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.05 |    5826 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------

모범 답안 서술

1번 줄 SORT GROUP BY는 달마다 주문 금액을 모두 줄 세운다. 중앙값과 90번째 백분위수는 '줄 세웠을 때 가운데와 90% 자리의 값'이라서 달의 금액을 전부 정렬해야 구할 수 있다. 그래서 100만 줄을 정렬하는 데 65MB(OMem)가 필요한데 낮 시간 상한이 약 20MB라 Used-Mem이 20M (1), 곧 one-pass로 돌며 22MB를 TEMP에 썼다가 다시 읽었다(Writes 4,928). 0.61초의 대부분이 이 정렬이고, 5분마다 새로 고치니 하루 144번 TEMP를 쓴다. 두 값이 같은 금액 순서를 쓰므로 정렬은 한 번이다. 대시보드 그래프는 APPROX_MEDIAN과 APPROX_PERCENTILE로 바꾼다. 근사 함수는 금액을 전부 줄 세우지 않고 달마다 분포의 작은 요약만 들고 있다가, 그 요약에서 백분위수를 어림한다. 1번 줄이 SORT GROUP BY APPROX로 바뀌어 메모리는 215K로 줄고 TEMP를 쓰지 않으며, 시간은 0.61초 → 0.13초다(TEMP 22MB → 0). 그대로 남는 일량은 읽는 블록 5,826이다. 근사가 줄이는 것은 정렬 메모리이지 읽는 양이 아니다. 대가는 값이 정확하지 않다는 것이다. 근사 백분위수는 정확한 값과 조금 다르다(이런 분포에서 재 보면 0.1% 안팎). 추세를 보는 그래프에는 충분하고, DETERMINISTIC을 붙여 새로 고칠 때마다 값이 흔들리지 않게 한다. 하지만 월 마감 보고서처럼 숫자가 문서에 남는 곳에는 정확한 값을 쓴다. 지난달 한 달 치만 정확한 함수로 구하면 약 2만 7천 줄 정렬이라 메모리 안에서 끝난다.

해설

COUNT(DISTINCT)도 같은 무리다: 그룹마다 서로 다른 값을 모두 기억해야 해서 메모리가 값의 수에 비례한다. APPROX_COUNT_DISTINCT는 작은 요약으로 센다. 다만 임원 보고 숫자처럼 오차를 받을 수 없는 곳에서는 쓰지 않는다(SORT-055).
정확한 값을 싸게 얻는 길: 금액이 100원 단위라 값의 종류가 4,901가지뿐이다. (달, 금액)으로 먼저 묶어 건수를 센 뒤(약 17만 6천 줄) 누적 건수로 가운데 자리를 찾으면 정확한 중앙값을 작은 정렬로 구할 수 있다. 그래프가 정확한 값을 고집하면 이 방식이나 야간 요약 표를 쓴다.
메모리를 늘리는 안: 상한이 65MB를 넘으면 TEMP는 사라지지만 100만 줄 정렬은 그대로이고, 주문이 늘면 다시 모자란다(교재 09화 §6).
근사 함수의 오차는 데이터 분포와 버전에 따라 다르다. 바꾸기 전에 지난 몇 달을 정확한 값과 나란히 뽑아 차이를 현업에 보여 주고 승인받는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 정확한 백분위수와 근사 백분위수의 메모리 차이는 교재 환경 23ai의 100만 행 실측 비율 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 정확한 백분위수와 근사 백분위수의 메모리 차이는 교재 환경 23ai의 100만 행 실측 비율 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황