Musings on tuning and the optimizer

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