Does Table monitoring affect performance?
We want to turn on monitoring so that we can determine which indexes are not being used. This will be for about 10 tables. We'll have to check for a period of about 2 weeks. During this two weeks we'll have a batch process run. Will having the table monitoring on affect the performance of our queries and batch processes? If so, will there be a significant degradation? We are on Oracle9i Enterprise Edition Release 126.96.36.199.0 - 64bit Production Edited by: user1175547 on Jul 6, 2010 8:30 AM
We have the monitoring enabled for around 3000 tables (4500 odd indexes) in one of the databases for all time and we have never encountered any performance issues.
Yes. In most cases not a lot but there is obviously overhead. Most likely, for a two week period, you will have no issues and I doubt you will see a significant impact on performance.
Also keep in mind when considering indexes to remove that some indexes may not be shown as used e.g. an index on a fk column can be usefull when deleting form the parent table and also in 11g the optimiser may use index stats to get a better estimate of the cardinality of a query/join so removing indexes that appear unused can have side effects.
Thanks for all of your answers.