ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 9i研究系列之监视未使用索引(转)

Oracle 9i研究系列之监视未使用索引(转)

原创 Linux操作系统 作者:ysp_1984 时间:2009-07-19 19:50:00 0 删除 编辑

http://www.weixiuwang.com/Article/server/tech/200610/21454.html

    索引可以加快查询的速度,但索引会占用许多存储空间,在插入和删除行的时候,索引还会引入额外的开销,因此确保索引得到有效利用是我们很关注的一个问题。在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 ptimizer=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_monitoring,
  
  ou.end_monitoring
  
  from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ u
  
  where i.obj# = ou.obj#
  
  and io.obj# = ou.obj#
  
  and t.obj# = i.bo#
  
  and io.owner# = u.user#
  
  /
  
  COMMENT ON TABLE SYS.V$ALL_OBJECT_USAGE IS
  
  'Record of all index usage'
  
  /
  
  GRANT SELECT ON SYS.V$ALL_OBJECT_USAGE TO "PUBLIC"
  
  /
  
  CREATE PUBLIC SYNONYM V$ALL_OBJECT_USAGE
  
  FOR SYS.V$ALL_OBJECT_USAGE
  
  /
  
  之后就可以从这个视图中查询相关信息了,下面是示例过程:
  
  ----------www.ncn.cn---------示例过程--开始--------------------
  
  SQL> conn sys/sys as sysdba
  
  Connected.
  
  SQL> desc v$all_object_useage
  
  ERROR:
  
  ORA-04043: object v$all_object_useage does not exist
  
  SQL> @/oracle/oracle9/cyx/all_object_usage.sql
  
  SQL> CREATE OR REPLACE VIEW SYS.V$ALL_OBJECT_USAGE
  
  2 (
  
  3 OWNER,
  
  4 INDEX_NAME,
  
  5 TABLE_NAME,
  
  6 MONITORING,
  
  7 USED,
  
  8 START_MONITORING,
  
  9 END_MONITORING
  
  10 )
  
  11 AS
  
  12 select u.name, io.name, t.name,
  
  13 decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
  
  14 decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
  
  15 ou.start_monitoring,
  
  16 ou.end_monitoring
  
  17 from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ u
  
  18 where i.obj# = ou.obj#
  
  19 and io.obj# = ou.obj#
  
  20 and t.obj# = i.bo#
  
  21 and io.owner# = u.user#
  
  22 /
  
  View created.
  
  SQL> COMMENT ON TABLE SYS.V$ALL_OBJECT_USAGE IS
  
  2 'Record of all index usage'
  
  3 /
  
  Comment created.
  
  SQL> GRANT SELECT ON SYS.V$ALL_OBJECT_USAGE TO "PUBLIC"
  
  2 /
  
  Grant succeeded.
  
  SQL> CREATE PUBLIC SYNONYM V$ALL_OBJECT_USAGE
  
  2 FOR SYS.V$ALL_OBJECT_USAGE
  
  3 /
  
  Synonym created.
  
  SQL> @start_index_monitor.sql
  
  SQL> alter index T.INDEX_CFS monitoring usage;
  
  Index altered.
  
  SQL> alter index T.TEST_PK monitoring usage;
  
  Index altered.
  
  SQL> conn t/t
  
  Connected.
  
  SQL> select * from test where id <5;
  
  ID NAME
  
  ---------- ----------
  
  1 aaaaaaaa
  
  2 www.ncn.cn
  
  3 aadfaaaa
  
  4 gototop
  
  4 rows selected.
  
  SQL> conn sys/sys as sysdba
  
  Connected.
  
  SQL> @stop_index_monitor.sql
  
  SQL> alter index T.INDEX_CFS nomonitoring usage;
  
  Index altered.
  
  SQL> alter index T.TEST_PK nomonitoring usage;
  
  Index altered.
  
  SQL> @report_index_usage.sql
  
  SQL> set pages 10000
  
  SQL> set pagesize 200
  
  SQL> set linesize 100
  
  SQL> spool report_index_usage.txt
  
  SQL> ttitle center "--Report of Unused Indexes--"
  
  SQL> select owner,table_name,index_name,used
  
  2 from v$all_object_usage
  
  3 where used='NO';
  
  --Report of Unused Indexes--
  
  OWNER TABLE_NAME INDEX_NAME USED
  
  ------------ -------------- ------------ ----
  
  T CFS INDEX_CFS NO
  
  1 row selected.
  
  SQL> spool off
  

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16162908/viewspace-609636/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-05-08

  • 博文量
    15
  • 访问量
    18318