RE: Execution plan changing
Date: Fri, 14 Sep 2012 15:38:28 +0000
Message-ID: <1184E7EFAB1D1C47A5038D06F64BE926020232F5_at_XM-MBX-02-PROD.ad.uchicago.edu>
Thanks Paul,
I am going to try and determine if the cardinality feedback is what might be resulting in what we are seeing. Is looking for it in the explain plan the best way to determine if it was used?
Thanks,
Mike
From: Paul Drake [mailto:bdbafh_at_gmail.com]
Sent: Friday, September 14, 2012 9:41 AM
To: Michael Schmitt
Cc: oracle-l_at_freelists.org
Subject: Re: Execution plan changing
Michael,
Optimizer feedback being enabled results in the Oracle Cost Based Optimizer intentionally changing the execution plan based upon prior executions of the statement.
https://blogs.oracle.com/optimizer/entry/cardinality_feedback
The bogus parameter that I included in the prior post was intended as a joke. If you want to disable cardinality feedback to limit the number of variables to consider in testing, execute the following:
alter system set "_optimizer_use_feedback"úlse scope=both;
Typically one is not supposed to set underscore parameters without the direction of Oracle Support. This is one of the parameters that is an exception to that rule. Please check the following notes in Metalink.
Cardinality Feedback - Frequently Asked Questions [ID 1344937.1]
[Image removed by sender.]Bug 8608703 - SubOptimal Execution Plan created by Cardinality Feedback [ID 8608703.8] hth.
Paul
On Fri, Sep 14, 2012 at 10:30 AM, Michael Schmitt <mschmitt_at_uchicago.edu<mailto:mschmitt_at_uchicago.edu>> wrote: Hi Paul,
Sorry, both of these parameters are a bit over my head. I will need to look into them. We are running on 11.2.0.1
Thanks,
Mike
From: Paul Drake [mailto:bdbafh_at_gmail.com<mailto:bdbafh_at_gmail.com>]
Sent: Friday, September 14, 2012 9:21 AM
To: Michael Schmitt
Cc: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
Subject: Re: Execution plan changing
results in "_execution_plan_deterministic"úLSE
This feature was introduced in 11g.
Perhaps it is worth using in 11.2.0.3.
IMHO, It was not worth using in 11.2.0.2.
On Fri, Sep 14, 2012 at 9:54 AM, Michael Schmitt <mschmitt_at_uchicago.edu<mailto:mschmitt_at_uchicago.edu>> wrote:
_optimizer_use_feedback=TRUE
From: Paul Drake [mailto:bdbafh_at_gmail.com<mailto:bdbafh_at_gmail.com>]
Sent: Friday, September 14, 2012 8:46 AM
To: Michael Schmitt
Cc: Christopher.Taylor2_at_parallon.net<mailto:Christopher.Taylor2_at_parallon.net>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
Subject: Re: Execution plan changing
SQL> show parameter "_optimizer_use_feedback".
--
http://www.completestreets.org/faq.html
http://safety.fhwa.dot.gov/ped_bike/docs/pamanual.pdf
--
http://www.completestreets.org/faq.html
http://safety.fhwa.dot.gov/ped_bike/docs/pamanual.pdf
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Sep 14 2012 - 10:38:28 CDT