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: optimizer_features_enabled

RE: optimizer_features_enabled

From: Loughmiller, Greg <greg.loughmiller_at_cingular.com>
Date: Fri, 20 Aug 2004 07:54:42 -0400
Message-ID: <5164A49467308C45AD50661F8CFDC343192011A5@s30342g004004.wdc.cingular.net>


Some of our teams have found that your SQL statements could get consistent execution plans between the versions of Oracle.. In some cases, we had the execution plans/explain plans take a nose dive for performance in 9.2 CBO. So without re-writing the SQL; the "optimizer_features_enabled" feature "assisted" some to obtain similar performance as the 817 CBO provided. In a couple of cases; we used it rather successfully. And the latest effort it was not an option after performing testing. The dev team identified some undocumented parameters that assisted in providing better results.
But the end game was to weed out the SQL in testing; and change it so the 92 optimizer "liked" it better..

greg

-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com] Sent: Friday, August 20, 2004 12:00 AM
To: oracle-l_at_freelists.org
Subject: Re: optimizer_features_enabled

I have not done exhaustive tests with that parameter, but based on my experience and from what I read from other people on newsgroups I have the impression that it is only certain features which get disabled when backdating optimizer_features enabled (just as the name suggests). You don't get total 8.1.7 behaviour by setting optimizer_features_enabled = 8.1.7. New algorithms and heuristics appear to be unaffected. But I could be wrong.
There is a metalink note that explains which features are enabled based on which setting.

At 07:23 PM 8/19/2004, you wrote:
>Hi,
>
>I have a question around the
>optimizer_features_enabled parameter. If we set this
>parameter to 8.1.7 in a 9.2 database does the RDBMS
>kernel use 8.1.7 optimization algorithms only or does
>it use the 9.2 optimization algorithms but limit the
>CBO to only using 8.1.7 CBO features?
>
>Any clarification on this would be much appreciated.
>
>Regards,
>
>Mark

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com



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 Aug 20 2004 - 06:50:58 CDT

Original text of this message

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