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

　　对于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　 MOUNTSat Dec　4 10:09:49 2004ALTER DATABASE OPENSat Dec　4 10:09:49 2004LGWR: Primary database is in CLUSTER CONSISTENT modeThread 1 opened at log sequence 54　Current log# 2 seq# 54 mem# 0: /opt/oracle/oradata/conner/redo02.logSuccessful open of redo thread 1.Sat Dec　4 10:09:49 2004SMON: enabling cache recoverySat Dec　4 10:10:33 2004Restarting 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)asselect 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#　and 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_MONITORING
　　------------------------------ ------------------------------ --- --- ------------------- -------------------
　　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
　　
　　此操作对数据库没有潜在的影响,但是请谨慎使用.作为实验目的提供.

查看本文来源