2주 동안 USED가 NO로 나온 인덱스 셋을 지우겠다는 정리안이 올라왔다

기초DDL한빛유통140 / 700

상황

조민우가 인덱스 정리를 맡았다. 10월 7일(월) 아침 한빛유통 스키마의 인덱스 22개에 ALTER INDEX … MONITORING USAGE를 걸고, 2주 뒤 V$OBJECT_USAGE에서 USED가 NO인 셋을 골라 정리안을 올렸다. "2주 동안 한 번도 쓰이지 않았으니 지운다. 적재 비용과 공간이 준다." 박팀장은 지우기 전에 셋을 하나씩 판단해 오라고 했다.

테이블과 인덱스

T_ITM_EVL 상품평가: 5,000행 · 48블록 · PK_T_ITM_EVL (ITM_ID, EVL_LST_NO) · UX_ITM_EVL_CUS (ITM_ID, CUS_ID): UNIQUE 인덱스 · 2021년 "고객은 상품마다 평가를 한 번만 남긴다"는 규칙을 넣을 때 만들었다 · 제약은 선언하지 않았고, 평가 등록 프로그램은 중복을 따로 확인하지 않고 INSERT한다
T_ORD 주문: 100,000행 · 896블록 · IX_T_ORD_PAY_DT (PAY_DT): 2023년 승인서에 "「월 결제 대사」 배치용(매월 1일 06:00, 지난달 결제를 PAY_DT 범위로 읽는다)"이라고 적혀 있다
T_ORD_DET 주문상세: 200,000행 · 896블록 · IX_T_ORD_DET_QTY (ORD_QTY): 2022년 「대량 주문 찾기」 실험 때 만든 것으로 보인다 · 이 이름을 쓰는 프로그램이 소스에 없고, AWR에 남은 6개월 치 실행계획(DBA_HIST_SQL_PLAN)에도 없다
세 인덱스 모두 FK와는 상관없다 · 주문은 하루 약 137건, 주문상세는 약 270행, 상품평가는 약 30건이 INSERT된다

원본 SQL

sql

-- 조민우 정리안 (10월 21일)
DROP INDEX UX_ITM_EVL_CUS;
DROP INDEX IX_T_ORD_PAY_DT;
DROP INDEX IX_T_ORD_DET_QTY;

실행계획 학습용 예시

xplan

[V$OBJECT_USAGE · 10월 21일(월) 09:00 · sqlp 계정으로 조회]
INDEX_NAME          TABLE_NAME   MONITORING  USED  START_MONITORING      END_MONITORING
------------------  -----------  ----------  ----  --------------------  --------------
UX_ITM_EVL_CUS      T_ITM_EVL    YES         NO    10/07/2024 09:10:12
IX_T_ORD_PAY_DT     T_ORD        YES         NO    10/07/2024 09:10:14
IX_T_ORD_DET_QTY    T_ORD_DET    YES         NO    10/07/2024 09:10:15
(나머지 19개는 USED = YES)

[같은 2주 동안 세 테이블의 DML]
T_ITM_EVL   INSERT 412건 · 그중 (ITM_ID, CUS_ID)가 겹쳐 ORA-00001로 거부된 INSERT 3건
T_ORD       INSERT 1,918건
T_ORD_DET   INSERT 3,804건

문제

V$OBJECT_USAGE의 USED = NO가 무엇을 뜻하고 무엇을 뜻하지 않는지 설명하시오.
세 인덱스를 각각 지운다, 남긴다, 더 지켜본다 가운데 하나로 판정하고 근거를 쓰시오.
판정에 맞게 정리안을 SQL로 다시 작성하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① UX_ITM_EVL_CUS : 남긴다. 조회가 아니라 '고객은 상품마다 평가 한 번' 을 지키는 인덱스다 (2주 동안 중복 INSERT 3건을 막았다)
--    규칙을 제약으로 선언해 두면 다음 정리 때 오해받지 않는다 (그 뒤로는 DROP INDEX 도 ORA-02429 로 막힌다)
ALTER TABLE T_ITM_EVL ADD CONSTRAINT UK_ITM_EVL_CUS UNIQUE (ITM_ID, CUS_ID) USING INDEX UX_ITM_EVL_CUS;

-- ② IX_T_ORD_PAY_DT : 더 지켜본다. 관찰 기간에 매월 1일 배치가 없었다 → 11월 1일을 넘겨 다시 보고, AWR 이력도 찾는다
SELECT sql_id, MIN(timestamp) AS first_seen, MAX(timestamp) AS last_seen
  FROM dba_hist_sql_plan
 WHERE object_name = 'IX_T_ORD_PAY_DT'
 GROUP BY sql_id;

