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

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

　　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

　　此操作對數據庫沒有潛在的影響但是請謹慎使用作為實驗目的提供

From:http://tw.wingwit.com/Article/program/Oracle/201311/17979.html