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