Oracle 9i新特性研究
五 监视未使用索引
索引可以加快查询的速度，但索引会占用许多存储空间，在插入和删除行的时候，索引还会引入额外的开销，因此确保索引得到有效利用是我们很关注的一个问题。在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_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
/
之后就可以从这个视图中查询相关信息了，下面是示例过程：
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
