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