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

From: Jared Still <jkstill_at_gmail.com>
Date: Thu, 8 Jan 2009 12:11:52 -0800
Message-ID: <bf46380901081211q3001730ep50255ca1d2ec2ea4_at_mail.gmail.com>



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_at_labor.state.ny.us> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *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
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 08 2009 - 14:11:52 CST

Original text of this message