Hi Jonathan,
First of all Thank you for all your help and suggestions with this issue
and taking time to go through the trace.
I was generally searching MOS about SPM bugs but not with Merge and did not
notice the bug. Bug 22542281 could be a relevant bug here and got the
backport for this patch for AIX. We are going to apply this patch on the PT
environment and test. I will keep you posted about the outcome.
I missed the Hint error section in trace. But I saw the below in good plan
where it picks plan baseline and Oracle was transformed Cardinality hint to
opt_estimate
(SELECT /*+ OPT_ESTIMATE (TABLE "D"@"SEL$7" ROWS=10.000000 ) */
"D"."COLUMN_VALUE" "COLUMN_VALUE" FROM (SELECT VALUE(KOKBF$0)
"COLUMN_VALUE" FROM TABLE( (SELECT
CAST("RXAPPL"."COMMON_PKG"."F_IN_LIST_NUM"(:B1) AS "IN_LIST_TYPE_1_6_NUM")
"CAST(COMMON_PKG.F_IN_LIST_NUM(" FROM "SYS"."DUAL" "D")) "KOKBF$0") "D"
WHERE ROWNUM>=0)
where as in bad plan
(SELECT /*+ MERGE MERGE */ "D"."COLUMN_VALUE" "COLUMN_VALUE" FROM (SELECT
VALUE(KOKBF$0) "COLUMN_VALUE" FROM TABLE( (SELECT
CAST("RXAPPL"."COMMON_PKG"."F_IN_LIST_NUM"(:B1) AS "IN_LIST_TYPE_1_6_NUM")
"CAST(COMMON_PKG.F_IN_LIST_NUM(" FROM "SYS"."DUAL" "D")) "KOKBF$0") "D"
WHERE ROWNUM>=0)
We will check on sql_patch with opt_estimate hint too.
Thanks,
Mani
On Fri, Aug 13, 2021 at 4:30 AM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
Mani,
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"@"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(@qb1 > qb2), e.g. merge(@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
On Fri, 13 Aug 2021 at 00:22, manikandan <pvmanikandan@xxxxxxxxx> wrote:
Hi Jonathan,
I totally agree to the point that the estimates for the collections are
driving the plan change and spoke about this to the developers. As per
them, they are using this cardinality hint in thousand's of queries. Back
to this query, they are working on how to rewrite the code.
We tried to create a sql patch with "OPT_PARAM("_optimizer_use_feedback"
"FALSE") OPT_PARAM("_optimizer_ansi_join_lateral_enhance" "FALSE")" to the
sql id , but that also seems not working. We did a 10053 trace (attached
for your reference ) on this sql with baseline and sql patch in place , but
could not find a good reason why its not picking baseline, may be I might
have overlooked.
