RE: CBO Question - Is 'monitoring' used to calculate query plan?

From: Yong Huang <>
Date: Fri, 9 Jan 2009 10:13:02 -0800 (PST)
Message-ID: <>


Tom quoted the documentation for table monitoring:

> In earlier releases, you could use these clauses to start or stop the
> collection of modification statistics on this table. These clauses have
> been deprecated.
> * If you formerly collected modification statistics on tables by
> using the DBMS_STATS package in GATHER AUTO or GATHER STALE mode, then
> you no longer have to do anything. Oracle Database now collects these
> statistics automatically, and the MONITORING and NOMONITORING keywords
> in existing code will not cause an error.
> * If, for performance reasons, you do not want to collect
> modification statistics on any tables, then you should set the
> STATISTICS_LEVEL initialization parameter to BASIC. Be aware, however,
> that doing so disables a number of manageability features.

I just want to point out that you can't control table monitoring on a table by table basis in 10g. The parameter _DML_MONITORING_ENABLED, controlled by statistics_level, has session or system scope. My article at talks about it. It specifically discusses the impact of setting statistics_level to a non-default value, typical.

Greg answered the question that table monitoring only affects dbms_stats's behavior. I guess in this respect it's similar to column usage info in col_usage$, although the latter is not visible in any documented view.

Yong Huang       

Received on Fri Jan 09 2009 - 12:13:02 CST

Original text of this message