Technology Network Community
Oracle Database
Fusion Middleware
Development Tools
Server & Storage Systems
Enterprise Management
Berkeley DB Family
Cloud Computing
Big Data
Business Intelligence
Migration and Modernization
E-Business Suite
PeopleSoft Enterprise
JD Edwards World
JD Edwards EnterpriseOne
User Productivity Kit Pro (UPK) and Tutor
Governance, Risk & Compliance (GRC)
Master Data Management (MDM)
Oracle CRM On Demand
On Demand: SaaS and Managed Applications
AutoVue Enterprise Visualization
Agile PLM
Endeca Experience Management
Fusion Applications
Archived Forums



General Questions

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

Related Links

Intriguing Data Dictionary Query
Can anyone help me ?
How to reduce SQL library cache miss ratio ?
unable to apply patch set on a Database..urgent!!!
WriteFile() failure
operating System Authentication on Winnt 4.0
XP and 8.1.7 client certification
Materialized View not refreshing
Memory allocation for SGA
Open database error
problem storing chinese characters
Oracle Data Guard 9i
Date and Time Format global for a database?
Java Pet Store
Bug in SQL*Plus with 8.1.6 instance.