oracle索引的跟踪

一、我们先通过一个例子具体说明“ALTER INDEX MONITORING USAGE”的使用方法：

1、建测试表

create table test(id number(3),name varchar2(10));

insert into test values(1,'aaaaaaaa');

insert into test values(2,'www.ncn.cn');

insert into test values(3,'aadfaaaa');

insert into test values(4,'gototop');

insert into test values(5,'shenzhen');

insert into test values(6,'china');

commit;

alter table test add (constraint test_pk primary key (id));

2、查询v$object_usage(因为没有监视，所以还看不到内容)

column index_name format a12

column monitoring format a10

column used format a4

column start_monitoring format a19

column end_monitoring format a19

select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;

SQL> l

1* select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage

SQL> /

no rows selected

Elapsed: 00:00:00.00

3、开始监控索引的使用情况

SQL> alter index test_pk monitoring usage;

Index altered.

Elapsed: 00:00:00.05

4、查询v$object_usage(可以看到正监视中)

SQL> select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING

------------ ---------- ---- ------------------- -------------------

TEST_PK YES NO 05/15/2003 13:28:22

Elapsed: 00:00:00.00

5、使用索引进行查询

SQL> set autotrace on explain

SQL> select * from test where id = 2;

ID NAME

---------- ----------

2 www.ncn.cn

Elapsed: 00:00:00.00

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'

2 1 INDEX (UNIQUE SCAN) OF 'TEST_PK' (UNIQUE)

SQL> set autotrace off

SQL> /

ID NAME

---------- ----------

2 www.ncn.cn

Elapsed: 00:00:00.00

SQL>

从上我们可以看到确实使用了索引。

6、查询v$object_usage(可以看到索引被使用过，但目前还处于被监视过程中)

SQL> select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING

------------ ---------- ---- ------------------- -------------------

TEST_PK YES YES 05/15/2003 13:28:22

Elapsed: 00:00:00.00

7、停止监视，并查询v$object_usage

SQL> alter index test_pk nomonitoring usage;

Index altered.

Elapsed: 00:00:05.03

SQL> select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING

------------ ---------- ---- ------------------- -------------------

TEST_PK NO YES 05/15/2003 13:28:22 05/15/2003 13:40:00

Elapsed: 00:00:00.64

到此为止，监视结束，MONITORING为NO，END_MONITORING给出了时间戳。