ALTER INDEX MONITORING USAGE

In order to maintain an efficient database, it can be very useful to prune indexes from time to time as the system usage changes. An index that may have been used very often when the schema was first designed, may not be used after a new code release for example.

It’s also common when implementing a datawarehouse to put a lot of indexes on tables because it’s not entirely clear which columns need them. This is because users in a data warehouse environment can run an almost infinite number of different queries. This is where Oracle index monitoring comes in.

When using the index monitoring technique it is essential that you monitor the index usage over a long enough time period so as to cover all types of application usage. For example, monthly processing jobs, overnight batch job processes as well as day-to-day usage. Only you can know what time period you need to cover, as it depends completely on your application.

In order to turn index monitoring on, you should use the syntax:

ALTER INDEX <index_name> MONITORING USAGE;

Once turned on, you can query the V$OBJECT_USAGE view to see which indexes have been used. Note that this view is cleared each time that the Oracle instance is re-started, as per other V$ views.

In order to turn the monitoring off, you can use:

ALTER INDEX <index_name> NOMONITORING;

If you would like some information regarding improving the speed of your import by creating indexes after you’ve done the data import, have a read of my article on export & import performance.

Like it, share it...

Category: Indexes