Oracle9i的索引监视及注意事项

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>

[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.

[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

[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>

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#

/

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

SQL> delete from object_usage; 1 row deleted. SQL> commit; Commit complete. SQL> select * from v$object_usage; no rows selected