19c로 올린 다음 날 판매 SQL 37개에서 실행계획 변경 경보가 울렸다

기초SELECT유통105 / 700

상황

유통 본사 DB를 11g에서 19c로 올린 다음 날, 성능 모니터링 도구가 판매 SQL 37개에 「실행계획 변경」 경보를 띄웠다. 거의 모두 TABLE ACCESS BY INDEX ROWID가 TABLE ACCESS BY INDEX ROWID BATCHED로 바뀐 것이다. 인프라팀은 이렇게 제안했다. "OPTIMIZER_FEATURES_ENABLE을 '11.2.0.4'로 되돌리면 경보가 다 사라진다. 오늘 밤 반영하겠다." 오과장이 대표로 「일별 판매 목록」 SQL의 전후 계획을 보내왔다. 캐시를 비우고 잰 값이다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · 판매 날짜순으로 쌓였다 · 2023-06-01 706건이 13블록에 모여 있다
인덱스 IX_CF10_SLE_DT (SLE_DT): BLEVEL 2 · 리프 1,393 · CLUSTERING_FACTOR 9,921(교재 03화 실측) · 통계 최신

원본 SQL

sql

SELECT SLE_NO, SLE_HMS, SHP_NO
  FROM CF10
 WHERE SLE_DT = :v_dt
 ORDER BY SLE_NO

실행계획 학습용 예시 · 건수와 블록은 적재 데이터(CF10) 그대로

xplan

[11g]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |    706 |00:00:00.05 |      18 |    18 |
|   1 |  SORT ORDER BY                       |                |      1 |    685 |    706 |00:00:00.05 |      18 |    18 |
|   2 |   TABLE ACCESS BY INDEX ROWID        | CF10           |      1 |    685 |    706 |00:00:00.05 |      18 |    18 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |    685 |    706 |00:00:00.01 |       5 |     5 |
-------------------------------------------------------------------------------------------------------------------------

[19c]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |    706 |00:00:00.02 |      18 |    18 |
|   1 |  SORT ORDER BY                       |                |      1 |    685 |    706 |00:00:00.02 |      18 |    18 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    685 |    706 |00:00:00.02 |      18 |    18 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |    685 |    706 |00:00:00.01 |       5 |     5 |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("SLE_DT"=:V_DT)

[대기 이벤트 · 테이블 블록 13개]  11g: db file sequential read 13번 · 19c: db file parallel read 2번

문제

두 계획에서 무엇이 바뀌었고 무엇이 그대로인지 Buffers, Reads, A-Time, 대기 이벤트로 설명하시오. BATCHED가 하는 일을 설명하시오.
인프라팀의 제안을 받아들일지 판단하시오. 그 대신 경보 37개 가운데 무엇을 골라 고쳐야 하는지 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고치지 않는다 — 원래 SORT ORDER BY 가 있던 SQL 이라 BATCHED 로 잃는 것이 없다
SELECT SLE_NO, SLE_HMS, SHP_NO
  FROM CF10
 WHERE SLE_DT = :v_dt
 ORDER BY SLE_NO;
-- 경보 37개 가운데 ① ORDER BY 없이 인덱스 순서에 기대는 SQL ② 계획에 SORT 가 새로 붙은 SQL 만 골라
--   ① 은 ORDER BY 를 쓰고 ② 는 그 SQL 에만 NO_BATCH_TABLE_ACCESS_BY_ROWID 힌트를 단다

모범 답안 서술

바뀐 줄은 2번 하나다. TABLE ACCESS BY INDEX ROWID가 BATCHED로 바뀌었다. 그대로인 것은 읽은 블록 수다. Buffers 18(인덱스 5, 테이블 13)과 디스크에서 읽은 블록 Reads 18이 두 계획에서 같다. 바뀐 것은 그 블록을 읽는 방식이다. 11g는 인덱스에서 ROWID를 하나 얻을 때마다 테이블 블록을 하나씩 디스크에 요청했다(db file sequential read 13번). 19c의 BATCHED는 ROWID를 모아 두었다가 테이블 블록 여러 개를 한꺼번에 요청한다(db file parallel read 2번). 그래서 기다리는 시간이 줄어 A-Time이 0.05 → 0.02초가 됐다. 이 SQL에서 BATCHED는 손해가 아니라 이득이다. 인프라팀의 제안은 받아들이지 않는다. OPTIMIZER_FEATURES_ENABLE을 '11.2.0.4'로 되돌리면 BATCHED뿐 아니라 그 뒤 버전에서 들어온 옵티마이저 기능이 모든 SQL에서 함께 꺼진다. 경보를 없애려고 시스템 전체의 계획을 다시 흔드는 셈이다. 경보는 계획이 바뀌었다는 신호일 뿐, 나빠졌다는 판정이 아니다. 경보 37개 가운데 고칠 것은 두 모양뿐이다. 하나는 ORDER BY 없이 인덱스 순서에 기대는 SQL이다. BATCHED로 읽은 행은 인덱스 순서라는 보장이 없어서 결과 순서가 흔들린다(ACC-025). ORDER BY를 써서 고친다. 다른 하나는 BATCHED를 고르면서 SORT ORDER BY가 새로 붙은 SQL이다. 정렬 때문에 부분범위 처리가 사라져 첫 화면이 늦어진다(ACC-027). 그 SQL에만 NO_BATCH_TABLE_ACCESS_BY_ROWID 힌트를 단다. 이 「일별 판매 목록」처럼 원래 SORT가 있던 SQL은 그대로 둔다.

해설

함정: 실행계획 해시값이 바뀌었다는 것만으로 되돌리려 하기 쉽다. 먼저 Buffers와 A-Time을 전후로 비교한다. 이 SQL은 같은 블록을 더 빨리 읽게 됐다.
BATCHED는 블록 수를 줄이지 않는다. 테이블 랜덤 액세스의 블록 수를 줄이는 것은 CF(쌓인 순서), 커버링 인덱스, 테이블 필터 컬럼을 인덱스로 옮기는 것이다. BATCHED는 같은 블록을 디스크에서 덜 기다리며 읽게 할 뿐이다.
이 테이블은 날짜순으로 쌓여 하루치가 13블록에 모여 있다. CF가 좋은 테이블에서는 이웃 블록을 미리 읽는 효과가 BATCHED 없이도 있어 차이가 작다. CF가 나쁜 테이블일수록 BATCHED의 이득이 크다.
업그레이드 경보를 다룰 때는 SPM(SQL Plan Baseline)으로 옛 계획을 붙잡아 두고 하나씩 진화시키는 방법도 있다(OPT 단원).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수와 블록은 적재 데이터(CF10) 그대로 ​

문제 ​

모범 답안 SQL ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황

테이블과 인덱스

원본 SQL

실행계획 학습용 예시 · 건수와 블록은 적재 데이터(CF10) 그대로

문제

모범 답안 SQL

모범 답안 서술

해설

관련 교재