Performance hit due to Monitoring

[sapatos]

Does anyone have experience with setting up monitoring on a database, and any possible performances hits taken because of this.

I'm looking at setting up dbms_stats and see that its possible to gather only stale stats, but this requires monitoring to be set up for the dba_tab_modifications table/view. Is the performance hit of maintaining these stats any higher than negligible?

Also I'm checking into index usage with the view to drop ones not used. Was thinking of going about it with the alter index monitoring command. Does this cause any performance hits at all?

I'm shy of running this in production without any information as its a high volume/critical application.

Thanks for any replies.

 

[SantaMufasa]

Sapatos,

Yes, there is a cost to monitoring. The actual cost on your database will depend upon many factors, not least of which is what you are monitoring, and when/how often you do that monitoring.

If you are conducting the monitoring to identify when you should re-gather statistics, then my suggestion is that you can save yourself monitoring costs by just re-gathering statistics an a scheduled basis when your database is least active. (That is what we do with great success.)

We also have reaped significant benefits by instructing Oracle to just SAMPLE 10 percent of the rows in the table. The sampling functionality produces remarkably accurate statistics that differ from a full sampling by completely insignificant amounts.

If you must monitor your database, but want to test the costs of implementation, then I recommend that you implement the monitoring on your test-database environment first, then if the results are satisfactory, then implement on your production environment next, with a contingency to disable the monitoring if you detect any unacceptable degradation in production performance.

Please post your findings back here in this thread so that others may benefit from both your question and the discoveries that accompany it.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[stefanhei]

We did not see any performance hits when we turned on monitoring.
On tables, which are loaded via some kind of batch process we did not enable monitoring but gather statistics after loading.
We use gather_schema_stats instead of gather_database_stats to have better control of timing.
Anyway I'd follow Dave's advice to try on your test-environment first, as you might find tables there, which you wish to handle seperately.

Stefan