Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Musings on tuning and the optimizer

RE: Musings on tuning and the optimizer

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Sun, 27 May 2001 21:52:28 -0700
Message-ID: <F001.00310D66.20010527213522@fatcity.com>

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

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU
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). Received on Sun May 27 2001 - 23:52:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US