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

From: Mercadante, Thomas F (LABOR) <"Mercadante,>
Date: Thu, 8 Jan 2009 14:58:58 -0500
Message-ID: <ABB9D76E187C5146AB5683F5A07336FFE096B2_at_EXCNYSM0A1AJ.nysemail.nyenet>


In the 10.2. docs:

file:///V:/Oracle10.2Doc/B19306_01/server.102/b14200/statements_7002.htm #i2095331


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.

See Also:

Oracle Database Reference for information on the STATISTICS_LEVEL
0.htm#REFRN10214> initialization parameter, including its affect on Oracle Database manageability features      

[] 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:


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.


Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

Received on Thu Jan 08 2009 - 13:58:58 CST

Original text of this message