주문 테이블을 고객 순서로 다시 실은 주말 뒤로 정산팀 월 주문 합계가 블록을 여든 배 넘게 읽는다

중급PLSQL한빛유통596 / 700

상황

상담팀이 「고객 주문 내역」 화면이 굼뜨다고 하자, 오과장은 토요일 밤 T_ORD를 고객 순서로 다시 실었다. 04화에서 본 대로 테이블을 인덱스 순서로 다시 쌓으면 그 인덱스의 클러스터링 팩터가 좋아진다. 월요일에 상담 화면은 실제로 빨라졌다(고객 한 명 Buffers 52 → 4). 그런데 정산팀 「월 주문 합계」(월을 고르면 그 달의 건수와 금액, 하루 수백 번)가 블록을 여든 배 넘게 읽는다. 오과장은 이렇게 말했다. "계획은 지난주와 똑같아요. 인덱스도 그대로 타고, 통계도 토요일 밤에 야간 잡이 다 모았어요." 조민우가 인덱스 통계를 다시 모아 보자고 하자 오과장이 덧붙였다. "그러면 날짜 인덱스 CF가 10만 가까이 나와서 FULL로 갈 거예요. TABLE_CACHED_BLOCKS를 최대로 올리면 CF가 작게 나온다니까 그걸 같이 하죠."

테이블과 인덱스

T_ORD: 100,000행 · 896블록(블록당 약 112행) · ORD_DT DATE(시분초 있음, 2023-01-01 ~ 2024-12-31, 하루 약 137건) · 2024년 6월 4,110건 · 고객 2,000명 × 50건
토요일 전에는 주문 순서(곧 날짜순)로 쌓여 있었다. 지금은 CUS_ID, ORD_DT 순서로 쌓여 있어서 한 고객의 주문 50건이 블록 한두 개에 모여 있다.
인덱스: PK_T_ORD (ORD_SEQ) · IX_T_ORD_DT (ORD_DT) BLEVEL 1 · 리프 265 · IX_T_ORD_CUS (CUS_ID) BLEVEL 1 · 리프 298 · 세 인덱스 모두 2024년 3월에 만들었다
야간 통계 잡 PKG_STATS.NIGHTLY(매일 23:40): 2021년에 T_ORD_BIG 인덱스 셋을 모으는 시간을 줄이려고 cascade => FALSE로 바꿨다. 주석에는 "인덱스 통계는 만들 때와 리빌드할 때 모인다"라고 적혀 있다.
T_ORD의 TABLE_CACHED_BLOCKS 선호도는 기본값(1)이다.

원본 SQL

sql

-- 토요일 22:00 · 오과장 : 상담 화면을 위해 T_ORD 를 고객 순서로 다시 싣는다 (인덱스 셋은 그대로 둔다)
CREATE TABLE T_ORD_SORT AS SELECT * FROM T_ORD ORDER BY CUS_ID, ORD_DT;
TRUNCATE TABLE T_ORD;
INSERT INTO T_ORD SELECT * FROM T_ORD_SORT;
COMMIT;
DROP TABLE T_ORD_SORT PURGE;
-- 통계는 야간 잡에 맡긴다

-- 야간 통계 잡 PKG_STATS.NIGHTLY (매일 23:40 · 2021-04 판)
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SQLP',
                                 options => 'GATHER STALE',
                                 cascade => FALSE);   -- 인덱스 통계는 만들 때와 리빌드할 때 모인다
END;
/

-- 정산팀 「월 주문 합계」
SELECT COUNT(*) AS cnt, SUM(ORD_AMT) AS amt, SUM(PAY_AMT) AS pay
  FROM T_ORD
 WHERE ORD_DT >= :v_from
   AND ORD_DT <  :v_to

실행계획 학습용 예시 · 클러스터링 팩터의 성질은 교재 04화 측정

xplan

[USER_TAB_STATISTICS · USER_INDEXES · 월요일 09:30]
 OBJECT_NAME    종류     NUM_ROWS  BLOCKS / LEAF_BLOCKS  CLUSTERING_FACTOR  LAST_ANALYZED
 T_ORD          TABLE     100,000                   896                  -  토 23:41 (야간 잡)
 PK_T_ORD       INDEX     100,000                   187                889  2024-03-11 (인덱스를 만든 날)
 IX_T_ORD_DT    INDEX     100,000                   265                892  2024-03-11
 IX_T_ORD_CUS   INDEX     100,000                   298             97,915  2024-03-11

[월 주문 합계 · 2024년 6월 · 월요일 10:05]
Plan hash value: 1830562947