-- ③ IX_T_ORD_DET_QTY : 숨겨 두고 월말과 월초를 넘겨 지켜본 뒤 지운다
ALTER INDEX IX_T_ORD_DET_QTY INVISIBLE;
-- (11월 첫 주까지 문제가 없으면)  DROP INDEX IX_T_ORD_DET_QTY;

모범 답안 서술

V$OBJECT_USAGE의 USED는 모니터링을 켠 뒤(START_MONITORING 10월 7일 09:10)부터 지금까지 그 인덱스가 실행계획에 한 번이라도 나왔는지를 YES와 NO로만 알려 준다. NO가 뜻하는 것은 "이 2주 동안 이 인덱스를 읽은 SQL 계획이 없었다"까지다. 몇 번 쓰였는지, 관찰 기간 밖에서는 어땠는지, 조회가 아닌 다른 일을 하고 있는지는 알려 주지 않는다. 그래서 NO는 지울 후보를 고르는 출발점이지 지워도 된다는 증거가 아니다. ① UX_ITM_EVL_CUS는 남긴다. 이 UNIQUE 인덱스의 일은 조회가 아니라 "고객은 상품마다 평가를 한 번만 남긴다"는 규칙을 지키는 것이다. 평가를 INSERT할 때마다 같은 (ITM_ID, CUS_ID)가 있는지 이 인덱스로 확인하지만, 그 확인은 실행계획에 나오지 않으므로 USED는 NO로 남는다. 같은 2주 동안 중복 INSERT 3건이 ORA-00001로 거부된 것이 이 인덱스가 일하고 있다는 증거다. 제약 없이 만든 인덱스라 DROP INDEX는 에러 없이 성공하고, 그 순간부터 중복 평가가 조용히 쌓인다. 규칙을 UNIQUE 제약으로 선언해 이 인덱스를 쓰게 하면 다음 정리 때 오해받지 않는다. ② IX_T_ORD_PAY_DT는 더 지켜본다. 승인서의 사용처는 매월 1일 06:00 배치인데, 관찰 기간(10월 7일 ~ 21일)에는 매월 1일이 없었다. 11월 1일을 넘겨 다시 보고, AWR의 실행계획 이력(DBA_HIST_SQL_PLAN)에서 10월 1일 실행분을 찾아본다. ③ IX_T_ORD_DET_QTY는 지운다. 관찰 기간에 쓰이지 않았고, 소스와 6개월 치 실행계획 이력에도 없으며, UNIQUE도 FK 인덱스도 아니다. 다만 바로 지우지 않고 INVISIBLE로 숨겨 월말과 월초를 넘긴 뒤 지운다. 숨긴 동안 문제가 생기면 VISIBLE로 몇 초 만에 되돌릴 수 있지만, 지운 인덱스는 다시 만드는 시간이 통째로 든다. 지우고 나면 주문상세 INSERT마다 고칠 인덱스가 하나 준다.

해설

V$OBJECT_USAGE는 자기 것만 보인다: 인덱스 주인 계정으로 접속해야 행이 나온다. 다른 계정에서 보면 빈 결과가 나와 모니터링이 안 걸렸다고 오해하기 쉽다. 12c부터는 DBA_OBJECT_USAGE로 모든 계정의 것을 본다.
DBA_INDEX_USAGE와 함께 본다: 12.2부터는 모니터링을 따로 켜지 않아도 DBA_INDEX_USAGE에 인덱스별 사용 횟수와 마지막 사용 시각이 쌓인다. 기본이 표본 수집이라 드물게 쓰이는 인덱스는 0으로 보일 수 있으므로, 이것도 판단의 출발점일 뿐이다.
모니터링을 켜고 끄는 시점: MONITORING USAGE를 걸고 풀 때 그 인덱스와 관련된 커서가 다시 파싱될 수 있다. 수십 개를 한꺼번에 걸 때는 업무가 한가한 시간을 고른다.
UNIQUE와 FK는 따로 확인한다: 조회에 안 쓰여도 지우면 안 되는 인덱스는 대개 UNIQUE(중복 방지)와 FK 자식 쪽(부모를 지울 때의 검사와 잠금, 교재 12화)이다. 정리안을 만들 때 USER_INDEXES.UNIQUENESS와 USER_CONSTRAINTS부터 대조한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황

테이블과 인덱스

원본 SQL

실행계획 학습용 예시

문제

모범 답안 SQL

모범 답안 서술

해설

관련 교재