Tip
2010.04.09 10:11

INDEX 사용여부 확인하기

조회 수 19103 추천 수 0 댓글 0

(9I) INDEX의 사용여부 확인하기
==============================

PURPOSE
-------

Oracle9i에서는 만들어진후 사용되지 않은 index을 찾을수 있는 feature가
소개되었다.

Explanation
------------

ALTER INDEX MONITORING USAGE 절을 이용하여 작업 시간동안 사용되지 않는
index을 찾을 수 있다.

Example
--------
< Simple Example >

1. Sample table and data를 생성한다.

create table products
(prod_id number(3),
prod_name_code varchar2(5));

insert into products values(1,'aaaaa');
insert into products values(2,'bbbbb');
insert into products values(3,'ccccc');
insert into products values(4,'ddddd');
commit;

2. 1번에서 만든 table에 Primary Key index를 만든다.

SQL> alter table products
add (constraint products_pk primary key (prod_id));

3. v$object_usage을 query하여 본다.
: 아직 monitoring이 start되지 않았음을 알수 있다.

SQL> column index_name format a12
SQL> column monitoring format a10
SQL> column used format a4
SQL> column start_monitoring format a19
SQL> column end_monitoring format a19
SQL> select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

no rows selected

4. Index의 사용여부를 확인하기 위해 monitoring을 시작한다.

SQL> alter index products_pk monitoring usage;

Index altered.

5. v$object_usage를 query하여 monitoring중인지를 확인할수 있다.
: MONITORING column이 'YES', START_MONITORING column이 시작한 날짜.

SQL> select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
PRODUCTS_PK YES NO 04/02/2002 16:11:56

6. Test를 위해 index를 사용하는 select 문장을 수행한다.
필요하다면 index을 타고 있는지를 학인하기 위해 Autotrace utility 를
사용하기 위해 plan_table을 만들어 학인한다.
( Bulletin 10712 : NEW FEATURE:AUTOTRACE IN SQL*PLUS 3.3 참조)
@$ORACLE_HOME/rdbms/admin/utlxplan

Table created.

SQL> set autotrace on explain
SQL> select * from products where prod_id = 2;

PROD_ID PROD_NAME_
---------- ----------
2 bbbbb


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTS'
2 1 INDEX (UNIQUE SCAN) OF 'PRODUCTS_PK' (UNIQUE)

 

SQL> set autotrace off

7. v$object_usage을query하여 index가 사용되어진 여부을 확인할 수 있다.
: USED column이 'YES'
select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
PRODUCTS_PK YES YES 04/02/2002 16:11:56

8. Index사용여부을 monitoring하는 것을 stop한다.

SQL> alter index products_pk nomonitoring usage;

Index altered.

9. v$object_usage를 query하여 monitoring이 stop되었는지 확인할수 있다.
: MONITORING column이 'NO', END_MONITORING column이 stop한 날짜.

SQL> select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
PRODUCTS_PK NO YES 04/02/2002 16:11:56 04/03/2002 11:05:30

< Database 전체의 모든 index에 대해 monitoring >

1. SYS와 SYSTEM user 소유의 index을 제외하고 모든 index에 대해
monitoring을 시작하도록 script을 만들어 보자.

set heading off
set echo off
set feedback off
set pages 10000
spool startmonitor.sql
select 'alter index '||owner||'.'||index_name||' monitoring usage;'
from dba_indexes
where owner not in ('SYS','SYSTEM');
spool off


2. SYS와 SYSTEM user 소유의 index을 제외하고 모든 index에 대해
monitoring 을 stop하도록 script을 만들어 보자.

set heading off
set echo off
set feedback off
set pages 10000
spool stopmonitor.sql
select 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
from dba_indexes
where owner not in ('SYS','SYSTEM');
spool off


3. ALTER ANY INDEX system privilege을 가지고 있는 user에서 1번에서 만든
script을 돌리도록 한다.

@startmonitor

4. database의 정상 가동을 수행한다. (

5. 일정시간이 흐른후에 ALTER ANY INDEX system privilege을 가지고 있는
user에서 monitoring을 stop하기 위해 2번에서 만든 script을 돌리도록
한다.

@stopmonitor

6. v$object_usage을 query하여 한번도 사용되지 않은 index을 확인한다.

select d.owner, v.index_name
from dba_indexes d, v$object_usage v
where v.used='NO' and d.index_name=v.index_name;

참고 > v$object_usage view는 connect하는 user에 대한 내용만 display
하므로 SYS user에서 다른 user의 monitoring현황을 보고자 한다면
아래와 같이 view을 만든다.

SQL> connect / as sysdba;

Connected.

SQL> create or replace view V$ALL_OBJECT_USAGE
(INDEX_NAME,
TABLE_NAME,
MONITORING,
USED,
START_MONITORING,
END_MONITORING)
as
select io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from 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#;

View created.


SQL> select * from v$all_object_usage;

INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
PK_EMP EMP YES NO 10/12/2001 06:42:35


Reference Document
------------------
Note:144070.1
Note:160712.1
Note:136642.1
Oracle9i Database Administrator's Guide


List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
공지 Q&A Oracle관련 게시물만 Sean 2014.04.09 84839
80 Tip 10G rollback시간 예상하기 고구마 2010.04.09 15159
79 Tip SESSION KILL에 대하여 고구마 2010.04.09 13381
78 Tip Trace Event 세팅 고구마 2010.04.09 17285
77 Tip 영역할당해제 고구마 2010.04.09 11469
» Tip INDEX 사용여부 확인하기 고구마 2010.04.09 19103
75 Tip Log miner 사용방법 1 김준호 2010.04.06 29847
74 Tip 화일의 손상 여부를 확인하는 dbv 사용 방법 김준호 2010.04.06 18482
73 Tip shared pool wait event 2 file 유주환 2010.04.05 28881
72 Tip SHRINK 와 MOVE 의 특징 김준호 2010.03.31 13633
71 Tip Windows Server 2008 (64bit) - Oracle 10g 설치 file 김준호 2010.03.29 20683
70 Tip TABLESPACE FREESPACE 조회하기 1 고구마 2010.05.19 16657
69 Tip UDP Buffer Tuning 기법 3 김준호 2010.05.04 30856
68 Tip TM과 TX 락 1 유주환 2010.04.30 19021
67 Tip 오라클 튜닝 세미나 자료(IO) 1 file 고구마 2010.04.28 13927
66 Tip 오라클 튜닝 세미나자료 (메모리) file 고구마 2010.04.28 14662
65 Tip SQL수행 처리 절차 고구마 2010.04.28 20809
64 Tip show space 고구마 2010.04.28 16767
63 Tip (Diagnostics) Oracle10g DB 접속 안될때 sqlplus 에서 SYSTEMSTATE DUMP 받기 1 고구마 2011.05.18 28948
62 Tip 10g standard edition과 enterprise edition의 차이점 2 윤현 2011.05.16 26020
61 Tip 윈도우에서 일정기간 지난 파일 및 폴더 자동삭제 하기 1 송기성 2011.03.31 21212
Board Pagination Prev 1 2 3 4 5 6 7 8 Next
/ 8