Skip to Main Content
  • Questions
  • Impact of Index Monitoring in production server

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Saivishnu.

Asked: November 08, 2019 - 7:04 am UTC

Last updated: November 11, 2019 - 2:01 am UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

Hi, we are planning to change global index to local index for partitioned table in production. Since we need to drop an index and create it again and we don't know the time it will take, we decided to monitor the index which are being used by the applications.
ALTER INDEX IDX_NAME MONITORING USAGE

Our idea is that since we have 3 partitions and only one partition holds 90% of the data, it is likely that other 2 partitions will be using table scan instead of the global index. Most of our processing is also on the other 2 partitions. Since we can't confirm for sure that the index is not being used, we decided to monitor the index for 5 days. If it is not being used, then we will convert those index into local index. Problem is that, it will result in hard parse of the sql statements against the table with corresponding indexes that are being monitored. We have decided to monitor indexes of just one table. But that table got huge amount of data and it is used frequently. How badly will it affect the production server?

and Connor said...

The impact is minimal (it only occurs for a parse for a query that might use the index, and not for DML). Note that monitoring is very much a binary thing. Once an index is detected as used, we set "YES" in the monitored column. We dont store a count, or when, and the only way to clear the "YES" is to set it back to not being monitored. So as long as it is *just* a simple: "Is my index ever being used", the monitoring should be an OK means for measuring.

But note that an index might be used just for its statistics and not its execution, and index monitoring will not detect that.

In terms the recreation, as an aside, you can so an EXPLAIN PLAN FOR CREATE INDEX ..., and you will get an estimated duration/size to recreate the index.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, December 01, 2019 - 6:35 pm UTC


More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.