Monitoring Index Usage

guang@db11r2> create table test1 (id number);

Table created.

guang@db11r2> insert into test1 values (1);

1 row created.

guang@db11r2> insert into test1 values (2);

1 row created.

guang@db11r2> insert into test1 values (3);

1 row created.

guang@db11r2> commit;

Commit complete.
guang@db11r2> create index idx_test1_id on test1(id);

Index created.

guang@db11r2> select table_name, index_name, monitoring, used from v$object_usage;

no rows selected

guang@db11r2> alter index idx_test1_id monitoring usage;

Index altered.

guang@db11r2> select table_name, index_name, monitoring, used from v$object_usage;

TABLE_NAME                     INDEX_NAME                     MON USE
------------------------------ ------------------------------ --- ---
TEST1                          IDX_TEST1_ID                   YES NO

guang@db11r2> select count(*) from test1 where id=1;

COUNT(*)
———-
1

guang@db11r2> select table_name, index_name, monitoring, used from v$object_usage;


TABLE_NAME                     INDEX_NAME                     MON USE
------------------------------ ------------------------------ --- ---
TEST1                          IDX_TEST1_ID                   YES YES

guang@db11r2> alter index idx_test1_id nomonitoring usage;

Index altered.

guang@db11r2> select table_name, index_name, monitoring, used from v$object_usage;

TABLE_NAME                     INDEX_NAME                     MON USE
------------------------------ ------------------------------ --- ---
TEST1                          IDX_TEST1_ID                   NO  YES

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: