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

  • From: Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
  • To: Willy Klotz <willyk@xxxxxxxxxxx>
  • Date: Fri, 4 Aug 2017 08:44:34 -0500

Yep, this seems likely to be the root cause I was looking for.  We do have
multiple sets of extended statistics on some of the affected tables.

If I alter my session and disable "_optimizer_enable_extended_stats" then
the query performs as expected instead of going wild.

Now I just have to decide if I want to disable this system wide, use a
schema trigger for specific sessions/machines, or have the developer hint
the one query I've identified.  I'm wondering how many other queries are
being affected by this as our CPU usage is terribly high all the time.

Regards,
Chris


On Fri, Aug 4, 2017 at 6:19 AM, Willy Klotz <willyk@xxxxxxxxxxx> wrote:

Chris,



this is not what you was asking for – but maybe it can help you
nevertheless. Did you check this one:



Bug 23197730 - high parse time and suboptimal plan with multiple inlist on
12c



Regards

Willy



*Von:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
*Im Auftrag von *Chris Taylor
*Gesendet:* Mittwoch, 2. August 2017 23:25
*An:* ORACLE-L <oracle-l@xxxxxxxxxxxxx>
*Betreff:* 12c Plan Stability for varying FORCE_MATCHING_SIGNATUREs -
options?



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









Other related posts: