Hello!
My other parallel discussion is not the most popular discussion ever. (Degree of Parallelism of 2 is derived from scan of object )
Let me raise another question.
Q1) Why does this plan switch from parallel to serial at plan line 186? (DB version: 12.1.0.2)
Much of the plan is serial. See TQ, IN-OUT columns.
And more generally what are the reasons for a plan to switch from parallel to serial?
Q2) Here is my list, please add missing possible causes
- CBO calculates lower cost for the serial plan
- Oracle limitation. E.g.: version does not support parallel windowing analytical function
- rownum is used in the select
- PL/SQL is not declared as PARALLEL ENABLE
- DEGREE of accessed object is set explicitly to 1
Q3) How do you debug such problem? Do you do CBO trace (10053) or do you do _px_trace (Tracing Parallel Execution with _px_trace (Doc ID 444164.1))? I would like to understand the root cause, the reason why oracle makes the decision of switching to serial around line plan 186.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 2798395329
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 2312K| 913M| | | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ20008 | | | | | Q2,08 | P->S | QC (RAND) |
| 3 | MULTI-TABLE INSERT | | | | | | Q2,08 | PCWP | |
| 4 | PX RECEIVE | | | | | | Q2,08 | PCWP | |
| 5 | PX SEND ROUND-ROBIN | :TQ20007 | | | | | Q2,07 | P->P | RND-ROBIN |
|* 6 | HASH JOIN OUTER BUFFERED | | 2312K| 913M| | | Q2,07 | PCWP | |
| 7 | PART JOIN FILTER CREATE | :BF0000 | 2312K| 593M| | | Q2,07 | PCWP | |
| 8 | PX RECEIVE | | 2312K| 593M| | | Q2,07 | PCWP | |
| 9 | PX SEND HASH | :TQ20005 | 2312K| 593M| | | Q2,05 | P->P | HASH |
| 10 | VIEW | XXXXXXXX_NOFV_NOHINT | 2312K| 593M| | | Q2,05 | PCWP | |
| 11 | SORT UNIQUE | | 2312K| 5725M| | | Q2,05 | PCWP | |
| 12 | PX RECEIVE | | | | | | Q2,05 | PCWP | |
| 13 | PX SEND HASH | :TQ20004 | | | | | Q2,04 | P->P | HASH |
| 14 | BUFFER SORT | | 164 | 56088 | | | Q2,04 | PCWP | |
| 15 | UNION-ALL | | | | | | Q2,04 | PCWP | |
|* 16 | HASH JOIN RIGHT OUTER | | 2312K| 2862M| | | Q2,04 | PCWP | |
| 17 | PX RECEIVE | | 31704 | 557K| | | Q2,04 | PCWP | |
| 18 | PX SEND HASH | :TQ20001 | 31704 | 557K| | | Q2,01 | P->P | HASH |
| 19 | PX BLOCK ITERATOR | | 31704 | 557K| | | Q2,01 | PCWC | |
| 20 | TABLE ACCESS FULL | XX_SSSSSSSSSS_CURR | 31704 | 557K| | | Q2,01 | PCWP | |
| 21 | BUFFER SORT | | | | | | Q2,04 | PCWC | |
| 22 | PX RECEIVE | | 2309K| 2819M| | | Q2,04 | PCWP | |
| 23 | PX SEND HASH | :TQ20000 | 2309K| 2819M| | | | S->P | HASH |
|* 24 | FILTER | | | | | | | | |
| 25 | MERGE JOIN OUTER | | 2309K| 2819M| | | | | |
| 26 | SORT JOIN | | 2309K| 2376M| | | | | |
|* 27 | FILTER | | | | | | | | |
| 28 | MERGE JOIN OUTER | | 2309K| 2376M| | | | | |
| 29 | SORT JOIN | | 2309K| 1934M| | | | | |
|* 30 | FILTER | | | | | | | | |
|* 31 | HASH JOIN RIGHT OUTER | | 2309K| 1934M| | | | | |
|* 32 | TABLE ACCESS FULL | SSW_XXXXXXXX_INT_YYYYY | 2 | 454 | | | | | |
|* 33 | FILTER | | | | | | | | |
|* 34 | HASH JOIN RIGHT OUTER | | 2309K| 1434M| | | | | |
|* 35 | TABLE ACCESS FULL | SSW_XXXXXXXX_INT_YYYYY | 2 | 712 | | | | | |
| 36 | VIEW | | 2309K| 649M| | | | | |
| 37 | HASH GROUP BY | | 2309K| 1182M| | | | | |
| 38 | HASH GROUP BY | | 2309K| 1182M| | | | | |
|* 39 | FILTER | | | | | | | | |
|* 40 | HASH JOIN OUTER | | 2309K| 1182M| | | | | |
|* 41 | FILTER | | | | | | | | |
|* 42 | HASH JOIN RIGHT OUTER | | 2309K| 1094M| | | | | |
| 43 | VIEW | | 117K| 1717K| | | | | |
| 44 | HASH UNIQUE | | 117K| 3320K| | | | | |
| 45 | HASH UNIQUE | | 117K| 3320K| | | | | |
|* 46 | HASH JOIN | | 117K| 3320K| | | | | |
| 47 | PARTITION LIST ALL | | 117K| 2060K| 1 | 8 | | | |
|* 48 | TABLE ACCESS FULL | DW_MMMMMMMM_MMMMMMMM_REL_CURR | 117K| 2060K| 1 | 8 | | | |
| 49 | PARTITION LIST ALL | | 38M| 401M| 1 | 3 | | | |
| 50 | PARTITION LIST ALL | | 38M| 401M| 1 | 8 | | | |
| 51 | TABLE ACCESS FULL | LL_KKKKKKKK_EEEE | 38M| 401M| 1 | 24 | | | |
|* 52 | HASH JOIN RIGHT OUTER | | 2309K| 1061M| | | | | |
| 53 | VIEW | | 2 | 126 | | | | | |
| 54 | UNION-ALL | | | | | | | | |
|* 55 | HASH JOIN | | 1 | 51 | | | | | |
| 56 | EATE PART JOIN FILTER CR | :BF0001 | 1 | 23 | | | | | |
|* 57 | TABLE ACCESS FULL | SSW_MMMMMMMM_GUBERRAL_INTEGET_ | 1 | 23 | | | | | |
| 58 | PARTITION LIST ALL | | 7470K| 199M| 1 | 3 | | | |
| 59 | GLE PARTITION LIST SIN | | 7470K| 199M|KEY(AP)|KEY(AP)| | | |
| 60 | TABLE ACCESS FULL | LL_KKKKKKKK_EEEE | 7470K| 199M| KEY | KEY | | | |
|* 61 | HASH JOIN | | 1 | 75 | | | | | |
|* 62 | HASH JOIN | | 1 | 69 | | | | | |
|* 63 | HASH JOIN | | 1 | 51 | | | | | |
| 64 | CREATE PART JOIN FILTER | :BF0002 | 1 | 23 | | | | | |
|* 65 | L TABLE ACCESS FUL | SSW_MMMMMMMM_GUBERRAL_INTEGET_ | 1 | 23 | | | | | |
| 66 | L PARTITION LIST AL | | 38M| 1022M| 1 | 3 | | | |
| 67 | OIN-FILTER PARTITION LIST J | | 38M| 1022M|:BF0002|:BF0002| | | |
|* 68 | LL TABLE ACCESS FU | LL_KKKKKKKK_EEEE | 38M| 1022M| 1 | 24 | | | |
| 69 | PARTITION LIST ALL | | 320K| 5640K| 1 | 8 | | | |
|* 70 | TABLE ACCESS FULL | DW_MMMMMMMM_MMMMMMMM_REL_CURR | 320K| 5640K| 1 | 8 | | | |
| 71 | PARTITION LIST ALL | | 38M| 219M| 1 | 3 | | | |
| 72 | PARTITION LIST ALL | | 38M| 219M| 1 | 8 | | | |
| 73 | TABLE ACCESS FULL | LL_KKKKKKKK_EEEE | 38M| 219M| 1 | 24 | | | |
|* 74 | HASH JOIN RIGHT OUTER | | 2309K| 1019M| | | | | |
| 75 | VIEW | | 1 | 22 | | | | | |
| 76 | HASH GROUP BY | | 1 | 23 | | | | | |
| 77 | HASH GROUP BY | | 1 | 23 | | | | | |
|* 78 | TABLE ACCESS FULL | SSW_MMMMMMMM_GUBERRAL_INTEGET_ | 1 | 23 | | | | | |
|* 79 | HASH JOIN RIGHT OUTER | | 2309K| 971M| | | | | |
| 80 | TABLE ACCESS FULL | PT_MMMM_FLOW_KKKKKKKKK | 16980 | 248K| | | | | |
|* 81 | R HASH JOIN RIGHT OUTE | | 2309K| 938M| | | | | |
| 82 | VIEW | DW_COCR_VVVV_NOFV | 11M| 339M| | | | | |
| 83 | HASH GROUP BY | | 11M| 4706M| | | | | |
| 84 | HASH GROUP BY | | 11M| 4706M| | | | | |
|* 85 | OUTER HASH JOIN RIGHT | | 20M| 8623M| | | | | |
| 86 | SINGLE PARTITION LIST | | 198K| 4264K| KEY | KEY | | | |
|* 87 | ULL TABLE ACCESS F | DW_MMMMMMMM_MMMMMMMM_REL_CURR | 198K| 4264K| 7 | 7 | | | |
|* 88 | OUTER HASH JOIN RIGHT | | 20M| 8186M| | | | | |
| 89 | ALL PARTITION LIST | | 66340 | 1036K| 1 | 8 | | | |
|* 90 | FULL TABLE ACCESS | DW_MMMMMMMM_MMMMMMMM_REL_CURR | 66340 | 1036K| 1 | 8 | | | |
|* 91 | T OUTER HASH JOIN RIGH | | 20M| 7875M| | | | | |
| 92 | VIEW | AM_CMDB_CONT_CCRE_PARENT_V | 1148K| 41M| | | | | |
| 93 | HASH UNIQUE | | 1148K| 71M| | | | | |
|* 94 | FILTER | | | | | | | | |
|* 95 | RIGHT OUTER HASH JOIN | | 1148K| 71M| | | | | |
|* 96 | VIEW | | 1 | 32 | | | | | |
|* 97 | ORT PUSHED RANK WINDOW S | | 1993K| 76M| | | | | |
|* 98 | SORT PUSHED RANK WINDOW | | 1993K| 76M| | | | | |
|* 99 | OIN RIGHT OUTER HASH J | | 1993K| 76M| | | | | |
| 100 | TION LIST ALL PARTI | | 1216K| 20M| 1 | 8 | | | |
|*101 | E ACCESS FULL TABL | DW_MMMMMMMM_MMMMMMMM_REL_CURR | 1216K| 20M| 1 | 8 | | | |
| 102 | TION LIST ALL PARTI | | 1971K| 41M| 1 | 8 | | | |
|*103 | E ACCESS FULL TABL | DW_MMMMMMMM_MMMMMMMM_REL_CURR | 1971K| 41M| 1 | 8 | | | |
|*104 | OUTER HASH JOIN | | 1148K| 36M| | | | | |
|*105 | CESS FULL TABLE AC | PT_CONT_CCRE_PARENT | 932K| 18M| | | | | |
| 106 | N LIST ALL PARTITIO | | 1971K| 22M| 1 | 8 | | | |
|*107 | CCESS FULL TABLE A | DW_MMMMMMMM_MMMMMMMM_REL_CURR | 1971K| 22M| 1 | 8 | | | |
|*108 | HT OUTER HASH JOIN RIG | | 20M| 7138M| | | | | |
| 109 | ST ALL PARTITION LI | | 9684 | 170K| 1 | 8 | | | |
|*110 | S FULL TABLE ACCES | DW_MMMMMMMM_MMMMMMMM_REL_CURR | 9684 | 170K| 1 | 8 | | | |
| 111 | VIEW | | 20M| 6789M| | | | | |
| 112 | UNION-ALL | | | | | | | | |
| 113 | LIST ALL PARTITION | | 785K| 13M| 1 | 8 | | | |
|*114 | ESS FULL TABLE ACC | DW_MMMMMMMM_MMMMMMMM_REL_CURR | 785K| 13M| 1 | 8 | | | |
| 115 | T WINDOW SOR | | 19M| 839M| | | | | |
| 116 | RT WINDOW SO | | 19M| 839M| | | | | |
| 117 | ORT WINDOW S | | 19M| 839M| | | | | |
|*118 | IN HASH JO | | 19M| 839M| | | | | |
| 119 | ACCESS FULL TABLE | DW_DDDDDDD_CURR | 137K| 1077K| | | | | |
|*120 | OIN HASH J | | 19M| 690M| | | | | |
| 121 | TION LIST ALL PARTI | | 19M| 484M| 1 | 8 | | | |
|*122 | E ACCESS FULL TABL | DW_MMMMMMMM_MMMMMMMM_REL_CURR | 19M| 484M| 1 | 8 | | | |
| 123 | TION LIST ALL PARTI | | 38M| 401M| 1 | 3 | | | |
| 124 | ITION LIST ALL PART | | 38M| 401M| 1 | 8 | | | |
| 125 | LE ACCESS FULL TAB | LL_KKKKKKKK_EEEE | 38M| 401M| 1 | 24 | | | |
|*126 | ER HASH JOIN RIGHT OUT | | 2309K| 867M| | | | | |
|*127 | TABLE ACCESS FULL | SSW_MMMMMMMM_GUBERRAL_INTEGET_ | 1 | 23 | | | | | |
|*128 | HASH JOIN OUTER | | 2309K| 817M| | | | | |
|*129 | UTER HASH JOIN RIGHT O | | 2105K| 457M| | | | | |
| 130 | L TABLE ACCESS FUL | AD_KEYVAR_DDDDDDD_CO | 266 | 1862 | | | | | |
|*131 | OUTER HASH JOIN RIGHT | | 2105K| 443M| | | | | |
| 132 | LL TABLE ACCESS FU | AD_KEYVAR_DDDDDDD | 973 | 9730 | | | | | |
|*133 | OUTER HASH JOIN RIGHT | | 2105K| 423M| | | | | |
| 134 | VIEW | AM_KEYVAR_DW_DDDDDDD_V | 501K| 85M| | | | | |
| 135 | UNION-ALL | | | | | | | | |
|*136 | GHT OUTER HASH JOIN RI | | 50453 | 10M| | | | | |
|*137 | VIEW | V1 | 20045 | 1272K| | | | | |
| 138 | BY SORT GROUP | | 20045 | 548K| | | | | |
| 139 | P BY SORT GROU | | 20045 | 548K| | | | | |
|*140 | CESS FULL TABLE AC | TTTT | 20045 | 548K| | | | | |
|*141 | UTER HASH JOIN O | | 15829 | 2303K| | | | | |
|*142 | OUTER HASH JOIN | | 4966 | 407K| | | | | |
|*143 | ESS FULL TABLE ACC | DW_DDDDDDD_CURR | 4966 | 94354 | | | | | |
|*144 | VIEW | V1 | 20045 | 1272K| | | | | |
| 145 | UP BY SORT GRO | | 20045 | 548K| | | | | |
| 146 | OUP BY SORT GR | | 20045 | 548K| | | | | |
|*147 | ACCESS FULL TABLE | TTTT | 20045 | 548K| | | | | |
|*148 | VIEW | V1 | 20045 | 1272K| | | | | |
| 149 | P BY SORT GROU | | 20045 | 548K| | | | | |
| 150 | UP BY SORT GRO | | 20045 | 548K| | | | | |
|*151 | CCESS FULL TABLE A | TTTT | 20045 | 548K| | | | | |
|*152 | TER HASH JOIN OU | | 111K| 9173K| | | | | |
|*153 | S FULL TABLE ACCES | DW_DDDDDDD_CURR | 35125 | 651K| | | | | |
|*154 | VIEW | V1 | 20045 | 1272K| | | | | |
| 155 | BY SORT GROUP | | 20045 | 548K| | | | | |
| 156 | P BY SORT GROU | | 20045 | 548K| | | | | |
|*157 | CESS FULL TABLE AC | TTTT | 20045 | 548K| | | | | |
|*158 | GHT OUTER HASH JOIN RI | | 338K| 65M| | | | | |
|*159 | VIEW | V1 | 20045 | 1272K| | | | | |
| 160 | BY SORT GROUP | | 20045 | 548K| | | | | |
| 161 | P BY SORT GROU | | 20045 | 548K| | | | | |
|*162 | CESS FULL TABLE AC | TTTT | 20045 | 548K| | | | | |
|*163 | IGHT OUTER HASH JOIN R | | 81034 | 10M| | | | | |
|*164 | VIEW | V1 | 20045 | 1272K| | | | | |
| 165 | P BY SORT GROU | | 20045 | 548K| | | | | |
| 166 | UP BY SORT GRO | | 20045 | 548K| | | | | |
|*167 | CCESS FULL TABLE A | TTTT | 20045 | 548K| | | | | |
|*168 | OUTER HASH JOIN | | 19376 | 1400K| | | | | |
|*169 | ESS FULL TABLE ACC | DW_DDDDDDD_CURR | 4633 | 41697 | | | | | |
|*170 | VIEW | V1 | 20045 | 1272K| | | | | |
| 171 | UP BY SORT GRO | | 20045 | 548K| | | | | |
| 172 | OUP BY SORT GR | | 20045 | 548K| | | | | |
|*173 | ACCESS FULL TABLE | TTTT | 20045 | 548K| | | | | |
|*174 | T OUTER