늘 0건인 「빈 영수증 점검」이 10분마다 1.6초씩 돌며 TEMP를 쓴다

중급SELECT버거271 / 700

상황

본사 운영실의 「빈 영수증 점검」은 POS가 주문 헤더만 보내고 주문상품을 보내지 못한 주문을 찾는다(전송 오류 감지). 운영실 PC가 10분마다 그날 주문을 점검하는데, 결과는 거의 늘 0건이다. 그런데 한 번에 1.6초가 걸리고, 점검이 돌 때마다 DB 서버의 TEMP 사용량 그래프가 솟는다. 오과장: "찾을 게 없으면 빨리 끝나야 하는 것 아닌가."

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS) · 날짜순 적재 · 하루 약 1,370건(아래 계획은 :v_dt = '20131230', 1,371건)
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID): BLEVEL 2 · 리프 약 6,200블록 · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · 주문당 1 ~ 4줄
ORD_NO는 주문 일시 순으로 매겨져 있어, 하루치 주문의 주문상품은 ORD_ITEM_PK의 이웃한 리프 몇 개에 모여 있다
통계 최신 · PGA는 자동 관리(정렬 하나에 약 20MB까지 메모리)

원본 SQL

sql

SELECT ORD_NO FROM ORD WHERE ORD_DT = :v_dt
MINUS
SELECT ORD_NO FROM ORD_ITEM

실행계획 학습용 예시

xplan

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Writes | Used-Mem | Used-Tmp |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |      1 |        |      0 |00:00:01.62 |    6241 |  4870 |   4870 |          |          |
|   1 |  MINUS                                |             |      1 |        |      0 |00:00:01.62 |    6241 |  4870 |   4870 |          |          |
|   2 |   SORT UNIQUE                         |             |      1 |   1370 |   1371 |00:00:00.01 |      37 |     0 |      0 |   59K (0)|          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD         |      1 |   1370 |   1371 |00:00:00.01 |      37 |     0 |      0 |          |          |
|*  4 |     INDEX RANGE SCAN                  | ORD_X01     |      1 |   1370 |   1371 |00:00:00.01 |       6 |     0 |      0 |          |          |
|   5 |   SORT UNIQUE                         |             |      1 |   2472K|   1000K|00:00:01.60 |    6204 |  4870 |   4870 | 2148K (1)|    39M   |
|   6 |    INDEX FAST FULL SCAN               | ORD_ITEM_PK |      1 |   2472K|   2472K|00:00:00.23 |    6204 |     0 |      0 |          |          |
----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("ORD_DT"=:V_DT)

문제

결과가 0건인데도 6,241블록을 읽고 TEMP를 쓰는 까닭을 실행계획 5번과 6번 줄로 설명하시오. MINUS의 두 갈래가 서로 무엇을 모르는지도 설명하시오.
그날 주문의 주문상품만 확인하도록 SQL을 고치고, 고친 계획이 왜 주문마다 첫 줄만 보고 멈출 수 있는지 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 그날 주문마다 주문상품이 한 줄이라도 있는지만 본다 (주문번호가 서브쿼리로 넘어간다)
SELECT o.ORD_NO
  FROM ORD o
 WHERE o.ORD_DT = :v_dt
   AND NOT EXISTS (SELECT 1 FROM ORD_ITEM i WHERE i.ORD_NO = o.ORD_NO);

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

xplan

----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |      1 |        |      0 |00:00:00.01 |    1411 |
|   1 |  NESTED LOOPS ANTI                   |             |      1 |      1 |      0 |00:00:00.01 |    1411 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD         |      1 |   1370 |   1371 |00:00:00.01 |      37 |
|*  3 |    INDEX RANGE SCAN                  | ORD_X01     |      1 |   1370 |   1371 |00:00:00.01 |       6 |
|*  4 |   INDEX RANGE SCAN                   | ORD_ITEM_PK |   1371 |      2 |   1371 |00:00:00.01 |    1374 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("O"."ORD_DT"=:V_DT)
   4 - access("I"."ORD_NO"="O"."ORD_NO")

모범 답안 서술

6번 줄이 ORD_ITEM_PK를 처음부터 끝까지(INDEX FAST FULL SCAN) 읽어 주문상품 2,472K 행을 꺼냈고, 5번 줄의 SORT UNIQUE가 그것을 주문번호 100만 개로 정렬하며 중복을 지웠다. 정렬할 양이 메모리(약 20MB)를 넘어 TEMP에 39MB를 쓰고 다시 읽었다(Used-Mem의 (1), Reads와 Writes 4,870). 6,241블록 가운데 6,204블록과 1.6초의 거의 전부가 이 갈래의 몫이다. 그날 주문은 1,371건뿐이다. MINUS는 두 갈래를 각각 따로 끝까지 만든 뒤 비교하는 집합 연산이다. 아래 갈래 SELECT ORD_NO FROM ORD_ITEM은 위 갈래에 어느 주문번호가 있는지 모르므로 2년치 전부를 만들고, 위 갈래도 아래에 무엇이 있는지 모른 채 따로 정렬한다. 두 갈래 사이에는 상관 조건을 건넬 길이 없다. NOT EXISTS로 쓰면 바깥 주문의 ORD_NO가 서브쿼리에 상관 조건으로 들어간다. 옵티마이저는 이것을 NESTED LOOPS ANTI로 풀어, 그날 주문 1,371건마다 ORD_ITEM_PK(선두 컬럼 ORD_NO)를 한 번씩 찾아간다. 안티 조인은 짝을 하나 찾으면 그 주문을 버리고 바로 다음 주문으로 넘어가므로 주문상품을 끝까지 읽을 필요가 없고 정렬도 없다. Buffers는 6,241 → 1,411, 시간은 1.62초 → 0.01초이고 TEMP는 쓰지 않는다. 결과는 둘 다 0건이다.

해설

MINUS를 남긴다면 갈래마다 범위를 직접 적는다: 아래 갈래에도 WHERE ORD_DT = :v_dt를 적으면 ORD_ITEM_X01로 그날 주문상품 약 3,390줄만 읽어 100블록 안팎이 된다. 다만 ORD_ITEM.ORD_DT가 ORD.ORD_DT의 복사본이라는 전제에 기댄다. 두 값이 어긋나는 주문(자정 무렵 전송 지연 같은 것)이 있으면 빈 영수증으로 잘못 잡힌다.
옵티마이저는 MINUS를 안티 조인으로 바꿔 주지 않는다: 집합 연산을 조인으로 바꾸는 변환(SET_TO_JOIN)은 기본으로 꺼져 있다(_convert_set_to_join). 게다가 MINUS는 결과의 중복을 지우고 NULL끼리 같다고 보므로 NOT EXISTS와 늘 뜻이 같지도 않다. 이 문제에서는 ORD_NO가 PK라 같다.
해시 안티 조인이 아닌 까닭: 서브쿼리에 날짜 조건이 없으므로 해시 안티 조인을 고르면 주문상품 전체(6,204블록)를 읽어야 한다. 바깥이 1,371건으로 작고 안쪽에 조인 키가 선두인 인덱스가 있으니 NL 안티 조인이 맞다(교재 06화).
하루 144번(10분마다)이면 6,241 × 144 = 약 90만 블록과 TEMP 쓰기 144번이 1,411 × 144 = 약 20만 블록으로 준다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황