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
