Re: 12c Plan Stability for varying FORCE_MATCHING_SIGNATUREs - options?

Hi Chris,

Could it be adaptive features?

It's on by default but MOS 2187449.1 advises disabling it.

Ronan

On 2 Aug 2017 22:25, "Chris Taylor" <christopherdtaylor1994_at_gmail.com> wrote:

> I want to double-check something with you guys specifically related to 12c
> (12.1.0.2).
>
> (For the record, I have a lot of experience tuning statements, but not a
> lot of experience in 12c and am curious if things have changed any)
>
> We have a monster SQL statement that by necessity builds IN lists with
> varying values depending on the customer executing the statement.
>
> Each execution of this statement by a different customer results in a
> different FORCE_MATCHING_SIGNATURE.
>
> Oracle in our environment is consistently generating bad execution plans
> for this statement.
>
> Multiple test executions of the EXACT same statement is resulting in
> different plans from execution to execution - I believe this is related to
> "statistics feedback" as that is coming up in the NOTE section of the
> dbms_xplan output.
>
> 5 executions of the exact same statement resulted in 4 wildly different
> plans. Scenario:
> Test #1 - terrible plan
> Test #2 - great plan (statistics feedback used)
> Test #3 - variant plan of Test #2 with good results but slower than #2
> (statistics feedback used)
> Test #4 - terrible new plan (statistics feedback used)
> Test #5 - one of the previous plans but I don't remember which (statistics
> feedback used)
>
> I am WELL AWARE that there is something going on with statistics and the
> optimizer making a poor decision - I get that. (Something in our env is
> screwy - and am investigating root cause)
>
> What I need to know is, is there any way (especially in 12c) to get plan
> stability for statements that don't share a FORCE_MATCHING_SIGNATURE using
> the capabilities of the database?
>
> (SQL profiles obviously won't work and I don't think sql plan baselines
> will work but am looking for suggestions specific to the database
> capabilities available)
>
> Chris
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l