Oracle9i中如何监视索引并清除监视信息

来源：赛迪网    作者：31619

一般情况下，相对于DML操作，索引对于数据库是一个性能的负担。假如索引没有被合理有效的使用,那么它的存在性就必须重新考虑。

1. Oracle9i开始,Oracle允许监视索引的使用:

SQL> connect scott/tiger@conner
Connected to Oracle9i Enterprise Edition Release 9.2.0.4.0 
Connected as scott

SQL> select index_name from user_indexes;

INDEX_NAME
------------------------------
PK_DEPT
PK_EMP

开始监视pk_dept索引:

SQL> alter index pk_dept monitoring usage;

Index altered

在此过程中,如果查询使用索引,将会记录下来:

SQL> select * from dept where deptno=10;

DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK

停止监视:

SQL> alter index pk_dept nomonitoring usage;

Index altered

查询索引使用情况,YES表示在监视过程中索引被使用到:

SQL> select * from v$object_usage;

INDEX_NAME        TABLE_NAME         MONITORING USED START_MONITORING    END_MONITORING
----------------- ------------------ ---------- ---- ------------------- -------------------
PK_DEPT           DEPT               NO         YES  10/28/2004 10:55:19 10/28/2004 10:55:47

SQL>

2.Oracle9i的"Bug"

9205之前，假如你不慎监控了SYS.I_OBJAUTH1索引，而且不幸在重起数据库之前没有停止它，那么你的数据库将会无法启动，并且不会给出任何的错误信息。

下面的这条语句可以很容易的再现这个问题:

'ALTER INDEX SYS.I_OBJAUTH1 MONITORING USAGE'

假如有了足够好的备份，我们可以尝试一下:

注意：(请不要拿你现在的生产数据库来进行测试)。

[oracle@jumper oradata]$ sqlplus "/ as sysdba
"SQL*Plus: Release 9.2.0.4.0 - Production on Sat Dec 4 10:09:30 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> alter index SYS.I_OBJAUTH1 monitoring usage ;

Index altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 80811208 bytes
Fixed Size 451784 bytes
Variable Size 37748736 bytes
Database Buffers 41943040 bytes
Redo Buffers 667648 bytes
Database mounted.

 

至此，数据库会挂起，并且不会出现任何提示，在alert.log文件中，我们可以看到:

[oracle@jumper bdump]$ tail -f alert_conner.log 
Completed: ALTER DATABASE   MOUNT
Sat Dec  4 10:09:49 2004
ALTER DATABASE OPEN
Sat Dec  4 10:09:49 2004LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 opened at log sequence 54  
Current log# 2 seq# 54 mem# 0: /opt/oracle/oradata/conner/redo02.log
Successful open of redo thread 1.
Sat Dec  4 10:09:49 2004
SMON: enabling cache recovery
Sat Dec  4 10:10:33 2004
Restarting dead background process 
QMN0QMN0 started with pid=9

此时数据库则会停在此处，如果没有发现这个bug的存在，你可能会无所适从，也需你唯一能做的就是从备份中恢复，或是升级到9.2.0.5。

其实，Oracle已经Release了这个Bug，我们可以参考Metalink:Note:2934068.8,Oracle声明在9.2.0.5 (Server Patch Set)和 10g Production Base Release中fixed了这个Bug。

[oracle@jumper oradata]$ rm -rf conner
[oracle@jumper oradata]$ cp -R connerbak/ conner
[oracle@jumper oradata]$ sqlplus '/ as sysdba'
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Dec 4 10:19:07 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 80811208 bytes
Fixed Size 451784 bytes
Variable Size 37748736 bytes
Database Buffers 41943040 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>

3.在某些特殊的情况下,你可能需要清除这个v$object_usage视图中的信息。

Oracle的说法是,在下一次收集该对象的索引使用情况时会自动覆盖上一次的信息,不提供清除手段.

如果我们细心揣摩，可以发现v$object_usage是基于以下基表建立起来的:

create or replace view 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_monitoringfrom sys.obj$ io, 
sys.obj$ t, sys.ind$ i, 
sys.object_usage ouwhere io.owner# = userenv('SCHEMAID')  
and i.obj# = ou.obj#  
and io.obj# = ou.obj#  a
nd t.obj# = i.bo#
/

至此你可以注意到v$object_usage关键信息来源于OBJECT_USAGE表。

除此之外我们可以注意一下,此处v$object_usage的查询基于userenv('SCHEMAID')建立.

所以以不同用户登录,你是无法看到其他用户的索引监视信息的,即使是DBA,但是可以从object_usage表中得到。

SQL> select * from v$object_usage;

INDEX_NAME       TABLE_NAME     MON USE START_MONITORING    END_MONITORIN
------------------------------  -------------- -------------------
PK_DEPT            DEPT         NO  YES 10/28/2004 10:55:19 10/28/2004 10:55:47

SQL> select * from object_usage;
select * from object_usage
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> connect /as sysdba
Connected.
SQL> /

      OBJ#      FLAGS START_MONITORING    END_MONITORING
---------- ---------- ------------------- -------------------
      6288          1 10/28/2004 10:55:19 10/28/2004 10:55:47

实际上我们清除了object_usage表的记录,实际上也就清空了v$object_usage的信息。

SQL> delete from object_usage;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from v$object_usage;

no rows selected

注释：本文仅做参考示例，具体操作对数据库没有潜在的影响,但请大家根据自己的实际情况谨慎使用。