어젯밤부터 정산 배치의 취소분 정리 단계가 열 배 넘게 느려졌다

중급DELETE한빛유통257 / 700

상황

야간 정산 배치는 T_ORD의 주문을 T_ORD_STLM으로 옮긴 뒤(적재 단계), 취소 주문의 정산 행을 지운다(정리 단계). 정리 단계는 늘 0.1초 안팎이었는데 어젯밤부터 1.9초가 걸렸다. 배치 담당 오과장: "SQL은 한 글자도 안 바꿨다. 바뀐 것은 어제 저녁 DBA가 통계 수집 작업을 22시로 옮긴 것뿐이다." 22시에 T_ORD_STLM은 저녁 테스트 배치가 TRUNCATE만 하고 끝난 빈 테이블이었다. 적재 단계는 23시에 테이블을 다시 채운다.

테이블과 인덱스

T_ORD_STLM 정산: 실제 100,000행 · 640블록 · 인덱스 없음 · 통계는 0행(22시 수집) · T_ORD를 ORD_SEQ 순으로 읽어 적재한다
T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) (높이 2) · 취소(ORD_ST = 'CANCEL') 2,000건 · 통계 최신
정리 단계가 지우는 행은 2,000행이다

원본 SQL

sql

DELETE FROM T_ORD_STLM s
 WHERE EXISTS (SELECT 1
                 FROM T_ORD o
                WHERE o.ORD_SEQ = s.ORD_SEQ
                  AND o.ORD_ST  = 'CANCEL')

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT              |            |      1 |        |      0 |00:00:01.91 |  307912 |
|   1 |  DELETE                       | T_ORD_STLM |      1 |        |      0 |00:00:01.91 |  307912 |
|   2 |   NESTED LOOPS SEMI           |            |      1 |      1 |   2000 |00:00:01.74 |  300640 |
|   3 |    TABLE ACCESS FULL          | T_ORD_STLM |      1 |      1 |    100K|00:00:00.03 |     640 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| T_ORD      |    100K|      1 |   2000 |00:00:01.52 |  300000 |
|*  5 |     INDEX UNIQUE SCAN         | PK_T_ORD   |    100K|      1 |    100K|00:00:00.61 |  200000 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("O"."ORD_ST"='CANCEL')
   5 - access("O"."ORD_SEQ"="S"."ORD_SEQ")

문제

30만 블록이 어디서 나오는지 실행계획의 Starts로 설명하시오. 옵티마이저가 이 세미 조인 방식을 고른 이유를 E-Rows로 설명하시오.
오늘 밤 배치를 위해 SQL을 고치고, 재발을 막는 방법을 함께 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 근본: 적재가 끝난 뒤 통계를 모은다 (배치 안의 적재 단계 끝에 둔다)
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORD_STLM');
END;
/
-- ② 통계를 바로 못 고치는 오늘 밤: 세미 조인 방식을 해시로 정한다
DELETE FROM T_ORD_STLM s
 WHERE EXISTS (SELECT /*+ UNNEST HASH_SJ */ 1
                 FROM T_ORD o
                WHERE o.ORD_SEQ = s.ORD_SEQ
                  AND o.ORD_ST  = 'CANCEL');

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

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |            |      1 |        |      0 |00:00:00.06 |    8808 |          |
|   1 |  DELETE               | T_ORD_STLM |      1 |        |      0 |00:00:00.06 |    8808 |          |
|*  2 |   HASH JOIN RIGHT SEMI|            |      1 |   2000 |   2000 |00:00:00.03 |    1536 | 1405K (0)|
|*  3 |    TABLE ACCESS FULL  | T_ORD      |      1 |   2000 |   2000 |00:00:00.01 |     896 |          |
|   4 |    TABLE ACCESS FULL  | T_ORD_STLM |      1 |    100K|    100K|00:00:00.01 |     640 |          |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."ORD_SEQ"="S"."ORD_SEQ")
   3 - filter("O"."ORD_ST"='CANCEL')

모범 답안 서술

2번 줄이 NL 세미 조인이고, 안쪽 4번과 5번 줄의 Starts가 100K다. 정산 10만 행을 읽어 가며 행마다 PK_T_ORD로 주문을 한 번씩 찾아가 취소인지 확인했다. 인덱스에서 20만 블록, 테이블에서 10만 블록이 나와 30만 블록이 되었고, 그렇게 찾은 10만 건 가운데 취소는 2,000건뿐이었다. 옵티마이저가 NL 세미 조인을 고른 것은 3번 줄의 E-Rows가 1이기 때문이다. 22시에 모은 통계는 테이블이 비어 있을 때의 것이라 정산이 0행이라고 믿었고, 바깥이 1행이면 주문 PK를 한 번 찾는 NL 세미 조인이 가장 싸다. 실제로는 23시 적재로 10만 행이 들어 있었다. SQL은 그대로인데 옵티마이저가 보는 숫자가 바뀐 것이다. 바깥이 10만 행이면 해시 세미 조인이 맞다. 취소 주문 2,000건을 찾으려고 T_ORD를 한 번(896블록) 읽어 해시 테이블에 올리고, 정산을 한 번(640블록) 훑으며 짝이 있는 행만 지운다. 오늘 밤은 서브쿼리에 UNNEST HASH_SJ로 방식을 정하고, 통계를 고친 뒤에는 힌트 없이도 같은 계획이 나온다. Buffers는 307,912 → 8,808로 준다(남은 7천여 블록은 2,000행을 지우는 몫이다). 재발을 막으려면 통계를 적재가 끝난 뒤에 모은다. 적재 단계의 마지막에 DBMS_STATS.GATHER_TABLE_STATS를 넣거나, 10만 행일 때의 대표 통계를 모아 잠가 두고 DBA의 정기 수집에서 이 테이블을 뺀다.

해설

세미 조인도 방식을 고른다: EXISTS가 펼쳐지면 NL 세미, 해시 세미, 머지 세미 중 하나가 된다. NL 세미는 바깥이 작고 안쪽에 인덱스가 있을 때, 해시 세미는 양쪽이 다 클 때 맞다. 옵티마이저는 E-Rows로 고르므로 바깥이 1행이라고 믿으면 NL 세미를 고른다.
힌트는 응급 처치: HASH_SJ는 오래된 힌트다. 서브쿼리에 QB_NAME을 붙이고 바깥에서 USE_HASH로 정하는 방법도 쓴다. 통계를 고친 뒤에는 힌트를 떼고, 떼기 전까지는 힌트 대장에 이유를 적어 둔다(교재 06화).
NL 세미가 맞는 날: 취소분만 따로 모은 작은 작업 테이블을 바깥으로 둔다면 NL 세미 조인이 맞다. 같은 SQL이라도 바깥 크기가 방식을 정한다.
통계 정책: 날마다 비우고 채우는 테이블은 수집 시각에 따라 통계가 0행이 된다. 교재 10화 §6의 재적재 사고와 같은 모양이다. 통계 수집 시각을 옮길 때는 그 시각에 비어 있는 테이블이 없는지부터 본다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황