Re: Changes to RULE based optimizer between Oracle8 and 9i
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 31 Jan 2004 11:02:04 +0800
Try running the queries with the explicit /*+ Hints */ RULE and CHOOSE.
Query v$SQL_PLAN in 9i to see the actual execution plan.
Hemant
At 11:58 AM 30-01-04 -0600, you wrote:
>-- Attached file included as plaintext by Ecartis --
>
>We tried both queries with and without statistics. Same results.
>
>Are you sure about OPIMIZER_MODE=RULE invoking the CBO if there are
>statistics. The Reference manual says otherwise (not that it's never been
>wrong).
> ----- Original Message -----
> From: Goulet, Dick
> To: Multiple recipients of list ORACLE-L
> 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=RULE will invoke the CBO.
>
> Dick Goulet
> Senior Oracle DBA
> Oracle Certified 8i DBA
>
> -----Original Message-----
> From: Keith Moore [mailto:kmoore7@xxxxxxxxxxxx]
> 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=RULE 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@xxxxxxxxxxxx
>
>
>
>-- 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@xxxxxxxxxxxxx
>put 'unsubscribe' in the subject line.
>--
>Archives are at //www.freelists.org/archives/oracle-l/
>FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
http://hkchital.tripod.com {last updated 24-Jan-04}
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
