Re: Sudden plan change related to "VIEW PUSHED PREDICATE" with cost difference of 1M VS 13G
From: Pap <oracle.developer35_at_gmail.com>
Date: Fri, 13 May 2022 10:37:09 +0530
Message-ID: <CAEjw_fhFPg_-u9StE_7GiCdUve=HBBqc2We6xO1OA9mcmeRquQ_at_mail.gmail.com>
Date: Fri, 13 May 2022 10:37:09 +0530
Message-ID: <CAEjw_fhFPg_-u9StE_7GiCdUve=HBBqc2We6xO1OA9mcmeRquQ_at_mail.gmail.com>
Somehow i am trying to force drive the plan towards the end by FULL + HASH JOIN but its not taking not sure why. I tried the hint below after the "Merge" key word in the query but still seeing a purely indexed access + nested loop path. However this path does not have any HASH JOIN or FULL SCAN towards the end , so I'm wondering if we should try this path as a profile and it would perform better?
with below hints
/*+USE_HASH(_at_"SEL$B29E968D" "VW_SQ_1"_at_"SEL$E9784550")
USE_HASH(_at_"SEL$1" "from$_subquery$_014"_at_"SEL$1")
FULL(_at_"SEL$291F8F59" "LASTHIST"_at_"SEL$3")
FULL(_at_"SEL$B29E968D" "HIST"_at_"SEL$2")
*/
| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost (%CPU)| Time |
| 0 | MERGE STATEMENT | | 20842
| 3073K| | 1064K (1)| 00:00:42 |
| 1 | MERGE |
HIST | | | | | |
| 2
| VIEW | | |
| | | |
| 3 | NESTED LOOPS OUTER | | 20842
| 4091K| | 1064K (1)| 00:00:42 |
| 4 | VIEW | | 20842
| 549K| | 250K (1)| 00:00:10 |
| 5 | SORT UNIQUE | | 20842
| 956K| 1240K| 250K (1)| 00:00:10 |
| 6
| CONCATENATION | | |
| | | |
|* 7 | HASH JOIN | | 20773
| 953K| | 125K (1)| 00:00:05 |
|* 8 | HASH JOIN | | 5 | 185
| | 183 (1)| 00:00:01 |
|* 9 | HASH JOIN | | 884 | 21216
| | 128 (0)| 00:00:01 |
| 10 | TABLE ACCESS STORAGE FULL| VTC | 884 | 9724
| | 74 (0)| 00:00:01 |
| 11 | TABLE ACCESS STORAGE FULL| VT | 1167 |
15171 | | 54 (0)| 00:00:01 |
| 12 | TABLE ACCESS STORAGE FULL | VTI | 6852 |
89076 | | 54 (0)| 00:00:01 | |* 13 | TABLE ACCESS STORAGE FULL | FS | 4908K| 46M| | 125K (1)| 00:00:05 | |* 14 | HASH JOIN | | 71 | 3337
| | 124K (1)| 00:00:05 |
|* 15 | HASH JOIN | | 5 | 185
| | 183 (1)| 00:00:01 |
|* 16 | HASH JOIN | | 884 | 21216 | | 128 (0)| 00:00:01 |
| 17 | TABLE ACCESS STORAGE FULL| VTC | 884 | 9724
| | 74 (0)| 00:00:01 |
| 18 | TABLE ACCESS STORAGE FULL| VT | 1167 |
15171 | | 54 (0)| 00:00:01 |
| 19 | TABLE ACCESS STORAGE FULL | VTI | 6852 |
89076 | | 54 (0)| 00:00:01 | |* 20 | TABLE ACCESS STORAGE FULL | FS | 16302
| 159K| | 124K (1)| 00:00:05 |
| 21 | VIEW | | 1 | 174
| | 39 (0)| 00:00:01 |
|* 22
| FILTER | | |
| | | |
|* 23 | TABLE ACCESS BY INDEX ROWID | HIST | 1 | 55
| | 5 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | HIST_IX1 | 1
| | | 4 (0)| 00:00:01 |
| 25 | SORT AGGREGATE | | 1 | 35
| | | |
|* 26 | TABLE ACCESS BY INDEX ROWID | HIST | 1
| 35 | | 34 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | HIST_IX1 | 51
| | | 27 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
On Fri, May 13, 2022 at 1:02 AM Andy Sayer <andysayer_at_gmail.com> wrote:
> _at_Andy , Regarding the "nested loop outer" while MERGE INTO i.e at the top >> of the plan, If i see the before patch plan which was running fast, in that >> this was NESTED LOOP OUTER only. But yes as you said , i will try to force >> if at all i will be able to make that 'HASH JOIN OUTER' any way. > > You need to consider the whole thing. Consider a nested loop index lookup > that is working its way through thousands of rows - it could be faster with > a hash join full table scan. If you just swap the index lookup to the > tablescan then you've made it many times worse. If you swap the nested > loops for hash join at the same time you've made it many times better. > > The live monitor you shared gave me enough information to know that all > hash joins for this part would be a decent idea. But It has only switched > the plan halfway, making everything suck. > > You can also use explain plan with my suggested (estimated) hints or the > rewrite I've shared. Although, there is no substitute for running it > against a representative data set on your development environment (but you > should get the explain plan first to make sure it's going to do what > it looks like we're trying to get it to do). To be clear - my hints are to > make it complete the plan change from nested loop indexes to hash joins, my > rewrite is to help you get your old plan back with nested loop indexes (and > I must note that the indexes being used could do with some improvement if > you really want to go down this route). > > I don't think you're going to have any luck pursuing a stats problem here > - if it was down to statistics, a hint would work (assuming you are using > it correctly). > > Thanks, > Andrew > > On Thu, 12 May 2022 at 19:07, Pap <oracle.developer35_at_gmail.com> wrote: > >> I checked dba_tab_col_statistics for HIST table but don't see any column >> group existing so wondering from where it's getting that. Can it be picking >> that from the index stats? If that's the case it's hard to alter that. >> >> On Thu, 12 May 2022, 9:23 pm Noveljic Nenad, <nenad.noveljic_at_vontobel.com> >> wrote: >> >>> This one – HIST, LASTHIST on HIST: >>> >>> >>> >>> ColGroup cardinality sanity check: ndv for HIST[HIST] = >>> 45569134.000000 HIST[LASTHIST] = 45569134.000000 >>> >>> Join selectivity using 1 ColGroups: 2.1945e-08 (sel1 = 0.000000, sel2 = >>> 0.000000) >>> >>> >>> >>> >>> >>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On >>> Behalf Of *Noveljic Nenad >>> *Sent:* Donnerstag, 12. Mai 2022 17:51 >>> *To:* Pap <oracle.developer35_at_gmail.com>; Andy Sayer < >>> andysayer_at_gmail.com>; Mohamed Houri <mohamed.houri_at_gmail.com> >>> *Cc:* Oracle L <oracle-l_at_freelists.org> >>> *Subject:* RE: Sudden plan change related to "VIEW PUSHED PREDICATE" >>> with cost difference of 1M VS 13G >>> >>> >>> >>> Can you try to delete (on the test system) the column group statistics >>> that produced a suspiciously low selectivity? >>> >>> >>> >>> JPPD: Retrieved original view card: 45569134.000000 >>> >>> Join Card: 0.000000 = outer (0.000000) * inner (45569134.000000) * sel >>> (2.1945e-08) >>> >>> Join Card - Rounded: 1 Computed: 0.000000 >>> >>> Best:: JoinMethod: NestedLoop >>> >>> Cost: 39.073380 Degree: 1 Resp: 39.073380 Card: 0.000000 >>> Bytes: >>> >>> *********************** >>> >>> Best so far: Table#: 0 cost: 5.002757 card: 0.000000 bytes: 55.000000 >>> >>> Table#: 1 cost: 39.073380 card: 0.000000 bytes: >>> 120.000000 >>> >>> *********************** >>> >>> (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000 >>> >>> JPPD: Updated best state, Cost = 39.073380 >>> >>> JPPD: Will not use JPPD from query block SEL$B29E968D (#5) >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> *From:* Pap <oracle.developer35_at_gmail.com> >>> *Sent:* Donnerstag, 12. Mai 2022 17:41 >>> *To:* Noveljic Nenad <nenad.noveljic_at_vontobel.com>; Andy Sayer < >>> andysayer_at_gmail.com>; Mohamed Houri <mohamed.houri_at_gmail.com> >>> *Cc:* Oracle L <oracle-l_at_freelists.org> >>> *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. *** >>> >>> I have captured the snippets from the 10053 trace with JPPD keyword >>> section as pasted in below link. But unable to see any straight forward >>> reason like fix_control or parameter as the cause. >>> >>> https://gist.github.com/oracle9999/95a65f6725e2bb65949d4909652dc59a >>> >>> >>> >>> On Thu, 12 May 2022, 5:00 pm Noveljic Nenad, < >>> nenad.noveljic_at_vontobel.com> wrote: >>> >>> Explain plan won’t execute the query. >>> >>> >>> >>> Look for the lines in the trace containing JPPD. >>> >>> >>> >>> *From:* Pap <oracle.developer35_at_gmail.com> >>> *Sent:* Donnerstag, 12. Mai 2022 13:22 >>> *To:* Mohamed Houri <mohamed.houri_at_gmail.com>; Andy Sayer < >>> andysayer_at_gmail.com>; Noveljic Nenad <nenad.noveljic_at_vontobel.com> >>> *Cc:* Oracle L <oracle-l_at_freelists.org> >>> *Subject:* Re: Sudden plan change related to "VIEW PUSHED PREDICATE" >>> with cost difference of 1M VS 13G >>> >>> >>> >>> As we dont really want to run the MERGE/DML query on production. And >>> also the MERGE query is not able to finish with the new path , So is it >>> okay to just do like below explain plan to get the 10053 trace? Hoping that >>> the MERGE query will not be executed behind the scene but just the plan >>> will be generated. Please correct me if wrong here. >>> >>> alter session set tracefile_identifier='mergequeryplan'; >>> >>> alter session set events='10053 trace name context forever, level 1'; >>> >>> Explain plan for MERGE INTO HIST.....; >>> >>> alter session set events='10053 trace name context off'; >>> >>> >>> >>> _at_Mohamed, Regarding the nested loop and hash join which is happening >>> towards the end of the plan, yes both of the sql monitor which i posted for >>> "post patch" are running slow. >>> >>> _at_Andy , Regarding the "nested loop outer" while MERGE INTO i.e at the >>> top of the plan, If i see the before patch plan which was running fast, in >>> that this was NESTED LOOP OUTER only. But yes as you said , i will try to >>> force if at all i will be able to make that 'HASH JOIN OUTER' any way. >>> >>> >>> >>> ____________________________________________________ >>> >>> 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. >>> >>> >>> 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. >>> >>> >>> 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 Fri May 13 2022 - 07:07:09 CEST