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

Tom quoted the documentation for table monitoring:

> MONITORING | NOMONITORING
>
> 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
http://yong321.freeshell.org/computer/ParameterDependencyAndStatistics.doc 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       

--
http://www.freelists.org/webpage/oracle-l