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

ORA - 01033
Stored Procedure
extended audit trail for XML
Analyzing Load Profile[Tuning]
Shell script question
Copy DB to a new server
DBA_REGISTRY inconsistent
unable to login through isqlplus
Analytic Function
Full Database Exp & Imp
Explain the use of setting GLOBAL_NAMES equal to TRUE
Delicate issue with locked user
What role/roles require to administer OEM DB control jobs?