监视未使用索引

• 索引
• 使用
• 监视
• SQL
• set
• object_usage

索引可以加快查询的速度，但索引会占用许多存储空间，在插入和删除行的时候，索引还会引入额外的开销，因此确保索引得到有效利用是我们很关注的一个问题。在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下的索引使用情况了：
中国_网管联盟bitsCN.com

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