재고 이력을 IOT로 바꾼 뒤 상품 재고 추이는 빨라졌는데, 일자별 재고 점검은 9블록에서 1,640블록이 됐다

중급SELECT유통126 / 700

상황

본사 레거시 유통은 매일 밤 매장별 상품 재고를 한 줄씩 남기는 재고 이력 테이블을 둔다. 점장 앱의 「상품 재고 추이」(매장 하나, 상품 하나, 석 달)가 느려서, 석 달 전 오과장이 이 테이블을 PK 순서로 저장하는 IOT로 다시 만들었다. 그 화면은 93블록에서 3블록이 됐다. 그런데 재고 담당이 매일 아침 여는 「일자별 재고 점검」(하루 545줄)은 IOT로 바꾼 직후 9블록에서 550블록이 됐고, 지금은 1,640블록을 읽는다. 본사 담당 여럿이 하루 300번쯤 연다.

테이블과 인덱스

CF18_HIS 일별상품재고 이력: CF18과 같은 컬럼(SHP_NO · ITM_NO · INV_BS_DT VARCHAR2(8) · INV_QTY)으로 2년 치를 쌓았다(이 문제를 위해 둔 파생 테이블) · 매장 5곳 × 상품 109개 × 730일 = 397,850행 · 하루 545줄
석 달 전 IOT로 다시 만들었다 · PK CF18_HIS_PK (SHP_NO, ITM_NO, INV_BS_DT) · BLEVEL 2 · 리프는 만들 때 1,530블록, 지금 약 2,300블록
밤마다 545줄이 매장과 상품 구간마다 끝에 하나씩, 즉 IOT의 가운데 545곳에 들어간다
보조 인덱스 CF18_HIS_X01 (INV_BS_DT): IOT와 함께 만들었다 · BLEVEL 2 · 리프 약 1,900블록 · PCT_DIRECT_ACCESS는 만들 때 100, 지금 38 · 통계는 매주 일요일에 모은다
IOT로 바꾸기 전에는 힙 테이블에 같은 PK와 (INV_BS_DT) 인덱스가 있었고, 하루 545줄은 밤에 한꺼번에 들어가 4블록에 모여 있었다

원본 SQL

sql

SELECT SHP_NO, ITM_NO, INV_QTY
  FROM CF18_HIS
 WHERE INV_BS_DT = :v_dt
 ORDER BY SHP_NO, ITM_NO

실행계획 학습용 예시 · 이력 테이블은 CF18을 2년 치로 쌓았다고 둔 파생 테이블

xplan

[지금 · PCT_DIRECT_ACCESS 38]
----------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |      1 |        |    545 |00:00:00.01 |    1640 |
|   1 |  SORT ORDER BY        |              |      1 |    545 |    545 |00:00:00.01 |    1640 |
|   2 |   INDEX UNIQUE SCAN   | CF18_HIS_PK  |      1 |    545 |    545 |00:00:00.01 |    1640 |
|*  3 |    INDEX RANGE SCAN   | CF18_HIS_X01 |      1 |    545 |    545 |00:00:00.01 |       5 |
----------------------------------------------------------------------------------------------

[IOT로 바꾼 다음 날 · PCT_DIRECT_ACCESS 100]
----------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |      1 |        |    545 |00:00:00.01 |     550 |
|   1 |  SORT ORDER BY        |              |      1 |    545 |    545 |00:00:00.01 |     550 |
|   2 |   INDEX UNIQUE SCAN   | CF18_HIS_PK  |      1 |    545 |    545 |00:00:00.01 |     550 |
|*  3 |    INDEX RANGE SCAN   | CF18_HIS_X01 |      1 |    545 |    545 |00:00:00.01 |       5 |
----------------------------------------------------------------------------------------------

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

[USER_INDEXES]
INDEX_NAME      PCT_DIRECT_ACCESS  IOT_REDUNDANT_PKEY_ELIM
CF18_HIS_X01                   38  YES

문제

3번 줄로 545줄의 위치를 찾은 뒤 2번 줄이 1,640블록을 읽는 이유를, IOT의 보조 인덱스가 행을 가리키는 방식과 PCT_DIRECT_ACCESS로 설명하시오. 힙 테이블일 때 9블록이던 SQL이 IOT로 바꾼 다음 날에도 550블록이었던 이유도 설명하시오.
IOT는 그대로 두고 이 점검이 IOT를 찾아가지 않도록 고치시오. 추측 주소(physical guess)를 갱신하는 방법과 비교하여 고른 방법의 대가를 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 보조 인덱스의 논리 ROWID 에는 PK (SHP_NO, ITM_NO, INV_BS_DT) 가 들어 있다
-- → INV_QTY 만 더하면 점검 SQL 이 쓰는 컬럼이 보조 인덱스에 다 있다 (IOT 를 찾아가지 않는다)
CREATE INDEX CF18_HIS_X02 ON CF18_HIS (INV_BS_DT, INV_QTY) ONLINE;
DROP INDEX CF18_HIS_X01;

SELECT SHP_NO, ITM_NO, INV_QTY        -- SQL 은 그대로
  FROM CF18_HIS
 WHERE INV_BS_DT = :v_dt
 ORDER BY SHP_NO, ITM_NO;

