RULE versus CHOOSE - Basic Qs.

Qs. Is there ANY Benefit of keeping optimizer_mode=CHOOSE WITHOUT having Any Statistics Existent on the Application Tables , Indexes ?

Qs With NO Statistics Existant on ALL Objects , yet with optmizer_mode =

CHOOSE , does it mean that RULE (RBO) will be Used ?

Following Excerpts seem to imply the Same that RBO will be Used if NO Statistics Exist but with Optimizer mode set to CHOOSE

EXCERPTS from a Doc. Paper #169 :-

Determining which optimizer to use:

Determining which optimizer will be used to evaluate each query depends on several factors and can be set at either the instance level, session level or query level.
Instance Level:
OPTIMIZER_MODE - This parameter sets the default optimization behavior for the entire Oracle instance.

*CHOOSE - the default OPTIMIZER_MODE, differs determining which optimizer to use until each query is evaluated. If any table participating in a query has had statistics collected on it's data the CBO will be used to evaluate the query. For this reason it is essential for all tables in a query to have statistics collected or none of them have statistics collected.

Oracle's Cost Based Optimizer:

*If table statistics are missing or old the CBO can make bad decisions. If statistics are missing Oracle can only guess what data might be in the table.

THANKS SO MUCH ALL
> -----Original Message-----
> From: Jackson Andy - andjac [SMTP:andjac_at_acxiom.co.uk]
> Sent: Friday, July 06, 2001 1:28 PM
> To: LazyDBA.com Discussion
> Subject: RE: RULE versus CHOOSE
>
>
> I would strongly resist anyone who wanted to run a v8 database with
> RULE
> based optimization for the following reasons:
>
> - The CBO is aware of new Oracle features and can generate execution
> paths
> for them, eg partition elimination
> - The CBO is much better now than it used to be but in situations
> where you
> need a guaranteed execution path you can always use hints.
> - Development of the RBO stopped with v7 and Oracle peridocially make
> noises
> as though they will drop it in the future
> - The only part of the database that should use the RBO is the data
> dictionary
>
> The CBO is only as good as the database stats so you have to keep them
> as up
> to date as possible, even if it means a performance hit whilst the
> analyze
> command is running, a 10% estimation should be sufficient.
>
> There is lots of stuff on Metalink about this.
>
> Cheers
> Andy Jackson
>
> -----Original Message-----
> From: VIVEK_SHARMA [mailto:VIVEK_SHARMA_at_infy.com]
> Sent: Friday, July 06, 2001 5:51 AM
> To: LazyDBA.com Discussion
> Subject: RULE versus CHOOSE
>
>
> Database = Oracle 8.1.7.0.0 on SunOS 5.6 .
> Current Database Size = 20 GB
> This is only a Test One & the Live Production will be a BIGGER (100
> GB)
> One .
> OPTIMIZER_MODE = RULE & NO Statistics Exist Currently
>
> Qs.1 How does optimizer_mode=CHOOSE Compare with RULE ?
>
> Qs.2 Is there ANY Benefit of keeping optimizer_mode=CHOOSE WITHOUT
> having Any Statistics Existent on the Application Tables , Indexes ?
>
> If Statistics are DELETED on ALL Objects , yet with optmizer_mode =
> CHOOSE , does it behave in Exactly the Same manner as having
> optmizer_mode set to RULE Or are there Still Some Advantages which can
> be Reaped ?
>
> Qs.3 Are there any Disadvantages with Using RULE in 8.1.7.0 ?
>
> Qs.4 In Choose mode are there any Commonly known Standard Important
> Statistics' Fields/Values which can be Looked at to understand why
> optimizer took a particular path ?
> What Causes a Path to be Chosen in CHOOSE , we are largely ignorant
> about .
>
> NOTE - At a Customer's Database , Our Development Section Head wants
> to
> set optmizer_mode=RULE & keep it so . His Reasons :-
> - The path of the optimizer is more predictable when set to RULE
> - Any under-performance Issues would be Handled by Giving HINTS etc
> rather than Allowing the Optimizer to Choose / Compute it's own Path
> which may be a BAD One .
> - A Correct Path being Taken Today may in Time get Automatically
> Changed
> to a Worse Path somewhere in future (with the Stats getting OLD etc.)
> .
> - Lastly his Team will Take Responsibility for Any Performance Issues
> arising out of a Code underperforming .
>
> Qs Are there Any Best practices Documents / Links on RULE vs. CHOOSE ?
>
> Qs. What do you Folks Advice ? Should I Given in to the 80-20 Rule Or
> Still Endeavour to persist for optimizer_mode=CHOOSE ?
>
>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: VIVEK_SHARMA
  INET: VIVEK_SHARMA_at_infy.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).