Monitoring Index Usage


                                                                     

oracle@anar> create table test1 (id number);

Table created.

oracle@anar> insert into test1 values (1);

1 row created.

oracle@anar> insert into test1 values (2);

1 row created.

oracle@anar> insert into test1 values (3);

1 row created.

oracle@anar> commit;

Commit complete.

oracle@anar> create index idx_test1_id on test1(id);

Index created.

oracle@anar> select table_name, index_name, monitoring, used from v$object_usage;

no rows selected

oracle@anar> alter index idx_test1_id monitoring usage;

Index altered.

oracle@anar> select table_name, index_name, monitoring, used from v$object_usage;

TABLE_NAME                     INDEX_NAME                     MON USE
------------------------------ ------------------------------ --- ---
TEST1                          IDX_TEST1_ID                   YES NO
oracle@anar> select count(*) from test1 where id=1;

COUNT(*)
óóó-
1

oracle@anar> select table_name, index_name, monitoring, used from v$object_usage;

TABLE_NAME                     INDEX_NAME                     MON USE
------------------------------ ------------------------------ --- ---
TEST1                          IDX_TEST1_ID                   YES YES
oracle@anar> alter index idx_test1_id nomonitoring usage;

Index altered.

oracle@anar> select table_name, index_name, monitoring, used from v$object_usage;

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

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: