[Oracle] Index monitoring

DB 점검 도중, 약 60만건의 테이블에(담당자가 메인테이블이라고..) index가 20개 이상이 걸려 있는 것을 확인했다.

 

그리고 대부분 index들이 결합인덱스보다는 단일인덱스 인 것을 확인하고,

담당자에게 모두 필요한 index라고 물어보니

필요에 의해서 해당 테이블과 연관된 담당자들에 의해서 생성한 거라고,

자기도 잘 모른다고 하더군...

 

흠...내가 소속 DBA가 아닌 파견도..아닌 협력업체...DBA이다 보니..

이러지도 저러지도 못하는 상황..

 

보고는 하였지만..그래도 나는 DBA의 지부심이 있기에!!!내가 주체가 되어...제안....

"우리 INDEX 모니터링 걸어서 불필요한 거는 제거하고,

필요한 거는 새로 걸자고 제안....

 

index monitoring 관련 쿼리를 확인해 봤다.

 

1. index monitoring 여부 확인

 

-- 해당 user 라면 아래 쿼리를 이용하여 확인이 가능

SELECT table_name,
       index_name,
       monitoring
FROM   v$object_usage
WHERE  table_name = UPPER('&1')
AND    index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'));

 

--나는 DBA다!!!!

col owner for a10
col index_name for a20
col table_name for a20

 

SELECT U.NAME OWNER,
IO.NAME INDEX_NAME,
T.NAME TABLE_NAME,
DECODE(BITAND(I.FLAGS, 65536), 0, 'NO', 'YES') MORNITORING,
DECODE(BITAND(OU.FLAGS, 1), 0, 'NO', 'YES') USED,
OU.START_MONITORING,
OU.END_MONITORING
FROM SYS.USER$ U,
SYS.OBJ$ IO,
SYS.OBJ$ T,
SYS.IND$ I,
SYS.OBJECT_USAGE OU
WHERE I.OBJ# = OU.OBJ#
AND IO.OBJ# = OU.OBJ#
AND T.OBJ# = I.BO#
AND U.USER# = IO.OWNER#;

 

-- v$object_usage는 자신의 index 만 확인 가능하기에 이 점 유의!!

 

2. index에 모니터링이 걸려 있지 않으면 아래 쿼리를 이용하여 명령문을 만들 수 있다.

 

-- owner 와 table_name 을 입력해야 함

 

SELECT 'ALTER INDEX "' || i.owner || '"."' || i.index_name || '" MONITORING USAGE;'

FROM   dba_indexes i

WHERE  owner      = UPPER('&1')

AND    table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'));

 

 

 

 

3. 다시 체크한 결과 (위의 index monitoring 으로 조회)

 

 

 

4. index에 모니터링이 걸려 있는 것에 대해 해제 하는 명령어

 

 

SELECT 'ALTER INDEX "' || i.owner || '"."' || i.index_name || '" NOMONITORING USAGE;'

FROM   dba_indexes i

WHERE  owner      = UPPER('&1')

AND    table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'));

 

 

이렇게 하여 일단 체크를 진행하기 시작했다.

 

여기서 반은 걷어 냈으면 하는 바램이...