Search This Blog

Thursday, July 31, 2008

Oracle Monitoring Index Usage

Index is a associated structure with table, index is extra overhead statement with table, so if index is not utilized or unnessaccary index is created then drop that indexes.

Oracle provide a means of monitoring indexes to determine whether they are being used.

Let see in practical view.

SQL> drop table test purge;
Table dropped.


SQL> create table test as select * from all_objects;
Table created.

SQL> create index testidx on test(object_id);
Index created.

SQL> --now enable monitoring on index

SQL> alter index TESTIDX MONITORING usage;
Index altered.

SQL> ---execute query where index is created.

SQL> select count(*) from test where object_id=77204;
COUNT(*)

----------

1

SQL>---disable index mornitoring

SQL> alter index TESTIDX NOmonitoring usage;
Index altered.


SQL> ---now check the V$OBJECT_USAGE for index utilization. if we found nothing then drop index is better option

SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USED START_MONITORIN END_MONITORING

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

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

TESTIDX TEST NO YES 07/31/2008 19:51:20

07/31/2008 19:52:04

NOTE: Used column is indicate "index" is utilize or not.

1 comment:

Dipali Vithalani said...

Hi Taj,

How are you?

After a long time, I have visited your blog..

Got some nice stuff..

Regarding this article:

Good option..

I have two thoughts for deciding whether to use it in given situation:

1) We need to know how much extra load will be added on table activities if we start monitoring index usage like this.

2) Another thing is, using this command, we can moniter specific index.
There must be some other way , or query to dig up the system provided views to get the list of indexes with their usage information..
That can also be useful for this decision making (whether to grant the life to index or kill it )

Regards..