Re: Plan Baseline

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"_at_"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_at_gmail.com> 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"_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
>
>
> On Fri, 13 Aug 2021 at 00:22, manikandan <pvmanikandan_at_gmail.com> 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.
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l