「고액 판매 TOP 100」을 월요일 아침에 열면 TEMP 경보가 울린다

모범 답안 SQL ​

-- 정렬 바로 바깥에서 100 줄로 자른다 → SORT ORDER BY STOPKEY (상위 100 줄만 책상에)
-- 동점이면 PK (SLE_NO, ITM_NO) 순 : 100번째 자리에 같은 금액이 걸려도 결과가 정해진다
SELECT SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC, SLE_AMT
  FROM (SELECT SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC,
               SLE_QTY * SLE_UPRC AS SLE_AMT
          FROM CF11
         ORDER BY SLE_QTY * SLE_UPRC DESC, SLE_NO, ITM_NO)
 WHERE ROWNUM <= 100

개선 후 실행계획 학습용 예시 · 메모리 크기는 교재 09화 실측 규모 ​

[월요일 09:01 · 같은 부하]
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |      1 |        |    100 |00:00:00.31 |    8204 |       |          |         |
|*  1 |  COUNT STOPKEY          |      |      1 |        |    100 |00:00:00.31 |    8204 |       |          |         |
|   2 |   VIEW                  |      |      1 |   1096K|    100 |00:00:00.31 |    8204 |       |          |         |
|*  3 |    SORT ORDER BY STOPKEY|      |      1 |   1096K|    100 |00:00:00.31 |    8204 |    27K|   24K (0)|         |
|   4 |     TABLE ACCESS FULL   | CF11 |      1 |   1096K|   1096K|00:00:00.12 |    8204 |       |          |         |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=100)
   3 - filter(ROWNUM<=100)

모범 답안 서술 ​

4번 줄 SORT ORDER BY가 1,096,708행을 전부 정렬한다. 필요한 메모리(OMem)는 61M인데 월요일 9시에는 work area 하나가 쓸 수 있는 메모리가 약 20MB라서 Used-Mem이 20M (1), 곧 one-pass로 돌았다. 들어가지 못한 55M는 TEMP(Used-Tmp)에 썼다가 다시 읽었고, 2.4초의 대부분이 여기서 나온다. 12명이 동시에 열면 TEMP도 그만큼 쓴다. 한가한 화요일에 0.9초로 끝나는 것은 같은 정렬이 메모리 안(optimal)에서 끝나기 때문일 뿐이다. 전체를 정렬하는 까닭은 번호를 거르는 위치에 있다. 가운데 뷰가 번호(rn)를 붙이고 바깥에서 rn <= 100으로 거르면, 옵티마이저는 정렬하는 쪽에서 100줄만 필요하다는 것을 알 수 없다. 2번 줄이 COUNT STOPKEY가 아니라 COUNT이고 A-Rows가 1096K인 것이 그 증거다. ROWNUM <= 100을 정렬 바로 바깥에 두면 Top-N 정렬(SORT ORDER BY STOPKEY)이 된다. 입력을 넘기면서 지금까지 본 것 중 상위 100줄만 책상에 두고 나머지는 보자마자 버리므로, 메모리는 약 24K로 줄고 TEMP는 쓰지 않는다(0.31초). 동점이 100번째에 걸려도 결과가 정해지도록 정렬 끝에 PK (SLE_NO, ITM_NO)를 붙였다. Buffers는 고친 뒤에도 8,204로 그대로다. Top-N 정렬은 메모리를 줄일 뿐이고, 금액 순서로 줄 선 목록이 없으니 상위 100줄을 알려면 여전히 전부 읽어야 한다. 읽는 양까지 줄이려면 SLE_QTY * SLE_UPRC의 함수 기반 인덱스(내림차순)를 만들거나 금액 컬럼을 따로 두고 인덱스를 만들어야 한다. 하지만 판매 줄이 들어올 때마다 그 인덱스를 고치는 비용이 들고, 이 화면은 주 한 번 12명이 여는 화면이다. 8,204블록 × 12번이면 한 주에 10만 블록 남짓이라 인덱스를 만들 가치는 없다고 판단한다. 이 화면의 답은 메모리와 TEMP를 줄이는 데서 끝난다.

해설 ​

• 회사 공통 틀이 문제다: 페이징 틀이 '번호는 가운데, 거르기는 바깥' 모양이면 그 틀을 쓴 화면 전부가 같은 일을 한다. 안쪽에 ROWNUM <= :끝번호를 넣는 틀로 바꾸는 것이 화면 하나를 고치는 것보다 낫다(교재 09화, 강도현의 2019-05-20 커밋).
• Top-N의 책상은 N에 비례한다. 같은 SQL로 '상위 10만 줄 내려받기'를 하면 다시 수십 MB가 든다(교재 09화 §2의 페이지별 Used-Mem).
• PGA를 늘리면 one-pass가 optimal로 바뀔 뿐 1,096,708행 정렬은 그대로다. 메모리는 장소만 바꾸고 일량은 못 바꾼다(교재 09화 §6).
• FETCH FIRST 100 ROWS ONLY나 ROW_NUMBER() OVER (ORDER BY …) <= 100으로 써도 Top-N 정렬(WINDOW SORT PUSHED RANK)이 된다. 다만 번호 조건을 rn <= 100 OR :all = 'Y'처럼 다른 조건과 섞으면 다시 전체 정렬로 돌아갈 수 있다.

관련 교재 ​

• 09화 · Top-N 정렬과 STOPKEY
• 09화 · 정렬의 work area 3등급과 절벽