30일 사용 이력이 0인 인덱스를 지우면 POS 적재가 가벼워진다는 정리안이 올라왔다

중급DDL버거142 / 700

상황

버거원 운영 주문 ORD_RANDOM에는 인덱스가 여섯 개 있다. POS 적재가 느리다는 말이 나오자 조민우가 DBA_INDEX_USAGE 30일 치를 뽑아 정리안을 올렸다. "사용 횟수가 0이거나 거의 없는 X04, X07, X08을 지운다. 인덱스가 여섯에서 셋으로 준다." 박팀장은 이렇게 답했다. "횟수만 보고 정하지 마라. 셋이 각각 왜 있는지, 그 횟수가 무슨 읽기였는지까지 보고 와라."

테이블과 인덱스

ORD_RANDOM(운영본): 1,000,000행 · 22,414블록(블록당 약 45행) · 무작위 순서로 적재되어 시각이나 매장으로 시작하는 인덱스는 CLUSTERING_FACTOR가 약 1,000,000이다 · POS가 하루 약 1,370건을 INSERT하고, 매월 초 정정분 약 4만 건을 다시 넣는다
ORD_HMS VARCHAR2(6) '090000' ~ '225959'에 고르게 분포(한 시간에 약 7.1%) · 같은 조건을 FULL로 읽으면 22,234블록이다
SHOP 매장: 200행 · SHOP_PK (SHOP_NO) · FK FK_ORD_SHOP(ORD_RANDOM.SHOP_NO → SHOP.SHOP_NO, 2013년 선언, ENABLED) · 매년 1월 「매장 코드 정리」 배치가 개점이 취소된 매장 코드를 SHOP에서 지운다
인덱스(승인서 기준): ORD_RANDOM_PK (ORD_NO) 영수증 조회 · ORD_RANDOM_X01 (ORD_DT, ORD_HMS) 일별 주문 조회 · ORD_RANDOM_X02 (ORD_DT, SHOP_NO) 월마감(매월 1일) · ORD_RANDOM_X04 (SHOP_NO) 2013년 FK를 선언할 때 만들었다 · ORD_RANDOM_X07 (ORD_HMS) 2019년 「시간대별 주문」 화면용(화면은 2022년에 없어졌다), 리프 약 2,500블록 · ORD_RANDOM_X08 (ORD_DT, ORD_HMS, SHOP_NO) 작년 클레임 조회 튜닝 실험 때 INVISIBLE로 만들어 두었다
인덱스 하나는 정정분 4만 건 적재에 redo 약 9.4MB, 블록 변경 약 8만을 더한다(교재 04화 실측 비례) · DBA_INDEX_USAGE는 기본 설정(표본 수집)이다

원본 SQL

sql

-- 조민우 정리안 · DBA_INDEX_USAGE 30일 치 기준
DROP INDEX ORD_RANDOM_X04;   -- 0회
DROP INDEX ORD_RANDOM_X07;   -- 4회
DROP INDEX ORD_RANDOM_X08;   -- 0회

실행계획 학습용 예시 · 인덱스 규모와 CF는 교재 04화 실측

xplan

[DBA_INDEX_USAGE · 9월 25일 ~ 10월 24일]
NAME              TOTAL_ACCESS_COUNT  TOTAL_ROWS_RETURNED  BUCKET_1000_PLUS_ACCESS_COUNT  LAST_USED
----------------  ------------------  -------------------  -----------------------------  -----------
ORD_RANDOM_PK              1,184,320            1,184,320                              0  10/24 21:58
ORD_RANDOM_X01                88,210            4,625,330                              0  10/24 21:57
ORD_RANDOM_X02                     1               41,666                              1  10/01 07:12
ORD_RANDOM_X04                     0                    0                              0
ORD_RANDOM_X07                     4              284,712                              4  10/14 16:40
ORD_RANDOM_X08                     0                    0                              0

[X07을 쓴 SQL · 분석팀 BI 도구 · 10월 14일 네 번]
SELECT SHOP_NO, COUNT(*) FROM ORD_RANDOM
 WHERE ORD_HMS BETWEEN '120000' AND '125959' GROUP BY SHOP_NO
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |    200 |00:00:01.92 |   71372 |
|   1 |  HASH GROUP BY                       |                |      1 |     78 |    200 |00:00:01.92 |   71372 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD_RANDOM     |      1 |     78 |  71178 |00:00:01.88 |   71372 |
|*  3 |    INDEX RANGE SCAN                  | ORD_RANDOM_X07 |      1 |     78 |  71178 |00:00:00.02 |     180 |
-----------------------------------------------------------------------------------------------------------------
   3 - access("ORD_HMS">='120000' AND "ORD_HMS"<='125959')

[USER_CONSTRAINTS · ORD_RANDOM의 FK]
CONSTRAINT_NAME  TYPE  COLUMN    R_CONSTRAINT  STATUS
FK_ORD_SHOP      R     SHOP_NO   SHOP_PK       ENABLED

문제

정리안의 세 인덱스를 각각 지운다, 남긴다, 숨기고 지켜본다 가운데 하나로 판정하고 근거를 쓰시오. X04의 사용 횟수 0이 무엇을 놓치고 있는지 설명하시오.
X07은 30일 동안 네 번 쓰였다. 그 네 번이 어떤 읽기였는지 위 숫자로 설명하고, X07이 없어지면 그 SQL이 어떻게 되는지 설명하시오.
판정에 맞는 정리 절차를 SQL로 작성하시오.

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

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

모범답안

모범 답안 SQL

sql

