index monitoring

인덱스 사용 현황 모니터링

1. 개요
  - 새로운 인덱스를 생성하거나, 기존의 인덱스의 변경으로인해 사용되지 않는 인덱스가 생겨날 수 있다. 
이러한 인덱스를 찾아 수정하거나 제거할 필요성이 제기 된다. 
실제로 튜닝 작업 이후 기존에 있던 인덱스를 제거하려는 경우, 
실제로 어떤 인덱스를 제거해야 할지 결정하기만 쉽지 않다. 

- 특정 기간동안의 쿼리에 대한 엑세스 유형을 분석하고, 
인덱스의 사용유무를 모니터링 한 이후에 사용되지 않는 인덱스를 제거해야만 할 것이다. 



2.  뷰
- 오라클 9i 부터 제공되는 다이나믹 뷰 v$object_usage 를 이용하여 위의 작업을 
수행 및 결과를 토대로 인덱스 사용현황을 파악할 수 있다.



3. v$object_usage
INDEX_NAME NOT NULL VARCHAR2(30) 인덱스 명
TABLE_NAME NOT NULL VARCHAR2(30) 인덱스 테이블 명
MONITORING VARCHAR2(3) 모니터링 수행 여부
USED VARCHAR2(3) 인덱스 Access 여부
START_MONITORING VARCHAR2(19) 모니터링 수행 시작
END_MONITORING VARCHAR2(19) 모니터링 수행 종료






4. 사용방법

CREATE TABLE SAMPLE (
ID NUMBER (4),
DATA VARCHAR2(10)
);


INSERT INTO SAMPLE VALUES (1, 'aaa');
INSERT INTO SAMPLE VALUES (2, 'bbb');
INSERT INTO SAMPLE VALUES (3, 'ccc');
INSERT INTO SAMPLE VALUES (4, 'ddd');



alter table sample add constraint pk_sample primary key(id);



select index_name, table_name, index_type 
from user_indexes 
where table_name ='SAMPLE'






# 모니터링 시작
alter index pk_sample monitoring usage;


# 모니터링 중지
alter index pk_sample nomonitoring usage;


# 확인 - 1
select * from v$object_usage;



INDEX_NAME TABLE_NAME  MONITORING  USED START_MONITORING END_MONITORING
PK_SAMPLE SAMPLE YES NO 11/03/2016 09:18:15



# 확인 - 2
select username as owner ,
io.name as index_name,
t.name as table_name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES') as monitoring,
decode(bitand(ou.flags, 1), 0, 'NO', 'YES') as used,
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, dba_users u
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
and user_id = t.owner#;