Re: Musings on tuning and the optimizer

On Sunday 27 May 2001 22:35, MacGregor, Ian A. wrote:
> Isn't elimination of common subexpressions the default in 8.1.7? Also, the
> optimizer is sometimes overzealous and eliminates essential expressions.
> My source for this is Peoplesoft which suggests setting

Yes, that is the default for 8.1.7. My comment was just that I thought it interesting that they refer to a hidden parameter in the manual.

Jared

> optimizer_features_enable = 8.1.6
>
> When using their products against an 8.1.7 database.
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> ian_at_slac.stanford.edu
>
> -----Original Message-----
> Sent: Sunday, May 27, 2001 12:33 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Have run across some interesting things while reading up on the optimizer.
>
> First of all, there is a very interesting paper on MetaLink titled
> "Cost Based Optimizer - Common Misconceptions and Issues".
>
> This is note 35934.1.
>
> Among other things, it makes it clear under what conditions the CBO
> will be used. In a nutshell, any hint other than RULE will invoke the CBO.
>
> Are you using hints in your PL/SQL? No? Better read this paper. :)
>
> This interesting line was found in the 'Designing and Tuning for
> Performance' Manual.
>
> "Common subexpression elimination is enabled with initialization
> parameter OPTIMIZER_FEATURES_ENABLE or by setting the
> _ELIMINATE_COMMON_SUBEXPR parameter to TRUE. "
>
> Notice the sanctioned use of a hidden parameter.
>
> Always thought that the RBO joins your tables in the order found in the
> FROM clause? Think again.
>
> A partial quote from the same manual
>
> "Usually, the optimizer does not consider the order in which tables
> appear in the FROM clause when choosing an execution plan. The optimizer
> makes this choice by applying the following rules in order: "
>
> This from Chapter 4, 'The Optimizer"
>
> How about CBO ignoring hints? It will ignore the ORDERED hint on outer
> joins if it violates certain conditions.
>
> Speaking of the ORDERED hint, it can greatly reduce parse times when
> joining many tables. Obvious when you think about it.
>
> All kinds of interesting stuff when you Read The Fine Manual. ;)
>
> Jared

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.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).