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.

文