Well.....that looks interesting.
I'll check it out. Thanks!
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
