Re: CBO Question - Is 'monitoring' used to calculate query plan?
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- Date: Thu, 8 Jan 2009 12:11:52 -0800
Ah, thanks Tom.
I recall now that a statistics level of BASIC disables this.
The copy of the docs I have do not have the bit about Monitoring in them.
It still doesn't answer the question however of "Does the CBO use the stats
seen in all_tab_modifications for query plans?"
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
On Thu, Jan 8, 2009 at 11:58 AM, Mercadante, Thomas F (LABOR) <
Thomas.Mercadante@xxxxxxxxxxxxxxxxx> wrote:
> Jared,
>
>
>
> In the 10.2. docs:
>
>
> file:///V:/Oracle10.2Doc/B19306_01/server.102/b14200/statements_7002.htm#i2095331
>
>
>
> Tom
>
> *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_LEVELinitialization parameter to
> BASIC. Be aware, however, that doing so disables a number of
> manageability features.
>
>
>
> *See Also:*
>
> *Oracle Database Reference* for information on the
> STATISTICS_LEVELinitialization parameter, including its affect on Oracle
> Database
> manageability features
>
>
>
>
>
>
> ------------------------------
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Jared Still
> *Sent:* Thursday, January 08, 2009 2:53 PM
> *To:* Oracle-L Freelists
> *Subject:* CBO Question - Is 'monitoring' used to calculate query plan?
>
>
>
> From the 9i SQL manual:
> *MONITORING Clause*
>
> Specify MONITORING if you want Oracle to collect modification statistics
> on *table*. These statistics are estimates of the number of rows affected
> by DML statements over a particular period of time. *They are available
> for use by the optimizer *or for analysis by the user.
>
>
> I can't find such a reference for 10g or 11g.
>
> Does anyone know definitively if that is still the case for newer versions
> of Oracle?
>
> A link to such documentation would be useful if you have it handy.
>
> Thanks,
>
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>
Other related posts:
