택배사 연동 폴링이 배송 테이블을 하루 8,640번 통째로 읽는데, 인덱스 제안이 셋이다

실전DDL이커머스137 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

신규 이커머스는 택배사 연동 배치가 10초마다 출고 대기(READY) 배송을 찾아 택배사로 넘긴다. 운영 대시보드는 1분마다 배송 상태별 건수를 새로 그린다. 둘 다 shipping을 통째로 읽어 AWR의 'SQL ordered by Gets' 1, 2위에 올라 있다. 인덱스 제안이 셋 들어왔다. 조민우는 "상태가 넷뿐이니 비트맵", 선배는 "출고 대기만 담는 함수 기반 인덱스", 강도현은 "그냥 status B-tree"다. 박팀장은 "세 SQL을 하루 합계로 비교하고 하나를 골라라"고 했다.

테이블과 인덱스

shipping 배송: 34,908행 · 183블록(블록당 약 191행) · order_id와 1:1 · shipping_id 순으로 쌓였다 · PK (shipping_id)만 있다 · status NOT NULL
이 문제에서는 status를 넷으로 둔다: READY 약 120건 · SHIPPED 약 1,300건 · DELIVERED 약 32,700건 · RETURNED 약 800건 · READY인 배송은 최근 것이라 테이블 끝 블록 한두 개에 모여 있다
status에는 히스토그램이 없다(교재 18화의 점검)
실행 횟수: S1 택배사 폴링 하루 8,640번 · S2 대시보드 하루 1,440번 · S3 상태 변경(택배사 콜백) 하루 약 2,000번, 여러 세션이 동시에 보낸다

원본 SQL

sql

-- S1 택배사 폴링 (10초마다)
SELECT shipping_id, order_id, address_id
  FROM shipping
 WHERE status = 'READY'
 ORDER BY shipping_id;

-- S2 운영 대시보드 (1분마다)
SELECT status, COUNT(*) FROM shipping GROUP BY status;

-- S3 택배사 콜백
UPDATE shipping
   SET status = :v_status, shipped_date = SYSDATE
 WHERE shipping_id = :v_id;

실행계획 학습용 예시 · status 값과 분포는 문제에 적은 가정 · 비트맵 블록 수는 어림값

xplan

[S1]
------------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |      1 |        |    120 |00:00:00.01 |     183 |
|   1 |  SORT ORDER BY      |          |      1 |   8727 |    120 |00:00:00.01 |     183 |
|*  2 |   TABLE ACCESS FULL | SHIPPING |      1 |   8727 |    120 |00:00:00.01 |     183 |
------------------------------------------------------------------------------------------
   2 - filter("STATUS"='READY')

[S2]
------------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |      1 |        |      4 |00:00:00.01 |     183 |
|   1 |  HASH GROUP BY      |          |      1 |      4 |      4 |00:00:00.01 |     183 |
|   2 |   TABLE ACCESS FULL | SHIPPING |      1 |  34908 |  34908 |00:00:00.01 |     183 |
------------------------------------------------------------------------------------------

문제

세 제안 (가) status 비트맵 인덱스 (나) READY만 담는 함수 기반 인덱스 (다) status B-tree 인덱스가 S1, S2, S3에 각각 어떤 영향을 주는지 하루 읽는 양과 부작용으로 비교하시오.
하나를 골라 DDL과 필요한 통계 작업(또는 SQL 변경)을 작성하시오. 고른 이유를 숫자로 설명하시오.
고른 안에서 S1과 S2의 실행계획이 어떻게 바뀌는지 쓰고, 고르지 않은 두 안이 맞는 경우는 언제인지 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- (다) status B-tree : S1 은 READY 범위만, S2 는 인덱스만 읽어 상태별로 센다
CREATE INDEX IX_SHIPPING_ST ON shipping (status) ONLINE;

-- 치우친 분포(READY 0.3%)를 옵티마이저가 알게 한다 : 없으면 READY 를 4분의 1(8,727건)로 보고 FULL 을 고른다
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'SHIPPING',
                                METHOD_OPT    => 'FOR COLUMNS STATUS SIZE 254',
                                NO_INVALIDATE => FALSE);
END;
/

-- S1 · S2 · S3 는 그대로
SELECT shipping_id, order_id, address_id
  FROM shipping
 WHERE status = 'READY'
 ORDER BY shipping_id;

SELECT status, COUNT(*) FROM shipping GROUP BY status;

개선 후 실행계획 학습용 예시 · status 값과 분포는 문제에 적은 가정 · 비트맵 블록 수는 어림값

xplan

[S1]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |    120 |00:00:00.01 |       4 |
|   1 |  SORT ORDER BY                       |                |      1 |    120 |    120 |00:00:00.01 |       4 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SHIPPING       |      1 |    120 |    120 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN                  | IX_SHIPPING_ST |      1 |    120 |    120 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------------------
   3 - access("STATUS"='READY')

