對于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<sid>.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
此操作對數據庫沒有潛在的影響,但是請謹慎使用.作為實驗目的提供.
