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



Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Saivishnu.

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

Answered by: Connor McDonald - Last updated: November 11, 2019 - 2:01 am UTC

Category: Database Administration - Version: 11.2.0

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Oracle Database 18c

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.

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 we 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.

and you rated our response

  (1 rating)


December 01, 2019 - 6:35 pm UTC

Reviewer: A reader

More to Explore


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