Oracle9i新特性-索引监视及注意事项[修正版]

对于DML操作来说索引对于数据库是一个性能负担如果索引没有被有效的使用那么其存在性就值得从新考虑

从Oraclei开始Oracle允许你监视索引的使用:

SQL> connect scott/tiger@conner

Connected to Oraclei Enterprise Edition Release

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=;

DEPTNO DNAME LOC

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 // :: // ::

SQL>

Oraclei的Bug

在之前如果你不慎监控了SYSI_OBJAUTH索引并且不幸在重起数据库之前没有停止它那么你的数据库将会无法启动并且

不会给出任何错误信息

以下这条简单的语句可以轻易再现这个问题:

ALTER INDEX SYSI_OBJAUTH MONITORING USAGE

如果你有了足够好的备份(严重警告请不要拿你的生产数据库进行测试)你可以尝试一下:

[oracle@jumper oradata]$ sqlplus / as sysdba

SQL*Plus: Release Production on Sat Dec ::

Copyright (c) Oracle Corporation All rights reserved

Connected to:

Oraclei Enterprise Edition Release Production

With the Partitioning option

JServer Release Production

SQL> alter index SYSI_OBJAUTH monitoring usage ;

Index altered

SQL> shutdown immediate;

Database closed

Database dismounted

ORACLE instance shut down

SQL> startup

ORACLE instance started

Total System Global Area bytes

Fixed Size bytes

Variable Size bytes

Database Buffers bytes

Redo Buffers bytes

Database mounted

此时数据库挂起而且不会有任何提示在alert<sid>log文件中你可以看到:

[oracle@jumper bdump]$ tail f alert_connerlog Completed: ALTER DATABASE MOUNTSat Dec :: ALTER DATABASE OPENSat Dec :: LGWR: Primary database is in CLUSTER CONSISTENT modeThread opened at log sequence Current log# seq# mem# : /opt/oracle/oradata/conner/redologSuccessful open of redo thread Sat Dec :: SMON: enabling cache recoverySat Dec :: Restarting dead background process QMNQMN started with pid=

然后数据库将会停在此处

如果不知道此bug存在你可能会一筹莫展的

现在你能做的就是从备份中恢复或者升级

[oracle@jumper oradata]$ rm rf conner[oracle@jumper oradata]$ cp R connerbak/ conner[oracle@jumper oradata]$ sqlplus / as sysdba

SQL*Plus: Release Production on Sat Dec ::

Copyright (c) Oracle Corporation All rights reserved

Connected to an idle instance

SQL> startup

ORACLE instance started

Total System Global Area bytes

Fixed Size bytes

Variable Size bytes

Database Buffers bytes

Redo Buffers bytes

Database mounted

Database opened

SQL>

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

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

稍微研究了一下

v$object_usage是基于以下基表建立起来的:

create or replace view v$object_usage(index_name table_name monitoring used start_monitoring end_monitoring)asselect ioname tname decode(bitand(iflags ) NO YES) decode(bitand(ouflags ) NO YES) oustart_monitoring ouend_monitoringfrom sysobj$ io sysobj$ t sysind$ i sysobject_usage ouwhere ioowner# = userenv(SCHEMAID) and iobj# = ouobj# and ioobj# = ouobj# and tobj# = ibo#/

注意到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_MONITORING

PK_DEPT DEPT NO YES // :: // ::

SQL> select * from object_usage;

select * from object_usage

*

ERROR at line :

ORA: table or view does not exist

SQL> connect /as sysdba

Connected

SQL> /

OBJ# FLAGS START_MONITORING END_MONITORING

// :: // ::

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

SQL> delete from object_usage;

row deleted

SQL> commit;

Commit complete

SQL> select * from v$object_usage;

no rows selected

此操作对数据库没有潜在的影响但是请谨慎使用作为实验目的提供