Re: Sudden plan change related to "VIEW PUSHED PREDICATE" with cost difference of 1M VS 13G
Date: Wed, 18 May 2022 14:08:13 +0530
Message-ID: <CAEjw_fhS7AEsMwSPhbb0rUf-PGj_hYNQaiOdTtm8hncnkyEJXA_at_mail.gmail.com>
Thank You Andy. Below is the outline of the plan which is generated post applying your suggested hints.
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(_at_"SEL$3" "LASTHIST"_at_"SEL$3" ("HIST"."FSID"
"HIST"."OT_DT" "HIST"."SCD" "HIST"."MCD"))
PQ_FILTER(_at_"SEL$2" SERIAL)
INDEX_RS_ASC(_at_"SEL$2" "HIST"_at_"SEL$2" ("HIST"."FSID" "HIST"."OT_DT"
"HIST"."SCD" "HIST"."MCD"))
USE_HASH(_at_"SEL$4_2" "FS"_at_"SEL$4_2")
USE_HASH(_at_"SEL$4_2" "VTI"_at_"SEL$4_2")
USE_HASH(_at_"SEL$4_2" "VT"_at_"SEL$4_2")
USE_HASH(_at_"SEL$4_1" "FS"_at_"SEL$4")
USE_HASH(_at_"SEL$4_1" "VTI"_at_"SEL$4")
USE_HASH(_at_"SEL$4_1" "VT"_at_"SEL$4")
LEADING(_at_"SEL$4_2" "VTC"_at_"SEL$4_2" "VT"@"SEL$4_2" "VTI"@"SEL$4_2"
"FS"_at_"SEL$4_2")
LEADING(_at_"SEL$4_1" "VTC"_at_"SEL$4" "VT"@"SEL$4" "VTI"@"SEL$4" "FS"@"SEL$4") FULL(_at_"SEL$4_2" "FS"_at_"SEL$4_2") FULL(_at_"SEL$4_2" "VTI"_at_"SEL$4_2") FULL(_at_"SEL$4_2" "VT"_at_"SEL$4_2") FULL(_at_"SEL$4_2" "VTC"_at_"SEL$4_2") FULL(_at_"SEL$4_1" "FS"_at_"SEL$4") FULL(_at_"SEL$4_1" "VTI"_at_"SEL$4") FULL(_at_"SEL$4_1" "VT"_at_"SEL$4") FULL(_at_"SEL$4_1" "VTC"_at_"SEL$4") USE_HASH_AGGREGATION(_at_"SEL$5") USE_HASH(_at_"SEL$5_2" "FS"_at_"SEL$5_2") USE_HASH(_at_"SEL$5_2" "VTI"_at_"SEL$5_2") USE_HASH(_at_"SEL$5_2" "VT"_at_"SEL$5_2") USE_HASH(_at_"SEL$5_1" "FS"_at_"SEL$5")USE_HASH(_at_"SEL$5_1" "VTI"_at_"SEL$5") USE_HASH(_at_"SEL$5_1" "VT"_at_"SEL$5") LEADING(_at_"SEL$5_2" "VTC"_at_"SEL$5_2" "VT"@"SEL$5_2" "VTI"@"SEL$5_2" "FS"_at_"SEL$5_2")
LEADING(_at_"SEL$5_1" "VTC"_at_"SEL$5" "VT"@"SEL$5" "VTI"@"SEL$5" "FS"@"SEL$5") FULL(_at_"SEL$5_2" "FS"_at_"SEL$5_2") FULL(_at_"SEL$5_2" "VTI"_at_"SEL$5_2") FULL(_at_"SEL$5_2" "VT"_at_"SEL$5_2") FULL(_at_"SEL$5_2" "VTC"_at_"SEL$5_2") FULL(_at_"SEL$5_1" "FS"_at_"SEL$5") FULL(_at_"SEL$5_1" "VTI"_at_"SEL$5") FULL(_at_"SEL$5_1" "VT"_at_"SEL$5") FULL(_at_"SEL$5_1" "VTC"_at_"SEL$5")
USE_NL(_at_"SEL$1" "from$_subquery$_014"_at_"SEL$1")
LEADING(_at_"SEL$1" "TINFO"_at_"SEL$1" "from$_subquery$_014"@"SEL$1") NO_ACCESS(_at_"SEL$1" "from$_subquery$_014"_at_"SEL$1") NO_ACCESS(_at_"SEL$1" "TINFO"_at_"SEL$1")USE_MERGE_CARTESIAN(_at_"MRG$1" "HIST"_at_"MRG$1") USE_MERGE_CARTESIAN(_at_"MRG$1" "TINFO"_at_"MRG$1") LEADING(_at_"MRG$1" "from$_subquery$_015"_at_"MRG$1" "TINFO"@"MRG$1" "HIST"@"MRG$1")
FULL(_at_"MRG$1" "HIST"_at_"MRG$1") NO_ACCESS(_at_"MRG$1" "TINFO"_at_"MRG$1") NO_ACCESS(_at_"MRG$1" "from$_subquery$_015"_at_"MRG$1")OUTLINE(_at_"SEL$4")
OUTLINE(_at_"SEL$5")
OUTLINE_LEAF(_at_"SEL$4_2")
USE_CONCAT(_at_"SEL$4" 8 OR_PREDICATES(4)) OUTLINE_LEAF(_at_"SEL$4_1")
OUTLINE_LEAF(_at_"SEL$5_2")
USE_CONCAT(_at_"SEL$5" 8 OR_PREDICATES(4))
OUTLINE_LEAF(_at_"SEL$5_1") OUTLINE_LEAF(_at_"MRG$1") OUTLINE_LEAF(_at_"SEL$5") OUTLINE_LEAF(_at_"SEL$1") OUTLINE_LEAF(_at_"SEL$2") OUTLINE_LEAF(_at_"SEL$3") OUTLINE_LEAF(_at_"SEL$4") ALL_ROWS
OPT_PARAM('_optimizer_gather_feedback' 'false') OPT_PARAM('_optimizer_use_feedback' 'false') DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
On Tue, 17 May 2022, 3:54 am Andy Sayer, <andysayer_at_gmail.com> wrote:
> Can you grab the outline of that plan with my guessed hints? We can use
> them to fix up the hints. When you upgrade to 19c, you'll get notes about
> why it's not using the hints. It's no real surprise that the select query
> doesn't have the same issue - it takes it from a very unusual statement
> (which will suffer from obscure issues) to a fairly normal one (which other
> people would hit quickly so bugs can be fixed).
>
> It's funny how my hints have given the opposite plan to what I really
> intended but that is the nature of hints! I can see an opportunity for a
> big win where it's using the HIST_IX1 index on line 24, but if it's fast
> enough then not worth the effort.
>
> Thanks,
> Andy
>
> On Mon, 16 May 2022 at 12:53, Pap <oracle.developer35_at_gmail.com> wrote:
>
>> Dont have DML rights on prod so explain plan is not working for me for
>> this MERGE but i fetched it from admin guys. Below is the plan with OFE
>> ('12.1.0.2') and removing ordered hint. And i am seeing same plan even by
>> just removing ordered hint.
>>
>> Also something interesting, when i ran it as a SELECT statement, its
>> going for thr JPPD , so it seems , its the MERGE query which is facing some
>> blocking transformation. And i am seeing hint added as /*+ NO_MERGE */ in
>> the unparsed query in the 10053 trace, not sure if its expected or point
>> towards any oddity?
>>
>> And another interesting thing , when i had tried by pushing the hint
>> which Andy suggested i.e to make the plan as HASH JOIN + FULL SCAN , but
>> the resulted plan was totally different though. It was nested loop +
>> indexed access. But the sql is getting finished with that path. So we can
>> definitely push this as a profile. And this plan is having cost ~1M i.e.
>> lot less than the path its currently opting for by default. Still unknown
>> about the exact root cause/bug fix which is causing though. But we have
>> atleast got a workaround. Thank you so much.
>>
>> Posted the above plans in below link.
>>
>> https://gist.github.com/oracle9999/b3ff18f62f2ba6d0c0ffe448895e1046
>>
>>
>>
>>
>> On Fri, 13 May 2022, 4:35 pm Noveljic Nenad, <nenad.noveljic_at_vontobel.com>
>> wrote:
>>
>>> Have you considered following action plan?
>>>
>>> “
>>>
>>> - Generate the plan without optimizer_features_enabled (according to
>>> the outline it is set to 11.2.0.4)
>>> - Remove the ORDERED hint and check if it has any impact
>>> - Duplicate the database and upgrade it to 19c just to see if the
>>> problem reproduces in a newer release
>>>
>>> “
>>>
>>> The first two points are trivial and don’t require much effort.
>>>
>>>
>>>
>>> *From:* Pap <oracle.developer35_at_gmail.com>
>>> *Sent:* Freitag, 13. Mai 2022 07:21
>>> *To:* Noveljic Nenad <nenad.noveljic_at_vontobel.com>
>>> *Cc:* Oracle L <oracle-l_at_freelists.org>; Andy Sayer <andysayer_at_gmail.com>;
>>> Mohamed Houri <mohamed.houri_at_gmail.com>
>>> *Subject:* Re: Sudden plan change related to "VIEW PUSHED PREDICATE"
>>> with cost difference of 1M VS 13G
>>>
>>>
>>>
>>> **** E-Mail from outside Vontobel:* Do not click on links or open
>>> attachments unless you know the content is safe. ***
>>>
>>> * _at_Nenad *
>>>
>>> *"Are there any lines between the two lines mentioned above (“updated
>>> best state” and “Will not use JPPD” that might give the reason for not
>>> using JPPD? "*
>>>
>>> There are no lines between these two in the trace file.
>>>
>>>
>>>
>>> On Fri, May 13, 2022 at 1:45 AM Noveljic Nenad <
>>> nenad.noveljic_at_vontobel.com> wrote:
>>>
>>> Hello Pap
>>>
>>>
>>>
>>> I revisited my suggestion about column statistics - it was wrong. The
>>> low selectivity led to the JPPD transformation cost similar to the one that
>>> was in the plan with JPPD.
>>>
>>> JPPD: Updated best state, Cost = 39.073380
>>>
>>>
>>>
>>> If we multiply the cost above with the number of rows of the outer query
>>> block and add the cost of the outer join query block, we get the total cost
>>> of the plan with JPPD. It’s much lower than the plan without JPPD and
>>> similar to the cost of the good plan you’ve had previously:
>>>
>>> 20710 * 39.073380 + ~260K ~ 1M
>>>
>>>
>>>
>>> So JPPD should definitely be selected.
>>>
>>>
>>>
>>> The optimizer figured out that correctly and updated the best state with
>>> the cost of JPPD (39.07 is the cost of a single iteration):
>>>
>>>
>>>
>>> JPPD: Updated best state, Cost = 39.073380
>>>
>>>
>>>
>>> But then, it discarded it soon, for still unknown reason:
>>>
>>>
>>>
>>> JPPD: Will not use JPPD from query block SEL$B29E968D
>>>
>>>
>>>
>>> You mentioned that you edited out some lines from the optimizer trace.
>>> Are there any lines between the two lines mentioned above (“updated best
>>> state” and “Will not use JPPD” that might give the reason for not using
>>> JPPD? As already mentioned, the cost isn’t the reason.
>>>
>>>
>>>
>>> If not, the following might be the reason:
>>>
>>> A Optimizer applied some heuristics without logging it or
>>>
>>> B The correct decision got overridden due to a bug
>>>
>>>
>>>
>>> Could you try following:
>>>
>>> - Generate the plan without optimizer_features_enabled (according to
>>> the outline it is set to 11.2.0.4)
>>> - Remove the ORDERED hint and check if it has any impact
>>> - Duplicate the database and upgrade it to 19c just to see if the
>>> problem reproduces in a newer release
>>>
>>>
>>>
>>> Best regards,
>>>
>>>
>>>
>>> Nenad
>>>
>>>
>>>
>>> ____________________________________________________
>>>
>>> Please consider the environment before printing this e-mail.
>>>
>>> Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
>>>
>>>
>>> Important Notice
>>>
>>> This message is intended only for the individual named. It may contain
>>> confidential or privileged information. If you are not the named addressee
>>> you should in particular not disseminate, distribute, modify or copy this
>>> e-mail. Please notify the sender immediately by e-mail, if you have
>>> received this message by mistake and delete it from your system.
>>> Without prejudice to any contractual agreements between you and us which
>>> shall prevail in any case, we take it as your authorization to correspond
>>> with you by e-mail if you send us messages by e-mail. However, we reserve
>>> the right not to execute orders and instructions transmitted by e-mail at
>>> any time and without further explanation.
>>> E-mail transmission may not be secure or error-free as information could
>>> be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
>>> processing of incoming e-mails cannot be guaranteed. All liability of
>>> Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively
>>> referred to as "Vontobel Group") for any damages resulting from e-mail use
>>> is excluded. You are advised that urgent and time sensitive messages should
>>> not be sent by e-mail and if verification is required please request a
>>> printed version.
>>> Please note that all e-mail communications to and from the Vontobel
>>> Group are subject to electronic storage and review by Vontobel Group.
>>> Unless stated to the contrary and without prejudice to any contractual
>>> agreements between you and Vontobel Group which shall prevail in any case,
>>> e-mail-communication is for informational purposes only and is not intended
>>> as an offer or solicitation for the purchase or sale of any financial
>>> instrument or as an official confirmation of any transaction.
>>> The legal basis for the processing of your personal data is the
>>> legitimate interest to develop a commercial relationship with you, as well
>>> as your consent to forward you commercial communications. You can exercise,
>>> at any time and under the terms established under current regulation, your
>>> rights. If you prefer not to receive any further communications, please
>>> contact your client relationship manager if you are a client of Vontobel
>>> Group or notify the sender. Please note for an exact reference to the
>>> affected group entity the corporate e-mail signature. For further
>>> information about data privacy at Vontobel Group please consult
>>> www.vontobel.com.
>>>
>>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed May 18 2022 - 10:38:13 CEST