Please don't sent me private email - especially email with large
attachements - in response to a public discussion on the list server.
Did you not notice the llines in the trace file saying
- WARNING: SOME HINTS HAVE ERRORS *********
Maybe that's a clue - but maybe it's a side effect of the optimizer trying
to use the SPB and ignoring those hints. Whatever the reason for its
appearance, one of the hints with an error (err=20) was: OPT_ESTIMATE
(TABLE "D"_at_"SEL$8" ROWS=10.000000 ) )
so that's a useful clue about a better approach to hinting the
cardinality(10)
Then there's the line
SPM: couldn't reproduce any enabled+accepted plan so using the
cost-based plan, planId = 460841780
So clearly Oracle tried using your baseline, but didn't get the same plan
hash value as before.
Then you might recall an earlier post of mine saying:
*In particular the one that leap to mind is the fact that you have several
MERGE() hints in the outline - and there's an enhancement in 12.2.0.1 which
deals with the problem that SPB's that contain MERGE() hints don't always
reproduce the same plan because they don't specify what they merge into,
only what they merge from. The unambiguous 12.2. syntax looks like:
merge(_at_qb1 > qb2), e.g. merge(_at_SEL$12 > SEL$641071AC). So it's possible
that with some change in estimated numbers the optimizer has merged query
blocks in a different order - getting to the same final query block - and
failed to reproduce the plan. *
Did you try searching MOS for details about that enhancement, or about a
bug where SPM plans didn't always reproduced with merge() hints. If you had
you might have found:
Bug 22542281 - MERGE hint syntax not explicit enough / syntax extended (Doc
ID 22542281.8)
and then followed the patch link to find that there are several patches
relating to 12.1.0.2 - so maybe you could patch your Oracle and get the SPB
working.
Finally - a little creative speculation - maybe when Oracle tries to apply
the baseline the initial "ignore_optim_embedded_hints" makes it ignore the
cardinality hint, so the plan that appears is affected by the sampled
content of the collection. IF (note the IF) that's the case then could you
bypass the problem by using the sql_patch to add the cardinality
(opt_estimate) hint until you've had a chance to test the patch for the
known defect.
Regards
Jonathan Lewis
