监视未使用索引

2/9/2008来源:Oracle教程人气:6010


  作者:gototop
  
  索引可以加快查询的速度,但索引会占用许多存储空间,在插入和删除行的时候,索引还会引入额外的开销,因此确保索引得到有效利用是我们很关注的一个问题。 在Oracle9i之前,要知道一个索引是否被使用是困难的,而Oracle 9i中提供了一个有效的监控方法:ALTER INDEX MONITORING USAGE。下面我讲具体说明如何使用该方法来鉴别未使用的索引。
  
  一、我们先通过一个例子具体说明“ALTER INDEX MONITORING USAGE”的使用方法:
  
  1、建测试表
  
  create table test(id number(3),name varchar2(10));
  
  insert into test values(1,'aaaaaaaa');
  
  insert into test values(2,'www.ncn.cn');
  
  insert into test values(3,'aadfaaaa');
  
  insert into test values(4,'gototop');
  
  insert into test values(5,'shenzhen');
  
  insert into test values(6,'china');
  
  commit;
  
  alter table test add (constraint test_pk PRimary key (id));
  
  2、查询v$object_usage(因为没有监视,所以还看不到内容)
  
  column index_name format a12
  
  column monitoring format a10
  
  column used format a4
  
  column start_monitoring format a19
  
  column end_monitoring format a19
  
  select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
  
  SQL> l
  
  1* select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage
  
  SQL> /
  
  no rows selected
  
  Elapsed: 00:00:00.00
  
  3、开始监控索引的使用情况
  
  SQL> alter index test_pk monitoring usage;
  
  Index altered.
  
  Elapsed: 00:00:00.05
  
  4、查询v$object_usage(可以看到正监视中)
  
  SQL> select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
  
  INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
  
  ------------ ---------- ---- ------------------- -------------------
  
  TEST_PK YES NO 05/15/2003 13:28:22
  
  Elapsed: 00:00:00.00
  
  5、使用索引进行查询
  
  SQL> set autotrace on eXPlain
  
  SQL> select * from test where id = 2;
  
  ID NAME
  
  ---------- ----------
  
  2 www.ncn.cn
  
  Elapsed: 00:00:00.00
  
  Execution Plan
  
  ----------------------------------------------------------
  
  0 SELECT STATEMENT Optimizer=CHOOSE
  
  1 0 TABLE access (BY INDEX ROWID) OF 'TEST'
  
  2 1 INDEX (UNIQUE SCAN) OF 'TEST_PK' (UNIQUE)
  
  SQL> set autotrace off
  
  SQL> /
  
  ID NAME
  
  ---------- ----------
  
  2 www.ncn.cn
  
  Elapsed: 00:00:00.00
  
  SQL>
  
  从上我们可以看到确实使用了索引。

  
  6、查询v$object_usage(可以看到索引被使用过,但目前还处于被监视过程中)
  
  SQL> select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
  
  INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
  
  ------------ ---------- ---- ------------------- -------------------
  
  TEST_PK YES YES 05/15/2003 13:28:22
  
  Elapsed: 00:00:00.00
  
  7、停止监视,并查询v$object_usage
  
  SQL> alter index test_pk nomonitoring usage;
  
  Index altered.
  
  Elapsed: 00:00:05.03
  
  SQL> select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
  
  INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
  
  ------------ ---------- ---- ------------------- -------------------
  
  TEST_PK NO YES 05/15/2003 13:28:22 05/15/2003 13:40:00
  
  Elapsed: 00:00:00.64
  
  到此为止,监视结束,MONITORING为NO,END_MONITORING给出了时间戳。
  
  二、v$object_usage视图解释
  
  从上面的例子中我们可以看出,索引的监视信息都是存在在v$objec_usage视图中,该视图的定义如下:
  
  CREATE OR REPLACE VIEW SYS.V$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 io.owner# = userenv('SCHEMAID')
  
  and i.obj# = ou.obj#
  
  and io.obj# = ou.obj#
  
  and t.obj# = i.bo#
  
  /
  
  COMMENT ON TABLE SYS.V$OBJECT_USAGE IS
  
  'Record of index usage'
  
  /
  
  GRANT SELECT ON SYS.V$OBJECT_USAGE TO "PUBLIC"
  
  /
  
  下面是该视图列的描述:
  
  INDEX_NAME: sys.obj$.name 中的索引名字
  
  TABLE_NAME: sys.obj$obj$name 中的表名
  
  MONITORING: YES (索引正在被监控), NO (索引没有被监控)
  
  USED: YES (索引已经被使用过), NO (索引没有被使用过)
  
  START_MONITORING: 开始监控的时间
  
  END_MONITORING: 结束监控的时间
  
  所有被使用过至少一次的索引都可以被监控并显示到这个视图中。
  
  三、监视数据库中所有索引的使用情况
  
  1、生成开始/结束监视索引的SQL脚本:
  
  set heading off
  
  set echo off
  
  set feedback off
  
  set pages 10000
  
  spool start_index_monitor.sql
  
  select 'alter index 'owner'.'index_name' monitoring usage;'
  
  from dba_indexes
  
  where owner in ('YOUR','PROD_DB','OWNER','LIST');
  
  spool off
  
  set heading on
  
  set echo on
  
  set feedback on
  
  ------------------------------------------------
  
  set heading off
  
  set echo off
  
  set feedback off
  
  set pages 10000
  
  spool stop_index_monitor.sql
  
  select 'alter index 'owner'.'index_name' nomonitoring usage;
'
  
  from dba_indexes
  
  where owner in ('YOUR','PROD_DB','OWNER','LIST');
  
  spool off
  
  set heading on
  
  set echo on
  
  set feedback on
  
  2、进行监视并查询结果:
  
   
  
  在业务量比较多的一天上班时运行start_index_monitor.sql,下班前运行stop_index_monitor.sql,之后就可以在各用户自己的v$object_usage视图中看到该SCHEMA下的索引使用情况了:
  
  SQL> conn t/t
  
  Connected.
  
  SQL> select index_name,table_name,used
  
  2 from v$object_usage
  
  3 where used='NO';
  
  INDEX_NAME TABLE_NAME USED
  
  ------------ ------------------------------ ----
  
  TEST_PK TEST NO
  
  1 row selected.
  
  SQL>
  
  3、改进结果查寻方法
  
  你也许已经注重到,上面查询结果是需要我们单独查询各SCHEMA中的v$object_usage,其实我们可以通过给v$object_usage视图添加一个owner列来创建一个可以存储所有SHCEMA的v$object_usage视图,不妨叫做v$all_object_usage,定义如下:
  
  CREATE OR REPLACE VIEW SYS.V$ALL_OBJECT_USAGE
  
  (
  
  OWNER,
  
  INDEX_NAME,
  
  TABLE_NAME,
  
  MONITORING,
  
  USED,
  
  START_MONITORING,
  
  END_MONITORING
  
  )
  
  AS
  
  select u.name, io.name, t.name,
  
  decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
  
  decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
  
  ou.start_mon