Oracle9i新特性-索引监视及注意事项[修正版]

在Oracle9i中,如何监视索引并清除监视信息

-使用Oracle9i新特性

Last Updated: Saturday, 2004-12-04 10:28 Eygle

对于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

  1<sid>.log文件中，你可以看到: 
  2
  3&gt; 
  4&gt;     [oracle@jumper bdump]$ tail -f alert_conner.log   
  5&gt;     &gt; Completed: ALTER DATABASE   MOUNT  
  6&gt;     &gt; Sat Dec  4 10:09:49 2004  
  7&gt;     &gt; ALTER DATABASE OPEN  
  8&gt;     &gt; Sat Dec  4 10:09:49 2004  
  9&gt;     &gt; LGWR: Primary database is in CLUSTER CONSISTENT mode  
 10&gt;     &gt; Thread 1 opened at log sequence 54  
 11&gt;     &gt;   Current log# 2 seq# 54 mem# 0: /opt/oracle/oradata/conner/redo02.log  
 12&gt;     &gt; Successful open of redo thread 1.  
 13&gt;     &gt; Sat Dec  4 10:09:49 2004  
 14&gt;     &gt; SMON: enabling cache recovery  
 15&gt;     &gt; Sat Dec  4 10:10:33 2004  
 16&gt;     &gt; Restarting dead background process QMN0  
 17&gt;     &gt; QMN0 started with pid=9  
 18  
 19---  
 20  
 21然后数据库将会停在此处。 
 22
 23如果不知道此bug存在，你可能会一筹莫展的。 
 24
 25现在你能做的就是从备份中恢复，或者升级。 
 26
 27&gt; 
 28&gt;     [oracle@jumper oradata]$ rm -rf conner  
 29&gt;     &gt; [oracle@jumper oradata]$ cp -R connerbak/ conner  
 30&gt;     &gt; [oracle@jumper oradata]$ sqlplus '/ as sysdba'
 31&gt; 
 32&gt; SQL*Plus: Release 9.2.0.4.0 - Production on Sat Dec 4 10:19:07 2004 
 33&gt; 
 34&gt; Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 
 35&gt; 
 36&gt; Connected to an idle instance. 
 37&gt; 
 38&gt; SQL&gt; startup   
 39&gt;  ORACLE instance started. 
 40&gt; 
 41&gt; Total System Global Area 80811208 bytes   
 42&gt;  Fixed Size 451784 bytes   
 43&gt;  Variable Size 37748736 bytes   
 44&gt;  Database Buffers 41943040 bytes   
 45&gt;  Redo Buffers 667648 bytes   
 46&gt;  Database mounted.   
 47&gt;  Database opened.   
 48&gt;  SQL&gt;  
 49  
 50---  
 51  
 523\. 在特殊的情况下,你可能需要清除这个v$object_usage视图中的信息. 
 53
 54  
 55Oracle的说法是,在下一次收集该对象的索引使用情况时会自动覆盖上一次的信息,不提供清除手段. 
 56
 57稍微研究了一下. 
 58
 59v$object_usage是基于以下基表建立起来的: 
 60
 61&gt; 
 62&gt;     create or replace view v$object_usage  
 63&gt;     &gt; (index_name, table_name, monitoring, used, start_monitoring, end_monitoring)  
 64&gt;     &gt; as  
 65&gt;     &gt; select io.name, t.name,  
 66&gt;     &gt;        decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),  
 67&gt;     &gt;        decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),  
 68&gt;     &gt;        ou.start_monitoring,  
 69&gt;     &gt;        ou.end_monitoring  
 70&gt;     &gt; from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou  
 71&gt;     &gt; where io.owner# = userenv('SCHEMAID')  
 72&gt;     &gt;   and i.obj# = ou.obj#  
 73&gt;     &gt;   and io.obj# = ou.obj#  
 74&gt;     &gt;   and t.obj# = i.bo#  
 75&gt;     &gt; /
 76&gt;                             
 77  
 78---  
 79  
 80注意到v$object_usage关键信息来源于OBJECT_USAGE表.   
 81另外我们可以注意一下,此处v$object_usage的查询基于userenv('SCHEMAID')建立.   
 82所以以不同用户登录,你是无法看到其他用户的索引监视信息的,即使是dba,但是可以从object_usage表中得到.   
 83
 84
 85&gt; 
 86&gt;     SQL&gt; select * from v$object_usage;
 87&gt;     
 88&gt;     INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
 89&gt;     ------------------------------ ------------------------------ --- --- ------------------- -------------------
 90&gt;     PK_DEPT                        DEPT                           NO  YES 10/28/2004 10:55:19 10/28/2004 10:55:47
 91&gt;     
 92&gt;     SQL&gt; select * from object_usage;
 93&gt;     select * from object_usage
 94&gt;                   *
 95&gt;     ERROR at line 1:
 96&gt;     ORA-00942: table or view does not exist
 97&gt;     
 98&gt;     
 99&gt;     SQL&gt; connect /as sysdba
100&gt;     Connected.
101&gt;     SQL&gt; /
102&gt;     
103&gt;           OBJ#      FLAGS START_MONITORING    END_MONITORING
104&gt;     ---------- ---------- ------------------- -------------------
105&gt;           6288          1 10/28/2004 10:55:19 10/28/2004 10:55:47     
106&gt;                        
107  
108---  
109  
110实际上我们清除了object_usage表的记录,实际上也就清空了v$object_usage的信息. 
111
112&gt; 
113&gt;     SQL&gt; delete from object_usage;
114&gt;     
115&gt;     1 row deleted.
116&gt;     
117&gt;     SQL&gt; commit;
118&gt;     
119&gt;     Commit complete.
120&gt;     
121&gt;     SQL&gt; select * from v$object_usage;
122&gt;     
123&gt;     no rows selected
124&gt;     					    
125  
126---  
127  
128此操作对数据库没有潜在的影响,但是请谨慎使用.作为实验目的提供. 
129
130本文作者:   
131eygle,Oracle技术关注者,来自中国最大的Oracle技术论坛 itpub .   
132www.eygle.com 是作者的个人站点.你可通过 [email protected] 来联系作者.欢迎技术探讨交流以及链接交换. 
133
134* * *
135
136原文出处: 
137
138http://www.eygle.com/internal/How.to.Monitor.Index.and.How.to.Clean.out.v$object_usage.htm 
139
140* * *</sid>