Leng Kaing wrote: > >Hi guys, > > > >Apologies if I'm revisiting a beaten path but I've tried to search the >archive, metalink and google and couldn't find my answer (or it may have >been hiding). So I'll ask the question (again)... > > > >What is the performance impact of turning on MONITORING at the table >level? Ie. ALTER TABLE x MONITORING. Will it have a negative impact on >our production system? > > Leng, I am running 9.2.0.5 on Solaris, all my production tables are in the monitoring mode and there are no adverse effects on my production database. The explanation is simple: When tables are in monitoring mode (default in 10g), the only things that are update are memory tables (X$). As Mr. Litchfield has shown, those tables are not protected by the transaction mechanisms, which makes updating them much cheaper. Unless you already have CPU bound system, you will not suffer from performance degradation if you enable monitoring for 250 tables, like I did. It also takes away any purpose from gathering statistics based on STALE status. Niall has demonstrated that SYS.DBA_TAB_MODIFICATIONS, the entity behind "dbms_stats.gather_stale" will record inserts, even if those are eventually rolled back. A single failed load with SQL*Loader can have no effect whatsoever on the table itself, but cant invalidate your statistics and trigger an expensive DBMS_STATS job. Unrelated to that, I've been warned about the mistake I was consistently making in our communications and I have to humbly apologize. -- Mladen Gogala Oracle DBA Ext. 121 -- //www.freelists.org/webpage/oracle-l