RE: Changes to RULE based optimizer between Oracle8 and 9i

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Fri, 30 Jan 2004 13:13:34 -0500
Message-ID: <4001DEAF7DF9BD498B58B45051FBEA656D8114@25exch1.vicorpower.vicr.com>

Well if optimiser_mode=3Drule with stats doesn't invoke CBO then I = wonder why OEM is providing me with cost data??

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----

From: Keith Moore [mailto:kmoore7_at_jcpenney.com] Sent: Friday, January 30, 2004 12:59 PM
To: oracle-l_at_freelists.org
Subject: Re: Changes to RULE based optimizer between Oracle8 and 9i

Attached file included as plaintext by Ecartis --

We tried both queries with and without statistics. Same results.

Are you sure about OPIMIZER_MODE=3DRULE invoking the CBO if there are = statistics. The Reference manual says otherwise (not that it's never = been wrong).

Original Message -----=20 From: Goulet, Dick=20 To: Multiple recipients of list ORACLE-L=20 Sent: Friday, January 30, 2004 11:39 AM Subject: RE: Changes to RULE based optimizer between Oracle8 and 9i

Logic says one thing, experience says another. Question one, are the = tables analyzed? If so, since RBO is deprecated in 9i even setting = OPTIMIZER_MODE=3DRULE will invoke the CBO.

  Dick Goulet
  Senior Oracle DBA
  Oracle Certified 8i DBA=20

-----Original Message-----
From: Keith Moore [mailto:kmoore7_at_jcpenney.com] Sent: Friday, January 30, 2004 11:54 AM To: Multiple recipients of list ORACLE-L Subject: Changes to RULE based optimizer between Oracle8 and 9i

We move an application that uses OPTIMIZER_MODE=3DRULE from Oracle8 = to 9i. Most of it is fine, but there are two queries that have a very = different execution plan. In one case, the execution time increases from = less than a minute to more than an hour. Neither query uses any of the = new Oracle 9i features.

My understanding is that the Rule optimizer code has not changed, = except to account for new features like IOT's. Has anyone else seen this = type of behavior?

    Keith Moore
    Oracle Certified Professional
    972-431-5126
    kmoore7_at_jcpenney.com

Attached file included as plaintext by Ecartis --
Desc: Signature

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any = review,
dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer.

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

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

----------------------------------------------------------------

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


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Fri Jan 30 2004 - 12:13:34 CST