|
|
|
![]()
| [일반] INDEX의 사용여부 확인하기 |
|
| 호석 님께서 쓴 글입니다 - 121.♡.39.7 |
읽음:1582 |
|
|
[Oracle] INDEX의 사용여부 확인하기 (05.01.13 14:25)
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
|
|
|
|
|
|
