Oracle9i的索引监视及注意事项

文章作者 100test 发表时间 2007:09:25 12:53:26
来源 100Test.Com百考试题网

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

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

SQL> connect scott/tiger@conner Connected to Oracle9i Enterprise Edition Release 9.2.0.4.0 Connected as scott SQL> 0select index_name from user_indexes. INDEX_NAME ------------------------------ PK_DEPT PK_EMP 开始监视pk_dept索引: SQL> alter index pk_dept monitoring usage. Index altered 在此过程中,如果查询使用索引,将会记录下来: SQL> 0select * from dept where deptno=10. DEPTNO DNAME LOC ------ -------------- ------------- 10 ACCOUNTING NEW YORK 停止监视: SQL> alter index pk_dept nomonitoring usage. Index altered 查询索引使用情况,YES表示在监视过程中索引被使用到: SQL> 0select * 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 2004 LGWR: 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 QMN0 QMN0 started with pid=9