INDEX의 사용여부 확인하기

No. 17040

(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