RE: Execution plan changing
- From: Michael Schmitt <mschmitt@xxxxxxxxxxxx>
- To: Paul Drake <bdbafh@xxxxxxxxx>
- Date: Fri, 14 Sep 2012 15:38:28 +0000
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@xxxxxxxxx]
Sent: Friday, September 14, 2012 9:41 AM
To: Michael Schmitt
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxx<mailto:mschmitt@xxxxxxxxxxxx>> 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@xxxxxxxxx<mailto:bdbafh@xxxxxxxxx>]
Sent: Friday, September 14, 2012 9:21 AM
To: Michael Schmitt
Cc: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxx<mailto:mschmitt@xxxxxxxxxxxx>> wrote:
_optimizer_use_feedback=TRUE
From: Paul Drake [mailto:bdbafh@xxxxxxxxx<mailto:bdbafh@xxxxxxxxx>]
Sent: Friday, September 14, 2012 8:46 AM
To: Michael Schmitt
Cc: Christopher.Taylor2@xxxxxxxxxxxx<mailto:Christopher.Taylor2@xxxxxxxxxxxx>;
oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
