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>

--------------------next---------------------

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

--------------------next---------------------