-- X04 (SHOP_NO) : 남긴다 — FK_ORD_SHOP 의 자식 쪽 인덱스. 매장 코드를 지우거나 바꿀 때 자식 주문을 이 인덱스로 확인한다
SELECT c.constraint_name, cc.column_name, cc.position, c.status
  FROM user_constraints c
  JOIN user_cons_columns cc ON cc.constraint_name = c.constraint_name
 WHERE c.table_name = 'ORD_RANDOM'
   AND c.constraint_type = 'R';

-- X08 : 지운다 — INVISIBLE 이라 아무 SQL 도 못 쓰는데 INSERT 마다 고친다 (실험은 끝났다)
DROP INDEX ORD_RANDOM_X08;

-- X07 : 숨기고 월초(월마감 · 정정분 적재)를 넘겨 지켜본 뒤 지운다
ALTER INDEX ORD_RANDOM_X07 INVISIBLE;
--   분석팀 SQL 은 FULL 로 바뀐다 (71,372 → 22,234) · 11월 첫 주까지 문제가 없으면
-- DROP INDEX ORD_RANDOM_X07;

개선 후 실행계획 학습용 예시 · 인덱스 규모와 CF는 교재 04화 실측

xplan

[X07을 숨긴 뒤 · 분석팀 SQL]
---------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |      1 |        |    200 |00:00:00.21 |   22234 |
|   1 |  HASH GROUP BY     |            |      1 |     78 |    200 |00:00:00.21 |   22234 |
|*  2 |   TABLE ACCESS FULL| ORD_RANDOM |      1 |     78 |  71178 |00:00:00.15 |   22234 |
---------------------------------------------------------------------------------------------
   2 - filter(("ORD_HMS">='120000' AND "ORD_HMS"<='125959'))

모범 답안 서술

① X04는 남긴다. 사용 횟수는 0이지만 X04의 선두 컬럼 SHOP_NO는 FK_ORD_SHOP의 자식 쪽 컬럼이다. 부모 SHOP에서 매장 코드를 지우거나 바꿀 때 오라클은 그 매장의 주문이 ORD_RANDOM에 남아 있는지 확인해야 하고, 이 확인을 X04로 한다. X04가 없으면 매장 코드 하나를 지울 때마다 ORD_RANDOM 22,414블록을 통째로 훑고, 그동안 자식 테이블에 잠금이 걸려 POS INSERT와 서로 기다리게 된다(교재 12화). 사용 이력은 SQL의 실행계획에서 인덱스를 읽은 경우를 센다. DML 안에서 하는 FK 검사는 잡히지 않을 수 있고, 기본이 표본 수집이라 드물게 쓰이는 인덱스는 0으로 보이기 쉽다. 1월에 한 번 도는 배치라면 9월 ~ 10월 30일 치에는 나올 수도 없다. ② X07의 네 번은 모두 분석팀 BI 도구의 같은 SQL이었다. TOTAL_ROWS_RETURNED 284,712를 4로 나누면 한 번에 71,178행, 곧 점심 한 시간 치(7.1%)를 X07로 읽었다. 이 인덱스는 CLUSTERING_FACTOR가 약 1,000,000이라 행마다 다른 테이블 블록을 읽으므로 한 번에 71,372블록, FULL 22,234블록의 세 배가 넘는다. ORD_HMS가 문자라서 옵티마이저가 한 시간 범위를 78행으로 낮게 본 탓에 고른 경로다. 그러니 이 네 번은 X07이 쓸모 있다는 증거가 아니라 X07 때문에 손해를 본 기록이다. X07을 숨기면 그 SQL은 FULL로 바뀌어 71,372 → 22,234블록으로 가벼워진다. 다만 숨긴 채로 월초(월마감, 정정분 적재)를 넘겨 다른 쓰임이 없는지 본 뒤 지운다. ③ X08은 지운다. INVISIBLE 인덱스는 옵티마이저 눈에만 보이지 않을 뿐, 주문이 INSERT될 때마다 다른 인덱스와 똑같이 고쳐진다. 아무 SQL도 쓰지 못하면서 POS INSERT와 월초 정정 적재에 비용만 낸다(정정분 4만 건에 redo 약 9.4MB, 블록 변경 약 8만). 정리가 끝나면 인덱스는 여섯에서 넷이 된다. 줄어든 둘만큼 POS INSERT 한 건에 블록 변경이 약 4회 줄고, 월초 정정 적재의 redo가 약 19MB 준다.

해설

FK 인덱스인지 보는 법: USER_CONS_COLUMNS의 FK 컬럼이 어떤 인덱스의 앞부분(같은 순서)과 겹치는지 대조한다. X02 (ORD_DT, SHOP_NO)처럼 SHOP_NO가 뒤에 있는 인덱스는 FK 검사를 대신하지 못한다.
관찰 기간을 잡는 법: 표본 수집을 전수로 바꾸는 숨은 파라미터(_iut_stat_collection_type)도 있지만 지원 부서와 협의해야 한다. 그보다는 관찰 기간이 월초와 분기 말, 연초 배치를 포함하게 잡고 AWR 실행계획 이력(DBA_HIST_SQL_PLAN)을 함께 찾는 편이 안전하다.
분석팀 SQL의 근본 대책: FULL 22,234블록도 운영 주문 테이블에는 부담이다. 이런 분석은 이관 전 보관본이나 정보계 사본에서 돌리게 한다(ACC-029).
많이 쓰인다는 것도 근거가 아니다: 사용 횟수가 많아도 BUCKET 칸이 1,000행 이상 쪽에 몰려 있으면, 넓은 범위를 CF가 나쁜 인덱스로 읽고 있는지 의심해 본다. 사용 이력은 지울 인덱스뿐 아니라 잘못 쓰이는 인덱스도 찾아 준다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 인덱스 규모와 CF는 교재 04화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 인덱스 규모와 CF는 교재 04화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황