Oracle 9i新特性研究5监视未使用索引
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
SQL>select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
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
———- ———-
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
———- ———-
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视图中，该视图的定义如下：
v$object_usage是基于以下基表建立起来的:
|
下面是该视图列的描述：
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
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、改进结果查寻方法
v$object_usage是基于以下基表建立起来的:
|
注意到v$object_usage关键信息来源于OBJECT_USAGE表.另外我们可以注意一下,此处v$object_usage的查询基于userenv(‘SCHEMAID’)建立.所以以不同用户登录,你是无法看到其他用户的索引监视信息的,即使是dba,但是可以从object_usage表中得到.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
你也许已经注意到，上面查询结果是需要我们单独查询各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> @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
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
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Oracle9i的Bug
在9205之前，如果你不慎监控了SYS.I_OBJAUTH1索引，并且不幸在重起数据库之前没有停止它，那么你的数据库将会无法启动，并且不会给出任何错误信息。
以下这条简单的语句可以轻易再现这个问题:
‘ALTER INDEX SYS.I_OBJAUTH1 MONITORING USAGE’
如果你有了足够好的备份(严重警告，请不要拿你的生产数据库进行测试)，你可以尝试一下:
|
此时，数据库挂起，而且不会有任何提示，在alert<sid>.log文件中，你可以看到:
|
然后数据库将会停在此处。如果不知道此bug存在，你可能会一筹莫展的。现在你能做的就是从备份中恢复，或者升级。
|
在特殊的情况下,你可能需要清除这个v$object_usage视图中的信息.