-- 비교한 방법 : 추측 주소만 고친다 (밤마다 분할이 생기면 다시 낡는다)
-- ALTER INDEX CF18_HIS_X01 UPDATE BLOCK REFERENCES;
-- EXEC DBMS_STATS.GATHER_INDEX_STATS(USER, 'CF18_HIS_X01');

개선 후 실행계획 학습용 예시 · 이력 테이블은 CF18을 2년 치로 쌓았다고 둔 파생 테이블

xplan

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |      1 |        |    545 |00:00:00.01 |       6 |
|   1 |  SORT ORDER BY        |              |      1 |    545 |    545 |00:00:00.01 |       6 |
|*  2 |   INDEX RANGE SCAN    | CF18_HIS_X02 |      1 |    545 |    545 |00:00:00.01 |       6 |
----------------------------------------------------------------------------------------------
   2 - access("INV_BS_DT"=:V_DT)

모범 답안 서술

IOT의 행은 리프 블록이 분할되면 다른 블록으로 옮겨 가므로, 보조 인덱스는 ROWID 대신 논리 ROWID를 담는다. 논리 ROWID는 PK 값과, 인덱스를 만들 때 행이 있던 블록 주소(physical guess, 추측 주소)로 되어 있다. PCT_DIRECT_ACCESS는 그 추측이 맞는 비율이다. 통계상 이 값이 100이면 오라클은 추측 주소로 바로 가고, 100 미만이면 추측을 쓰지 않고 PK로 IOT를 처음부터 탐색한다. 석 달 동안 밤마다 545줄이 IOT 가운데 545곳에 들어가면서 리프가 분할되었고(1,530 → 약 2,300블록), 옮겨 간 행이 늘어 PCT_DIRECT_ACCESS가 38로 떨어졌다. 그래서 545줄마다 PK를 루트, 브랜치, 리프 3블록씩 탐색한다. 545 × 3 = 1,635블록에 보조 인덱스 5블록을 더해 1,640이다. IOT로 바꾼 다음 날에도 550블록이었던 것은 IOT가 PK (SHP_NO, ITM_NO, INV_BS_DT) 순서로 저장되기 때문이다. 같은 날짜의 545줄은 매장과 상품 구간마다 하나씩, 리프 545개에 흩어진다. 추측이 다 맞아도 545블록을 읽는다. 힙 테이블에서는 밤에 한꺼번에 들어간 545줄이 4블록에 모여 있었다. IOT는 PK 순서 하나만 좋게 만들고, 다른 순서로 읽는 SQL은 나빠진다. 보조 인덱스의 논리 ROWID에는 PK 컬럼 SHP_NO, ITM_NO, INV_BS_DT가 이미 들어 있다. 그래서 INV_QTY만 더해 (INV_BS_DT, INV_QTY)로 다시 만들면 점검 SQL이 쓰는 컬럼이 보조 인덱스에 다 있고, IOT를 찾아가지 않는다. 하루치 545항목은 리프 3블록 남짓이라 Buffers 1,640 → 6이다. 하루 300번이면 약 49만 → 1,800블록이다. 추측 주소에 기대지 않으므로 분할이 생겨도 나빠지지 않는다. 비교한 방법은 ALTER INDEX … UPDATE BLOCK REFERENCES로 추측 주소를 고친 뒤 통계를 다시 모아 PCT_DIRECT_ACCESS를 100으로 올리는 것이다. 그러면 550블록으로 돌아가지만, 밤마다 분할이 생기므로 다시 낡고 갱신과 통계 수집을 주기적으로 해야 한다. 고른 방법의 대가는 보조 인덱스가 INV_QTY 한 컬럼만큼 커지는 것이다. 인덱스 수는 그대로라 밤 적재 비용은 거의 같고, 재고를 고치는 UPDATE가 생기면 그때마다 X02도 고쳐야 한다.

해설

함정: 추측 주소를 고치고 통계를 다시 모으지 않으면 PCT_DIRECT_ACCESS가 38 그대로라서 오라클은 여전히 PK로 탐색한다. 반대로 추측이 대부분 맞는데 통계를 새로 모으는 바람에 100 미만으로 떨어져 PK 탐색으로 바뀌는 일도 있다.
IOT_REDUNDANT_PKEY_ELIM이 YES인 것은 보조 인덱스 키(INV_BS_DT)와 겹치는 PK 컬럼을 오라클이 한 번만 저장했다는 뜻이다.
이 점검을 위해 IOT를 버리고 힙 테이블로 돌아가면 점검은 9블록이 되지만, 하루 수천 번 여는 「상품 재고 추이」가 93블록으로 돌아간다. 구조는 가장 자주 읽는 순서에 맞추고, 다른 순서는 보조 인덱스로 받는다.
밤마다 가운데에 넣는 IOT는 리프가 반씩 나뉘며 커진다(1,530 → 2,300). (SHP_NO, ITM_NO)의 반복을 줄이는 키 압축(COMPRESS 2)도 함께 검토할 만하다(ACC-060, ACC-061).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 이력 테이블은 CF18을 2년 치로 쌓았다고 둔 파생 테이블 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 이력 테이블은 CF18을 2년 치로 쌓았다고 둔 파생 테이블 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황