-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |      1 |        |      1 |00:00:00.03 |    4118 |
|   1 |  SORT AGGREGATE               |             |      1 |      1 |      1 |00:00:00.03 |    4118 |
|*  2 |   FILTER                      |             |      1 |        |   4110 |00:00:00.03 |    4118 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_ORD       |      1 |   4105 |   4110 |00:00:00.03 |    4118 |
|*  4 |     INDEX RANGE SCAN          | IX_T_ORD_DT |      1 |   4105 |   4110 |00:00:00.01 |      12 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:V_FROM<:V_TO)
   4 - access("ORD_DT">=:V_FROM AND "ORD_DT"<:V_TO)

[같은 SQL · 지난주 금요일]  Plan hash value 1830562947 (같은 계획) · Buffers 49 · 0.01초

문제

계획이 지난주와 같은데 읽는 블록이 여든 배 넘게 늘어난 이유를 쓰시오. USER_INDEXES의 어느 숫자가 지금 테이블과 맞지 않는지, 그 숫자가 왜 고쳐지지 않았는지도 쓰시오.
통계를 바로잡는 SQL과 고친 야간 잡을 쓰시오. TABLE_CACHED_BLOCKS를 올리자는 오과장의 안을 평가하고, 그 선호도가 맞는 테이블은 어떤 테이블인지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 오늘 : 세 인덱스의 통계를 지금 행 순서로 다시 모은다 (이 SQL 의 커서도 바로 다시 파싱)
BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(ownname => 'SQLP', indname => 'IX_T_ORD_DT',  no_invalidate => FALSE);
  DBMS_STATS.GATHER_INDEX_STATS(ownname => 'SQLP', indname => 'IX_T_ORD_CUS', no_invalidate => FALSE);
  DBMS_STATS.GATHER_INDEX_STATS(ownname => 'SQLP', indname => 'PK_T_ORD',     no_invalidate => FALSE);
END;
/

-- ② 야간 잡 : 인덱스를 빼지 않는다 (cascade 를 기본값 AUTO_CASCADE 로 되돌린다)
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SQLP',
                                 options => 'GATHER STALE',
                                 cascade => DBMS_STATS.AUTO_CASCADE);
END;
/

-- ③ TABLE_CACHED_BLOCKS 는 기본값(1) 그대로 둔다 · SQL 은 그대로
SELECT COUNT(*) AS cnt, SUM(ORD_AMT) AS amt, SUM(PAY_AMT) AS pay
  FROM T_ORD
 WHERE ORD_DT >= :v_from
   AND ORD_DT <  :v_to;

개선 후 실행계획 학습용 예시 · 클러스터링 팩터의 성질은 교재 04화 측정

xplan

Plan hash value: 2966391504

------------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |      1 |        |      1 |00:00:00.01 |     896 |
|   1 |  SORT AGGREGATE     |       |      1 |      1 |      1 |00:00:00.01 |     896 |
|*  2 |   FILTER            |       |      1 |        |   4110 |00:00:00.01 |     896 |
|*  3 |    TABLE ACCESS FULL| T_ORD |      1 |   4105 |   4110 |00:00:00.01 |     896 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:V_FROM<:V_TO)
   3 - filter(("ORD_DT">=:V_FROM AND "ORD_DT"<:V_TO))

[USER_INDEXES · 다시 모은 뒤]  PK_T_ORD CF 99,861 · IX_T_ORD_DT CF 99,874 · IX_T_ORD_CUS CF 894

모범 답안 서술

