在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#
/
                       
数据挖掘研究院