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