무엇이 그대로이고 무엇이 바뀌었나: 계획(인덱스 범위 스캔 뒤 테이블 액세스)과 E-Rows 4,105는 지난주와 같다. 바뀐 것은 테이블의 행 순서다. 지금 T_ORD는 고객 순서로 쌓여 있어서, IX_T_ORD_DT를 날짜 순서로 읽으면 이웃한 두 주문은 거의 언제나 서로 다른 고객의 것이고 서로 다른 블록에 있다. 6월 4,110건을 읽는 동안 3번 줄은 행마다 다른 블록을 찾아가 4,106블록을 읽었다(전체 4,118). 지난주처럼 날짜순으로 쌓여 있을 때는 같은 4,110건이 블록 37개에 모여 있어서 49블록이면 됐다. 어느 숫자가 틀렸나: USER_INDEXES의 IX_T_ORD_DT CLUSTERING_FACTOR 892는 날짜순으로 쌓여 있던 때의 값이다. 지금 순서로 세면 99,874로 테이블 행 수에 가깝다. 이 인덱스 통계의 LAST_ANALYZED는 2024년 3월(인덱스를 만든 날)이고 테이블 통계는 토요일 23:41이다. 야간 잡이 cascade => FALSE로 테이블과 열 통계만 모으고 인덱스 통계는 빼서, 행 순서가 통째로 바뀌었는데도 인덱스 통계는 고쳐지지 않았다. TRUNCATE는 통계를 지우지 않고, 일반 INSERT는 인덱스 통계를 고치지 않는다. 옵티마이저는 낡은 CF 892로 인덱스 경로 비용을 1 + 265 × 0.041 + 892 × 0.041 = 49로 셈해 FULL(896블록)보다 싸다고 보았다. 실제 CF로 셈하면 4,100이 넘는다. 고침: 세 인덱스의 통계를 지금 순서로 다시 모으고 커서를 무효화한다. IX_T_ORD_DT의 CF가 99,874가 되면 옵티마이저는 FULL을 골라 Buffers가 4,118 → 896이 된다. 야간 잡은 cascade를 기본값(DBMS_STATS.AUTO_CASCADE)으로 되돌려 테이블을 모을 때 인덱스도 함께 모으게 한다. 인덱스 통계를 모으는 일은 리프 블록을 한 번 읽는 정도라 테이블 수집에 견주어 크지 않다. 반대 방향도 낡았다: IX_T_ORD_CUS의 CF 97,915도 옛 순서의 값이다. 지금은 894라서, 여러 고객의 주문을 한꺼번에 읽는 SQL은 이 인덱스가 싸다는 사실을 모른 채 FULL을 고르고 있다. 테이블은 한 가지 순서로만 쌓이므로 두 인덱스의 CF가 함께 좋을 수는 없다. 다시 모으면 두 숫자가 모두 지금 순서에 맞춰진다. 오과장 안 평가: TABLE_CACHED_BLOCKS는 CF를 셀 때 최근에 들른 블록 N개로 돌아오는 것은 세지 않게 하는 선호도다(기본값 1, 최대 255). 지금 T_ORD는 다음 행이 896블록 어디에나 있을 수 있어서, 255로 올려도 돌아오는 경우는 넷 가운데 하나 남짓이고 CF는 7만 안팎으로 여전히 크다. 설령 CF가 작게 나와도 행마다 다른 블록을 읽는 실제 일(4,106블록)은 그대로라, 옵티마이저가 더 비싼 길을 고르게 만들 뿐이다. 이 선호도는 여러 세션이 동시에 일반 INSERT로 넣어 이웃한 키의 행이 블록 몇 개에 번갈아 들어간 테이블에 맞는다. 그런 테이블은 실제로는 블록 몇 개를 오가며 읽는데, 기본 CF는 오갈 때마다 1씩 더해 부풀려진다. 그래서 이 테이블은 기본값 1을 그대로 둔다.

해설

다시 싣는 작업 절차에는 인덱스 통계 단계가 따로 있어야 한다. CTAS로 새 테이블을 만들고 인덱스를 새로 만들면 만들 때 통계가 모이지만, TRUNCATE 뒤 제자리에 다시 실으면 인덱스 통계는 옛 값 그대로 남는다. 재구성 작업서에 "인덱스 통계 수집과 USER_INDEXES 확인"을 한 줄 넣는다(교재 04화 §4).
계획 해시가 같다고 일이 같은 것은 아니다. 같은 계획이라도 테이블의 행 순서가 바뀌면 테이블 액세스 줄의 블록 수가 달라진다. 계획을 비교할 때는 Buffers까지 함께 본다.
월 주문 합계를 더 줄이려면 CF를 피하는 커버링 인덱스 (ORD_DT, ORD_AMT, PAY_AMT)가 있다(04화 §7). 한 달을 리프 수십 블록으로 끝내지만 인덱스가 넷이 되어 주문을 넣을 때마다 고칠 곳이 늘어난다. 테이블을 고객 순서로 둔다는 결정이 먼저이고, 커버링 인덱스는 그 뒤에 비용을 따져 고른다.
cascade => FALSE의 원래 목적(T_ORD_BIG 인덱스 셋의 수집 시간)은 그 테이블에만 CASCADE 선호도를 주는 식으로 풀 수 있다. 스키마 전체에서 인덱스를 빼면 이번처럼 행 순서가 바뀐 테이블을 아무도 다시 세지 않는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 클러스터링 팩터의 성질은 교재 04화 측정 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 클러스터링 팩터의 성질은 교재 04화 측정 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황