在Oracle9i中,如何監(jiān)視索引并清除監(jiān)視信息
-使用Oracle9i新特性
Last Updated: Saturday, 2004-12-04 10:28 Eygle
對于DML操作來說，索引對于數(shù)據(jù)庫是一個性能負擔.假如索引沒有被有效的使用,那么其存在性就值得從新考慮.
1. 從Oracle9i開始,Oracle答應你監(jiān)視索引的使用:
SQL> connect scott/tiger@connerConnected to Oracle9i EnterPRise Edition Release 9.2.0.4.0 Connected as scottSQL> select index_name from user_indexes;INDEX_NAME------------------------------PK_DEPTPK_EMP開始監(jiān)視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停止監(jiān)視:SQL> alter index pk_dept nomonitoring usage;Index altered查詢索引使用情況,YES表示在監(jiān)視過程中索引被使用到: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之前，假如你不慎監(jiān)控了SYS.I_OBJAUTH1索引，并且不幸在重起數(shù)據(jù)庫之前沒有停止它，那么你的數(shù)據(jù)庫將會無法啟動，并且
不會給出任何錯誤信息。
以下這條簡單的語句可以輕易再現(xiàn)這個問題:
'ALTER INDEX SYS.I_OBJAUTH1 MONITORING USAGE'
假如你有了足夠好的備份(嚴重警告，請不要拿你的生產(chǎn)數(shù)據(jù)庫進行測試)，你可以嘗試一下:
[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.
此時，數(shù)據(jù)庫掛起，而且不會有任何提示，在alert<sid>.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
然后數(shù)據(jù)庫將會停在此處。
假如不知道此bug存在，你可能會一籌莫展的。
現(xiàn)在你能做的就是從備份中恢復，或者升級。
[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)
as
select 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_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where 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')建立.
所以以不同用戶登錄,你是無法看到其他用戶的索引監(jiān)視信息的,即使是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
此操作對數(shù)據(jù)庫沒有潛在的影響,但是請謹慎使用.作為實驗目的提供.
本文作者:
eygle,Oracle技術關注者,來自中國最大的Oracle技術論壇itpub.
www.eygle.com是作者的個人站點.你可通過Guoqiang.Gai@Gmail.com來聯(lián)系作者.歡迎技術探討交流以及鏈接交換.
原文出處:
http://www.eygle.com/internal/How.to.Monitor.Index.and.How.to.Clean.out.v$object_usage.htm
