RE: CBO Question - Is 'monitoring' used to calculate query plan?
Date: Fri, 9 Jan 2009 10:13:02 -0800 (PST)
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
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 HuangReceived on Fri Jan 09 2009 - 12:13:02 CST