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
[EMAIL PROTECTED]
-----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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
