 在oracle9i中,怎样监控索引并清除监控信息 -使用oracle9i新特性 last updated: saturday, 2004-12-04 10:28 eygle 对于dml操作来说，索引对于数据库是个性能负担.假如索引没有被有效的使用,那么其存在性就值得从新考虑. 1. 从oracle9i开始,oracle允许您监控索引的使用: 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开始监控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 假如您有了足够好的备份(严重警告，请不要拿您的生产数据库进行测试)，您能够尝试一下: [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 [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 然后数据库将会停在此处。 假如不知道此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) 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)建立. 所以以不同用户登录,您是无法看到其他用户的索引监控信息的,即使是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是作者的个人站点.您可通过guoqiang.gai@gmail.com来联系作者.欢迎技术探讨交流连同链接交换. 原文出处: http://www.eygle.com/internal/how.to.monitor.index.and.how.to.clean.out.v$object_usage.htm |
喜欢本文，那就收藏到： |
.text:''):(d.getSelection?d.getSelection():'');void(keyit=window.open('http://my.poco.cn/fav/storeIt.php?t='+escape(d.title)+'&u='+escape(d.location.href)+'&c='+escape(t)+'&img=http://www.h-strong.com/blog/logo.gif','keyit','scrollbars=no,width=475,height=575,status=no,resizable=yes'));keyit.focus(); "添加到POCO网摘"){kind=logo}
Rss Feed