[S2]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      4 |00:00:00.01 |     105 |
|   1 |  HASH GROUP BY                       |                |      1 |      4 |      4 |00:00:00.01 |     105 |
|   2 |   INDEX FAST FULL SCAN               | IX_SHIPPING_ST |      1 |  34908 |  34908 |00:00:00.01 |     105 |
------------------------------------------------------------------------------------------------------------

[하루 합계 · 블록]
                  S1 (8,640번)       S2 (1,440번)       합계        S3 콜백
지금              183 → 1,581,120    183 → 263,520     1,844,640
(가) 비트맵         4 →    34,560      6 →   8,640        43,200    비트맵 항목 잠금 · 콜백끼리 대기 · ORA-00060
(나) READY FBI      3 →    25,920    183 → 263,520       289,440    READY에서 빠질 때만 항목 하나를 지운다
(다) B-tree         4 →    34,560    105 → 151,200       185,760    상태가 바뀔 때마다 항목을 옮긴다 · 행 단위 잠금

모범 답안 서술

지금은 S1과 S2가 모두 183블록짜리 FULL이다. 하루로는 S1이 158만, S2가 26만, 합계 약 184만 블록이다. (가) 비트맵 인덱스는 읽기로는 가장 싸다. S1은 READY 비트맵과 테이블 두어 블록(4), S2는 비트맵만 세어 몇 블록(6)이라 하루 약 4만 3천 블록이다. 하지만 S3가 문제다. 택배사 콜백이 상태를 바꿀 때마다 옛 상태와 새 상태의 비트맵 항목을 고쳐야 하고, 항목 하나는 수천 행의 비트를 덮으므로 그 항목 전체를 잠근다. 여러 세션이 동시에 보내는 콜백이 서로를 기다리고 ORA-00060도 난다. 상태가 계속 바뀌는 OLTP 테이블이라 고르지 않는다. (나) READY만 담는 함수 기반 인덱스 CASE status WHEN 'READY' THEN shipping_id END는 항목이 120개뿐이라 가장 작고, 콜백이 READY에서 빠질 때만 항목 하나를 지운다. S1은 3블록이다. 하지만 READY만 담으므로 S2의 상태별 건수에는 쓰지 못하고 S2는 그대로 FULL 183블록이다. 하루 약 28만 9천 블록이다. (다) status B-tree는 S1에서 READY 범위만 읽고 테이블 두어 블록을 더해 4블록이다. status가 NOT NULL이라 S2는 테이블 대신 인덱스 약 105블록을 INDEX FAST FULL SCAN으로 읽고 센다. 하루 약 18만 6천 블록으로 지금의 10분의 1이다. 콜백마다 항목을 옛 상태에서 지우고 새 상태에 넣지만 행 단위로 잠그므로 서로 기다리지 않는다. 그래서 (다)를 고른다. (다)에는 통계 작업이 따라야 한다. status에 히스토그램이 없으면 옵티마이저는 READY를 네 값 가운데 하나, 8,727건으로 추정해 S1에서 FULL을 고른다. status에 히스토그램(SIZE 254)을 모으면 READY를 120건 안팎으로 보고 인덱스를 쓴다. 고르지 않은 안이 맞는 경우도 있다. 비트맵은 밤에 적재하고 낮에는 읽기만 하는 분석 사본이라면 S2까지 가장 싸게 받는다. READY만 담는 함수 기반 인덱스는 상태별 건수 같은 다른 조회가 없고, 대기 건수가 전체에 비해 아주 적은 큐 테이블에 맞는다(IDX-047).

해설

함정: 값의 종류가 적다는 것만 보고 비트맵을 고르면 안 된다. 비트맵이 맞는지는 종류 수보다 그 테이블에 동시 DML이 있는지로 먼저 정한다(LOCK 단원의 비트맵 사고).
히스토그램 없이 B-tree를 만들면 '인덱스를 만들었는데 안 탄다'가 된다. 분포가 치우친 컬럼은 인덱스와 통계를 한 묶음으로 바꾼다(OPT 단원).
S2를 더 줄이고 싶다면 대시보드가 1분마다 정확한 건수를 볼 필요가 있는지부터 묻는다. 주기를 늘리거나 상태별 건수를 따로 쌓아 두는 방법이 인덱스보다 싸다.
READY 배송이 테이블 끝에 모여 있다는 전제가 깨지면(오래된 미출고가 섞이면) S1의 테이블 블록이 는다. 그래도 120행이면 120블록을 넘지 않는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · status 값과 분포는 문제에 적은 가정 · 비트맵 블록 수는 어림값 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · status 값과 분포는 문제에 적은 가정 · 비트맵 블록 수는 어림값 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황