Re: Execution plan changing

From: Paul Drake <bdbafh_at_gmail.com>
Date: Fri, 14 Sep 2012 10:41:26 -0400
Message-ID: <CAPptggWkVH79i=8EMqkAZcZvozCQDOTBjz1B98P1tO72UPP+Fw_at_mail.gmail.com>

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"=false 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]

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>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]
> *Sent:* Friday, September 14, 2012 9:21 AM
> *To:* Michael Schmitt
> *Cc:* oracle-l_at_freelists.org
>
> *Subject:* Re: Execution plan changing****
>
> ** **
>
> results in "_execution_plan_deterministic"=FALSE
>
> 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>
> wrote:****
>
> _optimizer_use_feedback=TRUE****
>
> ****
>
> *From:* Paul Drake [mailto:bdbafh_at_gmail.com]
> *Sent:* Friday, September 14, 2012 8:46 AM
> *To:* Michael Schmitt
> *Cc:* Christopher.Taylor2_at_parallon.net; 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 - 09:41:26 CDT