이관 대사의 건수 확인 한 줄이 인덱스만 읽는데 3초 가까이 걸린다

기초SELECT유통110 / 700

상황

본사는 매일 밤 판매상품(CF11)을 정보계로 옮긴 뒤, 원천 건수와 옮긴 건수를 맞춰 보는 대사 스크립트를 돌린다. 그 가운데 건수를 세는 단계 하나가 매일 3초 가까이 걸려 야간 작업표에서 눈에 띈다. 스크립트를 쓴 조민우는 이렇게 말했다. "테이블이 크니까 인덱스로 세라고 힌트를 넣었다." 아래는 이관 직후, 버퍼 캐시에 인덱스 블록이 거의 없을 때 잰 값이다.

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · 컬럼은 모두 NOT NULL
인덱스 CF11_PK (SLE_NO, ITM_NO): BLEVEL 2 · 리프 약 4,290블록 · 통계 최신 · 다른 인덱스 없음
db_file_multiblock_read_count = 128(교재 04화 실측 환경과 같다)
이관 작업은 테이블 블록만 읽으므로, 대사 단계가 시작될 때 인덱스 블록은 거의 디스크에 있다

원본 SQL

sql

SELECT /*+ INDEX(b CF11_PK) */ COUNT(*)
  FROM CF11 b

실행계획 학습용 예시 · 멀티블록 읽기 단위는 교재 04화 실측 환경

xplan

--------------------------------------------------------------------------------------------
| Id  | Operation        | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |      1 |        |      1 |00:00:02.84 |    4293 |  4291 |
|   1 |  SORT AGGREGATE  |         |      1 |      1 |      1 |00:00:02.84 |    4293 |  4291 |
|   2 |   INDEX FULL SCAN| CF11_PK |      1 |   1096K|   1096K|00:00:02.71 |    4293 |  4291 |
--------------------------------------------------------------------------------------------

[세션 통계 · 대기 이벤트 — 같은 실행]
  physical read total IO requests              4,291
  physical read total multi block requests         0
  db file sequential read                      4,291회 · 2.6초

문제

Buffers는 4,293블록으로 인덱스 크기만큼인데 2.8초가 걸리는 이유를 INDEX FULL SCAN이 블록을 읽는 방식과 세션 통계로 설명하시오.
결과(전체 건수)를 바꾸지 않고 같은 인덱스를 더 빨리 읽도록 SQL을 고치시오. 고친 뒤 Buffers와 I/O 요청 수가 각각 어떻게 달라지는지도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 순서가 필요 없는 건수 세기 — 인덱스 세그먼트를 순서 없이 멀티블록으로 훑는다
SELECT /*+ INDEX_FFS(b CF11_PK) */ COUNT(*)
  FROM CF11 b;
-- 힌트를 아예 떼도 옵티마이저가 INDEX FAST FULL SCAN 을 고른다

개선 후 실행계획 학습용 예시 · 멀티블록 읽기 단위는 교재 04화 실측 환경

xplan

------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |      1 |        |      1 |00:00:00.31 |    4312 |  4306 |
|   1 |  SORT AGGREGATE       |         |      1 |      1 |      1 |00:00:00.31 |    4312 |  4306 |
|   2 |   INDEX FAST FULL SCAN| CF11_PK |      1 |   1096K|   1096K|00:00:00.19 |    4312 |  4306 |
------------------------------------------------------------------------------------------------

[세션 통계 · 대기 이벤트]
  physical read total IO requests                 36
  physical read total multi block requests        34
  db file scattered read                        34회 · 0.2초

모범 답안 서술

2번 줄은 INDEX FULL SCAN이다. 이 스캔은 리프 블록을 키 순서대로 따라가므로 한 번에 한 블록씩 읽는다(싱글블록 읽기, db file sequential read). 리프 블록끼리는 키 순서로 이어져 있을 뿐 디스크에서 이웃해 있다는 보장이 없어서 묶어 읽을 수 없다. 그래서 Buffers 4,293만큼 I/O 요청이 4,291번 생겼고, 2.84초 가운데 2.6초가 그 대기다. 건수를 세는 데에는 키 순서가 필요 없다. 힌트를 INDEX_FFS로 바꾸면(힌트를 떼도 옵티마이저가 고른다) INDEX FAST FULL SCAN이 되어, 인덱스 세그먼트를 테이블 전체 스캔처럼 한 번에 128블록씩 멀티블록으로 읽는다(db file scattered read). 브랜치 블록까지 다 읽으므로 Buffers는 4,293 → 약 4,312로 오히려 조금 늘지만, I/O 요청은 4,291번 → 약 36번으로 줄고 시간은 2.84초 → 약 0.31초가 된다. 두 방식 모두 테이블(8,204블록)에는 가지 않는다. CF11_PK의 컬럼이 NOT NULL이라 모든 행이 인덱스에 들어 있다는 것이 보장되므로, 인덱스 항목만 세어도 COUNT(*)가 맞다.

해설

Buffers가 같아도 시간은 다르다. Buffers는 블록을 논리적으로 몇 번 만졌는지만 센다. 디스크에서 몇 번에 나눠 가져왔는지는 physical read total IO requests나 대기 이벤트로 본다(교재 04화 §2: 같은 2만 2천 블록이 186번 요청으로 끝나기도 하고 18,934번 요청이 되기도 한다).
같은 INDEX FULL SCAN이 옳은 경우도 있다. 결과를 키 순서대로 내야 해서 정렬을 피하고 싶을 때다. 순서가 필요 없는 건수 세기에 쓰면 순서를 지키는 비용(한 블록씩 읽기)만 치른다.
INDEX FAST FULL SCAN은 병렬로 나눠 읽을 수도 있다(PARALLEL_INDEX). 파티션되지 않은 인덱스의 INDEX FULL SCAN과 RANGE SCAN은 병렬로 나누지 못한다. 이 규모(4,300블록)에서는 직렬 FFS로 충분하다.
버퍼 캐시에 인덱스 블록이 다 올라와 있는 낮에는 두 방식의 차이가 거의 보이지 않는다. 낮에 재 본 개발자가 문제를 못 느끼는 이유다. 측정값을 적을 때는 캐시 상태를 함께 적는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 멀티블록 읽기 단위는 교재 04화 실측 환경 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 멀티블록 읽기 단위는 교재 04화 실측 환경 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황