监视索引

Identifying Unused Indexes with the ALTER INDEX MONITORING USAGE Command

作者 ora110 12:32 | 静态链接网址 | 最新回复 (0) | 引用 (0) | oracle - tunning

zt

Identifying Unused Indexes with the ALTER INDEX MONITORING USAGE Command

Checked for relevance on 03-July-2007

PURPOSE
This document explains how to identify unused indexes in order to remove them.


SCOPE & APPLICATION
DBAs must be aware that unused indexes:
* consume storage space
* degrade performance by unnecessary overheads during DML operations

REFERENCES

Note 144070.1 Identifying unused indexes in Oracle9i


How to Identify Unused Indexes in Order to Remove Them:
=======================================================
The examples below work if the user logged is the owner of the index.

1. Set the index under MONITORING:

SQL> alter index I_EMP monitoring usage;
Index altered.

SQL> select * from v$object_usage; （只能查看本schema下的index)

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP YES NO 03/14/2001 09:17:19


2. Check if the monitored indexes are used or not through the USED column in
V$OBJECT_USAGE view:

SQL> select sal from emp where ename='SMITH';

SAL
----------
800


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'I_EMP' (NON-UNIQUE)


The explain plan indicates the index is used.


SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP YES YES 03/14/2001 09:17:19


If the index was not used, the DBA could drop this unused index.


3. To stop monitoring an index, use the following SQL statement:

SQL> alter index i_emp nomonitoring usage;
Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP NO YES 03/14/2001 09:17:19 03/14/2001 09:55:24


As soon as you turn monitoring on again for the index, both columns
MONITORING and USED are reset.


SQL> alter index i_emp monitoring usage;
Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP YES NO 03/14/2001 09:57:27

一个测试案例

监视未使用索引

索引可以加快查询的速度，但索引会占用许多存储空间，在插入和删除行的时候，索引还会引入额外的开销，因此确保索引得到有效利用是我们很关注的一个问题。在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_mon

在Oracle9i中,如何监视索引并清除监视信息http://www.eygle.com/archives/2004/12/monitor_index_usage.html

<!-- comment these out if you want to see an example of custom fields, but remember to name the fields in the same way they are named here: 'imfeeling' (livejournal.com style), 'listening' and 'new_field'

: ?

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

转载于:http://blog.itpub.net/11782778/viewspace-615791/