菜鳥學堂：
對于dml操作來說，索引對于數據庫是一個性能負擔.如果索引沒有被有效的使用,那么其存在性就值得從新考慮.1. 從oracle9i開始,oracle允許你監視索引的使用:
sql> connect scott/[email protected] to oracle9i enterprise edition release 9.2.0.4.0 connected as scottsql> select index_name from user_indexes;index_name------------------------------pk_deptpk_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:47sql>
2.oracle9i的bug
在9205之前，如果你不慎監控了sys.i_objauth1索引，并且不幸在重起數據庫之前沒有停止它，那么你的數據庫將會無法啟動，并且不會給出任何錯誤信息。
以下這條簡單的語句可以輕易再現這個問題:
'alter index sys.i_objauth1 monitoring usage'
如果你有了足夠好的備份(嚴重警告，請不要拿你的生產數據庫進行測試)，你可以嘗試一下:
[[email protected] 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 - productionwith the partitioning optionjserver 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> startuporacle instance started.
total system global area 80811208 bytesfixed size 451784 bytesvariable size 37748736 bytesdatabase buffers 41943040 bytesredo buffers 667648 bytesdatabase mounted.
此時，數據庫掛起，而且不會有任何提示，在alert<sid>.log文件中，你可以看到:
[[email protected] 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。
[[email protected] oradata]$ rm -rf conner[[email protected] oradata]$ cp -r connerbak/ conner[[email protected] 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> startuporacle instance started.
total system global area 80811208 bytesfixed size 451784 bytesvariable size 37748736 bytesdatabase buffers 41943040 bytesredo buffers 667648 bytesdatabase 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:47sql> select * from object_usage;select * from object_usage *error at line 1:ora-00942: table or view does not existsql> connect /as sysdbaconnected.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
此操作對數據庫沒有潛在的影響,但是請謹慎使用.作為實驗目的提供.
本文作者:eygle,oracle技術關注者,來自中國最大的oracle技術論壇itpub.www.eygle.com是作者的個人站點.你可通過[email protected]來聯系作者.歡迎技術探討交流以及鏈接交換.
原文出處:
http://www.eygle.com/internal/how.to.monitor.index.and.how.to.clean.out.v$object_usage.htm
