1 2 Previous Next 26 Replies Latest reply on May 23, 2017 8:41 AM by AndrewSayer

    Same query running with different  plan, giving inferior execution time in 12c than in 10g

    seekeroftruth

      We recently migrated from 10g to 12c , while doing so most of the queries saw improvement but some queries took a performance hit. like going from 17 minutes to more than 2 hours. Stats are regularly updated too. Since we didn't have much time on our hand we used OPTIMIZER_FEATURES_ENABLE hint to run those using 10g optimizer.

       

      But I really like to know how to troubleshoot in such a case, whereas DB remains but queries perform differently. 

      I tried replicating the 10g Plan using hints but the exact order of table joining does not match.

       

      PLAN in 12c

       

      ---------------------------------------------------------------------------------------------------

      | Id  | Operation               | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

      ---------------------------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT        |                 |       |       |       |    19M(100)|          |

      |   1 |  HASH GROUP BY          |                 |  1331 |   170K|       |    19M (20)| 00:12:55 |

      |*  2 |   HASH JOIN             |                 |    57G|  6968G|       |    16M  (3)| 00:10:46 |

      |   3 |    VIEW                 | VW_GBF_15       |  1053 | 66339 |       |     5  (20)| 00:00:01 |

      |   4 |     HASH GROUP BY       |                 |  1053 | 15795 |       |     5  (20)| 00:00:01 |

      |*  5 |      FILTER             |                 |       |       |       |            |          |

      |   6 |       NESTED LOOPS      |                 |  1141 | 17115 |       |     4   (0)| 00:00:01 |

      |*  7 |        TABLE ACCESS FULL| CLASS           |  1141 | 12551 |       |     4   (0)| 00:00:01 |

      |*  8 |        INDEX UNIQUE SCAN| PK_FDT_CATEGORY |     1 |     4 |       |     0   (0)|          |

      |*  9 |    HASH JOIN            |                 |    61G|  3919G|   513M|    16M  (2)| 00:10:37 |

      |  10 |     TABLE ACCESS FULL   | ORDHEAD         |    24M|   233M|       |   136K  (1)| 00:00:06 |

      |* 11 |     HASH JOIN           |                 |  3821M|   206G|    10M|  3247K  (1)| 00:02:07 |

      |* 12 |      TABLE ACCESS FULL  | SHIPMENT        |   345K|  6750K|       | 78661   (2)| 00:00:04 |

      |* 13 |      HASH JOIN          |                 |   588M|    20G|  3952K|  1760K  (1)| 00:01:09 |

      |* 14 |       TABLE ACCESS FULL | DESC_LOOK       |   161K|  2052K|       |   310   (2)| 00:00:01 |

      |  15 |       TABLE ACCESS FULL | SHIPSKU         |   588M|    13G|       |   725K  (2)| 00:00:29 |

      ---------------------------------------------------------------------------------------------------

       

      PLAN in 10g

       

       

      Plan hash value: 985652515

       

      ---------------------------------------------------------------------------------------------------------------

      | Id  | Operation                           | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

      ---------------------------------------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT                    |                 |       |       |       |   111K(100)|          |

      |   1 |  HASH GROUP BY                      |                 |  1331 |   107K|       |   111K  (1)| 00:22:19 |

      |*  2 |   FILTER                            |                 |       |       |       |            |          |

      |*  3 |    HASH JOIN                        |                 |   301K|    23M|       |   111K  (1)| 00:22:19 |

      |   4 |     INDEX FULL SCAN                 | PK_FDT_CATEGORY |    61 |   244 |       |     1   (0)| 00:00:01 |

      |*  5 |     HASH JOIN                       |                 |   301K|    22M|       |   111K  (1)| 00:22:19 |

      |*  6 |      TABLE ACCESS FULL              | CLASS           |  1002 | 11022 |       |     4   (0)| 00:00:01 |

      |*  7 |      HASH JOIN                      |                 |   374K|    24M|  3952K|   111K  (1)| 00:22:19 |

      |*  8 |       TABLE ACCESS FULL             | DESC_LOOK       |   161K|  2052K|       |   309   (2)| 00:00:04 |

      |   9 |       TABLE ACCESS BY INDEX ROWID   | SHIPSKU         |    67 |  1675 |       |     5   (0)| 00:00:01 |

      |  10 |        NESTED LOOPS                 |                 |   374K|    19M|       |   109K  (1)| 00:21:58 |

      |  11 |         NESTED LOOPS                |                 |  5545 |   162K|       | 89723   (1)| 00:17:57 |

      |* 12 |          TABLE ACCESS FULL          | SHIPMENT        |  5570 |   108K|       | 78580   (2)| 00:15:43 |

      |  13 |          TABLE ACCESS BY INDEX ROWID| ORDHEAD         |     1 |    10 |       |     2   (0)| 00:00:01 |

      |* 14 |           INDEX UNIQUE SCAN         | PK_ORDHEAD      |     1 |       |       |     1   (0)| 00:00:01 |

      |* 15 |         INDEX RANGE SCAN            | PK_SHIPSKU      |    67 |       |       |     3   (0)| 00:00:01 |

      ---------------------------------------------------------------------------------------------------------------

       

       

      SELECT

              DL .DEPT AS DEPT,

              CL.CATEGORY,

              'A' AS STATUS,

              TO_DATE ('30-Apr-17') AS FIRST_DATE,

              SUM(CASE

                  WHEN OH.order_type='N/B' THEN SS.QTY_RECEIVED * SS.UNIT_COST

                  ELSE 0 END) AS NB_COST,

              SUM(CASE

                  WHEN OH.order_type='N/B' THEN SS.QTY_RECEIVED * SS.UNIT_RETAIL

                  ELSE 0 END) AS NB_RETAIL,  

              SUM(CASE

                  WHEN OH.order_type !='N/B' THEN SS.QTY_RECEIVED * SS.UNIT_COST

                  ELSE 0 END) AS BASIC_COST,

              SUM(CASE

                  WHEN OH.order_type !='N/B' THEN SS.QTY_RECEIVED * SS.UNIT_RETAIL

                  ELSE 0 END) AS BASIC_RETAIL    

          FROM   SHIPSKU SS,

                 SHIPMENT SH,

                 ORDHEAD OH,

                 DESC_LOOK DL,

                 CLASS CL,

                 CATEGORY FC

         WHERE   DL.DEPT < 80

                 AND DL.DEPT NOT IN

                          (25, 26, 18, 33, 40, 24)

                 --AND OH.ORDER_TYPE <> 'N/B'

                 AND SH.RECEIVE_DATE BETWEEN TO_DATE('30-Apr-17')

                                         AND  TO_DATE('27-May-17')

                 AND SH.ORDER_NO IS NOT NULL

                 AND OH.ORDER_NO = SH.ORDER_NO ---and oh.ORDER_NO in (5900319)

                 AND SH.SHIPMENT = SS.SHIPMENT

                 AND DL.SKU = SS.SKU

                 AND DL.DEPT = CL.DEPT

                 AND DL.CLASS = CL.CLASS

                 AND CL.CATEGORY= FC.CATEGORY

      GROUP BY DL .DEPT,CL.CATEGORY;

       

       

        • 4. Re: Same query running with different  plan, giving inferior execution time in 12c than in 10g
          John Thorton

          seekeroftruth wrote:

           

          Yes. Both environment is identical.

          if EVERYTHING were really identical, then results would be the same.

          By definition, when you observe different results you should be 100% certain that SOMETHING  is different!

          • 5. Re: Same query running with different  plan, giving inferior execution time in 12c than in 10g
            AndrewSayer

            Hard to compare the plans without the predicates section, please include them both.

             

            I'll note that 10g thinks you'll get 5570 rows from shipment with some filters.

            In 12c it thinks you'll get 345K rows from a tablescan with some fitlers.

             

            Neither look to have any filters other than those that you've hardcoded into your query. Perhaps your stats are different?

            • 6. Re: Same query running with different  plan, giving inferior execution time in 12c than in 10g
              Jonathan Lewis

              John Thorton wrote:

               

              seekeroftruth wrote:

               

              Yes. Both environment is identical.

              if EVERYTHING were really identical, then results would be the same.

              By definition, when you observe different results you should be 100% certain that SOMETHING is different!

               

              The OP probably assumed you had already noticed that he's comparing 10g with 12c - or maybe he was too polite to point out that you hadn't noticed that particular difference.

              Strange though it may seem to you, when Oracle releases a new version the optimizer group would expect that identical sets would sometimes produce different execution plans under the new version.

              • 7. Re: Same query running with different  plan, giving inferior execution time in 12c than in 10g
                Jonathan Lewis

                Sometimes you just get unlucky and a new version produces a transformation that an older version didn't have, and you find it's not appropriate for some of your queries.

                 

                In your case you can see from the VW_GBF_15 at operation 3 that the optimizer has used a "group by placement" (which actually appeared as a possibiity in 11g) to aggregate early before joining.  Unfortunately it looks like there's something wrong with the resulting arithmetic, and given the resulting run-time it's clearly a bad idea.

                 

                It's possible that the addition of some extra stats (perhaps extended/column group stats) might give the optimizer better information about your data and allow it to find the more appropriate path.  If all else fails there is a NO_PLACE_GROUP_BY hint that could disable the feature, though I'm not sure how to use it for your query: possibly just /*+ no_place_group_by() */, possibly /*+ no_place_group_by(@sel$1 (cl@sel$1) (fc@sel$1)) */

                 

                It's probably best to raise an SR to ask how best to disable the feature for this query.

                 

                Regards

                Jonathan Lewis

                • 8. Re: Same query running with different  plan, giving inferior execution time in 12c than in 10g
                  Randolf Geist

                  seekeroftruth wrote:

                   

                  We recently migrated from 10g to 12c , while doing so most of the queries saw improvement but some queries took a performance hit. like going from 17 minutes to more than 2 hours. Stats are regularly updated too. Since we didn't have much time on our hand we used OPTIMIZER_FEATURES_ENABLE hint to run those using 10g optimizer.

                   

                  But I really like to know how to troubleshoot in such a case, whereas DB remains but queries perform differently.

                  I tried replicating the 10g Plan using hints but the exact order of table joining does not match.

                   

                  In addition to what others have already said - it would be interesting to see the estimated cost of the original 10g plan in 12c, because the 12c plan you've posted has such a high cost in comparison (19M vs. 111K) and therefore the original plan (if considered at all by the 12c optimizer) is supposed to have an even higher cost (there are a few exceptions to that rule, but generally applicable).

                   

                  You've written that you weren't able to reproduce the 10g plan in 12c using hints, but there should be a fairly straightforward way of reproducing the 10g plan in 12c - just pull the full outline hint set from the execution plan (using the FORMAT parameter "+OUTLINE" or "ADVANCED" to have the "Outline data" section added to the DBMS_XPLAN.DISPLAY* output, which should look something like this:

                   

                  Outline Data

                  -------------

                   

                    /*+

                        BEGIN_OUTLINE_DATA

                  .

                  .

                  .

                        ALL_ROWS

                        OPTIMIZER_FEATURES_ENABLE('10.2.0.x')

                        IGNORE_OPTIM_EMBEDDED_HINTS

                        END_OUTLINE_DATA

                    */

                   

                  As a first attempt you should copy the full hint set into your statement in 12c to see that it reproduces the original 10g plan, then try again but remove or comment the OPTIMIZER_FEATURES_ENABLE hint (I think the DB_VERSION hint was added in later releases, but the OPTIMIZER_FEATURES_ENABLE should already be there in the 10g outline) to see what plan / cost you get with the remaining hint set but without switching to the old optimizer version.

                   

                  Randolf

                  • 9. Re: Same query running with different  plan, giving inferior execution time in 12c than in 10g
                    seekeroftruth

                    Actually, 12c Plan is from Production and 10g Plan is from Test and Production stats are updated on a regular basis.  I will post with actual row counts versus expected row counts tomorrow. thanks for your time.

                    • 10. Re: Same query running with different  plan, giving inferior execution time in 12c than in 10g
                      seekeroftruth

                      Hi Randolf,

                       

                      Here is the information you requested.

                       

                      10g Plans.

                       

                       

                      SQL_ID  bgxju691pz63u, child number 0

                      -------------------------------------

                      Plan hash value: 985652515

                       

                      --------------------------------------------------------------------------------------------------------

                      | Id  | Operation                           | Name            | E-Rows | A-Rows |   A-Time   | Buffers |

                      --------------------------------------------------------------------------------------------------------

                      |   0 | SELECT STATEMENT                    |                 |        |     50 |00:00:13.57 |     926K|

                      |   1 |  HASH GROUP BY                      |                 |   1331 |     50 |00:00:13.57 |     926K|

                      |*  2 |   FILTER                            |                 |        |   1121K|00:00:12.42 |     926K|

                      |*  3 |    HASH JOIN                        |                 |    301K|   1121K|00:00:11.30 |     926K|

                      |   4 |     INDEX FULL SCAN                 | PK_FDT_CATEGORY |     61 |     61 |00:00:00.01 |       1 |

                      |*  5 |     HASH JOIN                       |                 |    301K|   1121K|00:00:11.30 |     926K|

                      |*  6 |      TABLE ACCESS FULL              | CLASS           |   1002 |   1053 |00:00:00.01 |      13 |

                      |*  7 |      HASH JOIN                      |                 |    374K|   1121K|00:00:10.17 |     926K|

                      |*  8 |       TABLE ACCESS FULL             | DESC_LOOK       |    161K|    219K|00:00:00.01 |    1578 |

                      |   9 |       TABLE ACCESS BY INDEX ROWID   | SHIPSKU         |     67 |   1210K|00:00:09.48 |     924K|

                      |  10 |        NESTED LOOPS                 |                 |    374K|   1286K|00:00:08.12 |     865K|

                      |  11 |         NESTED LOOPS                |                 |   5545 |  75950 |00:00:06.93 |     631K|

                      |* 12 |          TABLE ACCESS FULL          | SHIPMENT        |   5570 |  75950 |00:00:06.32 |     403K|

                      |  13 |          TABLE ACCESS BY INDEX ROWID| ORDHEAD         |      1 |  75950 |00:00:00.59 |     227K|

                      |* 14 |           INDEX UNIQUE SCAN         | PK_ORDHEAD      |      1 |  75950 |00:00:00.37 |     151K|

                      |* 15 |         INDEX RANGE SCAN            | PK_SHIPSKU      |     67 |   1210K|00:00:00.61 |     234K|

                      --------------------------------------------------------------------------------------------------------

                       

                      Predicate Information (identified by operation id):

                      ---------------------------------------------------

                       

                         2 - filter(TO_DATE('30-Apr-17')<=TO_DATE('27-May-17'))

                         3 - access("CL"."FDC_CATEGORY"="FC"."CATEGORY")

                         5 - access("DL"."DEPT"="CL"."DEPT" AND "DL"."CLASS"="CL"."CLASS")

                         6 - filter(("CL"."DEPT"<80 AND "CL"."FDC_CATEGORY">300 AND "CL"."DEPT"<>25 AND "CL"."DEPT"<>26 AND "CL"."DEPT"<>18 AND

                                    "CL"."DEPT"<>33 AND "CL"."DEPT"<>40 AND "CL"."DEPT"<>24))

                         7 - access("DL"."SKU"="SS"."SKU")

                         8 - filter(("DL"."DEPT"<80 AND "DL"."DEPT"<>25 AND "DL"."DEPT"<>26 AND "DL"."DEPT"<>18 AND "DL"."DEPT"<>33 AND "DL"."DEPT"<>40

                                    AND "DL"."DEPT"<>24))

                        12 - filter(("SH"."RECEIVE_DATE">=TO_DATE('30-Apr-17') AND "SH"."ORDER_NO" IS NOT NULL AND

                                    "SH"."RECEIVE_DATE"<=TO_DATE('27-May-17')))

                        14 - access("OH"."ORDER_NO"="SH"."ORDER_NO")

                        15 - access("SH"."SHIPMENT"="SS"."SHIPMENT")

                       

                       

                      10g With outline data.

                       

                      SQL_ID  bgxju691pz63u, child number 0

                      -------------------------------------

                      SELECT  /*+ gather_plan_statistics */         DL .DEPT AS DEPT,         CL.FDC_CATEGORY,         'A'

                      AS STATUS,         TO_DATE ('30-Apr-17') AS FIRST_DATE,         SUM(CASE              WHEN

                      OH.order_type='N/B' THEN SS.QTY_RECEIVED * SS.UNIT_COST             ELSE 0 END) AS NB_COST,        

                      SUM(CASE              WHEN OH.order_type='N/B' THEN SS.QTY_RECEIVED * SS.UNIT_RETAIL             ELSE

                      0 END) AS NB_RETAIL,            SUM(CASE              WHEN OH.order_type !='N/B' THEN SS.QTY_RECEIVED

                      * SS.UNIT_COST             ELSE 0 END) AS BASIC_COST,         SUM(CASE              WHEN OH.order_type

                      !='N/B' THEN SS.QTY_RECEIVED * SS.UNIT_RETAIL             ELSE 0 END) AS BASIC_RETAIL          FROM  

                      SHIPSKU SS,            SHIPMENT SH,            ORDHEAD OH,            DESC_LOOK DL,            CLASS

                      CL,            FDT_CATEGORY FC    WHERE   DL.DEPT < 80            AND DL.DEPT NOT IN                  

                      "  (25, 26, 18, 33, 40, 24)           

                                  AND SH.RECEIVE_DATE BETWEEN TO_DATE('30-Apr-"

                       

                      Plan hash value: 985652515

                       

                      ---------------------------------------------------------------------------------------------------------------

                      | Id  | Operation                           | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

                      ---------------------------------------------------------------------------------------------------------------

                      |   0 | SELECT STATEMENT                    |                 |       |       |       |   111K(100)|          |

                      |   1 |  HASH GROUP BY                      |                 |  1331 |   107K|       |   111K  (1)| 00:22:19 |

                      |*  2 |   FILTER                            |                 |       |       |       |            |          |

                      |*  3 |    HASH JOIN                        |                 |   301K|    23M|       |   111K  (1)| 00:22:19 |

                      |   4 |     INDEX FULL SCAN                 | PK_FDT_CATEGORY |    61 |   244 |       |     1   (0)| 00:00:01 |

                      |*  5 |     HASH JOIN                       |                 |   301K|    22M|       |   111K  (1)| 00:22:19 |

                      |*  6 |      TABLE ACCESS FULL              | CLASS           |  1002 | 11022 |       |     4   (0)| 00:00:01 |

                      |*  7 |      HASH JOIN                      |                 |   374K|    24M|  3952K|   111K  (1)| 00:22:19 |

                      |*  8 |       TABLE ACCESS FULL             | DESC_LOOK       |   161K|  2052K|       |   309   (2)| 00:00:04 |

                      |   9 |       TABLE ACCESS BY INDEX ROWID   | SHIPSKU         |    67 |  1675 |       |     5   (0)| 00:00:01 |

                      |  10 |        NESTED LOOPS                 |                 |   374K|    19M|       |   109K  (1)| 00:21:58 |

                      |  11 |         NESTED LOOPS                |                 |  5545 |   162K|       | 89723   (1)| 00:17:57 |

                      |* 12 |          TABLE ACCESS FULL          | SHIPMENT        |  5570 |   108K|       | 78580   (2)| 00:15:43 |

                      |  13 |          TABLE ACCESS BY INDEX ROWID| ORDHEAD         |     1 |    10 |       |     2   (0)| 00:00:01 |

                      |* 14 |           INDEX UNIQUE SCAN         | PK_ORDHEAD      |     1 |       |       |     1   (0)| 00:00:01 |

                      |* 15 |         INDEX RANGE SCAN            | PK_SHIPSKU      |    67 |       |       |     3   (0)| 00:00:01 |

                      ---------------------------------------------------------------------------------------------------------------

                       

                      Query Block Name / Object Alias (identified by operation id):

                      -------------------------------------------------------------

                       

                         1 - SEL$1

                         4 - SEL$1 / FC@SEL$1

                         6 - SEL$1 / CL@SEL$1

                         8 - SEL$1 / DL@SEL$1

                         9 - SEL$1 / SS@SEL$1

                        12 - SEL$1 / SH@SEL$1

                        13 - SEL$1 / OH@SEL$1

                        14 - SEL$1 / OH@SEL$1

                        15 - SEL$1 / SS@SEL$1

                       

                      Outline Data

                      -------------

                       

                        /*+

                            BEGIN_OUTLINE_DATA

                            IGNORE_OPTIM_EMBEDDED_HINTS

                            OPTIMIZER_FEATURES_ENABLE('10.2.0.5')

                            ALL_ROWS

                            OUTLINE_LEAF(@"SEL$1")

                            FULL(@"SEL$1" "SH"@"SEL$1")

                            INDEX_RS_ASC(@"SEL$1" "OH"@"SEL$1" ("ORDHEAD"."ORDER_NO"))

                            INDEX(@"SEL$1" "SS"@"SEL$1" ("SHIPSKU"."SHIPMENT" "SHIPSKU"."SKU" "SHIPSKU"."CARTON"

                                    "SHIPSKU"."INV_STATUS"))

                            FULL(@"SEL$1" "DL"@"SEL$1")

                            FULL(@"SEL$1" "CL"@"SEL$1")

                            INDEX(@"SEL$1" "FC"@"SEL$1" ("FDT_CATEGORY"."CATEGORY"))

                            LEADING(@"SEL$1" "SH"@"SEL$1" "OH"@"SEL$1" "SS"@"SEL$1" "DL"@"SEL$1" "CL"@"SEL$1" "FC"@"SEL$1")

                            USE_NL(@"SEL$1" "OH"@"SEL$1")

                            USE_NL(@"SEL$1" "SS"@"SEL$1")

                            USE_HASH(@"SEL$1" "DL"@"SEL$1")

                            USE_HASH(@"SEL$1" "CL"@"SEL$1")

                            USE_HASH(@"SEL$1" "FC"@"SEL$1")

                            SWAP_JOIN_INPUTS(@"SEL$1" "DL"@"SEL$1")

                            SWAP_JOIN_INPUTS(@"SEL$1" "CL"@"SEL$1")

                            SWAP_JOIN_INPUTS(@"SEL$1" "FC"@"SEL$1")

                            USE_HASH_AGGREGATION(@"SEL$1")

                            END_OUTLINE_DATA

                        */

                       

                      Predicate Information (identified by operation id):

                      ---------------------------------------------------

                       

                         2 - filter(TO_DATE('30-Apr-17')<=TO_DATE('27-May-17'))

                         3 - access("CL"."FDC_CATEGORY"="FC"."CATEGORY")

                         5 - access("DL"."DEPT"="CL"."DEPT" AND "DL"."CLASS"="CL"."CLASS")

                         6 - filter(("CL"."DEPT"<80 AND "CL"."FDC_CATEGORY">300 AND "CL"."DEPT"<>25 AND "CL"."DEPT"<>26 AND

                                    "CL"."DEPT"<>18 AND "CL"."DEPT"<>33 AND "CL"."DEPT"<>40 AND "CL"."DEPT"<>24))

                         7 - access("DL"."SKU"="SS"."SKU")

                         8 - filter(("DL"."DEPT"<80 AND "DL"."DEPT"<>25 AND "DL"."DEPT"<>26 AND "DL"."DEPT"<>18 AND

                                    "DL"."DEPT"<>33 AND "DL"."DEPT"<>40 AND "DL"."DEPT"<>24))

                        12 - filter(("SH"."RECEIVE_DATE">=TO_DATE('30-Apr-17') AND "SH"."ORDER_NO" IS NOT NULL AND

                                    "SH"."RECEIVE_DATE"<=TO_DATE('27-May-17')))

                        14 - access("OH"."ORDER_NO"="SH"."ORDER_NO")

                        15 - access("SH"."SHIPMENT"="SS"."SHIPMENT")

                       

                      Column Projection Information (identified by operation id):

                      -----------------------------------------------------------

                       

                         1 - "DL"."DEPT"[NUMBER,22], "CL"."FDC_CATEGORY"[NUMBER,22], SUM(CASE  WHEN "OH"."ORDER_TYPE"<>'N/B'

                             THEN "SS"."QTY_RECEIVED"*"SS"."UNIT_RETAIL" ELSE 0 END )[22], SUM(CASE  WHEN "OH"."ORDER_TYPE"<>'N/B'

                             THEN "SS"."QTY_RECEIVED"*"SS"."UNIT_COST" ELSE 0 END )[22], SUM(CASE "OH"."ORDER_TYPE" WHEN 'N/B' THEN

                             "SS"."QTY_RECEIVED"*"SS"."UNIT_RETAIL" ELSE 0 END )[22], SUM(CASE "OH"."ORDER_TYPE" WHEN 'N/B' THEN

                             "SS"."QTY_RECEIVED"*"SS"."UNIT_COST" ELSE 0 END )[22]

                         2 - "CL"."FDC_CATEGORY"[NUMBER,22], "DL"."DEPT"[NUMBER,22], "SS"."UNIT_RETAIL"[NUMBER,22],

                             "SS"."UNIT_COST"[NUMBER,22], "SS"."QTY_RECEIVED"[NUMBER,22], "OH"."ORDER_TYPE"[VARCHAR2,3]

                         3 - (#keys=1) "CL"."FDC_CATEGORY"[NUMBER,22], "DL"."DEPT"[NUMBER,22],

                             "SS"."UNIT_RETAIL"[NUMBER,22], "SS"."UNIT_COST"[NUMBER,22], "SS"."QTY_RECEIVED"[NUMBER,22],

                             "OH"."ORDER_TYPE"[VARCHAR2,3]

                         4 - "FC"."CATEGORY"[NUMBER,22]

                         5 - (#keys=2) "DL"."DEPT"[NUMBER,22], "CL"."FDC_CATEGORY"[NUMBER,22], "SS"."UNIT_COST"[NUMBER,22],

                             "SS"."QTY_RECEIVED"[NUMBER,22], "OH"."ORDER_TYPE"[VARCHAR2,3], "SS"."UNIT_RETAIL"[NUMBER,22]

                         6 - "CL"."DEPT"[NUMBER,22], "CL"."CLASS"[NUMBER,22], "CL"."FDC_CATEGORY"[NUMBER,22]

                         7 - (#keys=1) "DL"."DEPT"[NUMBER,22], "DL"."CLASS"[NUMBER,22], "OH"."ORDER_TYPE"[VARCHAR2,3],

                             "SS"."UNIT_RETAIL"[NUMBER,22], "SS"."QTY_RECEIVED"[NUMBER,22], "SS"."UNIT_COST"[NUMBER,22]

                         8 - "DL"."SKU"[NUMBER,22], "DL"."CLASS"[NUMBER,22], "DL"."DEPT"[NUMBER,22]

                         9 - "SS"."SKU"[NUMBER,22], "SS"."QTY_RECEIVED"[NUMBER,22], "SS"."UNIT_COST"[NUMBER,22],

                             "SS"."UNIT_RETAIL"[NUMBER,22]

                        10 - "OH"."ORDER_TYPE"[VARCHAR2,3], "SS".ROWID[ROWID,10], "SS"."SKU"[NUMBER,22]

                        11 - "SH"."SHIPMENT"[NUMBER,22], "OH"."ORDER_TYPE"[VARCHAR2,3]

                        12 - "SH"."SHIPMENT"[NUMBER,22], "SH"."ORDER_NO"[NUMBER,22]

                        13 - "OH"."ORDER_TYPE"[VARCHAR2,3]

                        14 - "OH".ROWID[ROWID,10]

                        15 - "SS".ROWID[ROWID,10], "SS"."SKU"[NUMBER,22]

                       

                       

                      12c Plan

                       

                      SQL_ID  bgxju691pz63u, child number 0

                      -------------------------------------

                       

                       

                      Plan hash value: 2242901846

                       

                      --------------------------------------------------------------------------------------------------------------

                      | Id  | Operation               | Name            | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |

                      --------------------------------------------------------------------------------------------------------------

                      |   0 | SELECT STATEMENT        |                 |        |     50 |00:14:33.41 |    4832K|   4888K|  58528 |

                      |   1 |  HASH GROUP BY          |                 |   1572 |     50 |00:14:33.41 |    4832K|   4888K|  58528 |

                      |*  2 |   HASH JOIN             |                 |     57G|   2036K|00:14:31.53 |    4832K|   4888K|  58528 |

                      |   3 |    VIEW                 | VW_GBF_15       |   1053 |   1053 |00:00:00.01 |      17 |      0 |      0 |

                      |   4 |     HASH GROUP BY       |                 |   1053 |   1053 |00:00:00.01 |      17 |      0 |      0 |

                      |*  5 |      FILTER             |                 |        |   1053 |00:00:00.01 |      17 |      0 |      0 |

                      |   6 |       NESTED LOOPS      |                 |   1141 |   1053 |00:00:00.01 |      17 |      0 |      0 |

                      |*  7 |        TABLE ACCESS FULL| CLASS           |   1141 |   1053 |00:00:00.01 |      13 |      0 |      0 |

                      |*  8 |        INDEX UNIQUE SCAN| PK_FDT_CATEGORY |      1 |   1053 |00:00:00.01 |       4 |      0 |      0 |

                      |*  9 |    HASH JOIN            |                 |     61G|   2036K|00:14:30.47 |    4832K|   4888K|  58528 |

                      |  10 |     TABLE ACCESS FULL   | ORDHEAD         |     24M|     24M|00:00:33.95 |     704K|    704K|      0 |

                      |* 11 |     HASH JOIN           |                 |   3821M|   2036K|00:13:33.78 |    4127K|   4125K|      0 |

                      |* 12 |      TABLE ACCESS FULL  | SHIPMENT        |    345K|  90978 |00:00:50.79 |     403K|    403K|      0 |

                      |* 13 |      HASH JOIN          |                 |    588M|    557M|00:09:22.31 |    3723K|   3721K|      0 |

                      |* 14 |       TABLE ACCESS FULL | DESC_LOOK       |    220K|    219K|00:00:00.06 |    1578 |      0 |      0 |

                      |  15 |       TABLE ACCESS FULL | SHIPSKU         |    588M|    588M|00:03:41.34 |    3721K|   3721K|      0 |

                      --------------------------------------------------------------------------------------------------------------

                       

                      Predicate Information (identified by operation id):

                      ---------------------------------------------------

                       

                         2 - access("DL"."DEPT"="ITEM_2" AND "DL"."CLASS"="ITEM_1")

                         5 - filter(TO_DATE('27-May-17')>=TO_DATE('30-Apr-17'))

                         7 - filter(("CL"."DEPT"<80 AND "CL"."DEPT"<>25 AND "CL"."DEPT"<>26 AND "CL"."DEPT"<>18 AND "CL"."DEPT"<>33 AND "CL"."DEPT"<>40 AND

                                    "CL"."DEPT"<>24 AND "CL"."FDC_CATEGORY">300))

                         8 - access("CL"."FDC_CATEGORY"="FC"."CATEGORY")

                         9 - access("OH"."ORDER_NO"="SH"."ORDER_NO")

                        11 - access("SH"."SHIPMENT"="SS"."SHIPMENT")

                        12 - filter(("SH"."RECEIVE_DATE">=TO_DATE('30-Apr-17') AND "SH"."ORDER_NO" IS NOT NULL AND "SH"."RECEIVE_DATE"<=TO_DATE('27-May-17')))

                        13 - access("DL"."SKU"="SS"."SKU")

                        14 - filter(("DL"."DEPT"<80 AND "DL"."DEPT"<>25 AND "DL"."DEPT"<>26 AND "DL"."DEPT"<>18 AND "DL"."DEPT"<>33 AND "DL"."DEPT"<>24 AND

                                    "DL"."DEPT"<>40))

                       

                      Note

                      -----

                         - dynamic statistics used: dynamic sampling (level=2)

                         - this is an adaptive plan

                         - 1 Sql Plan Directive used for this statement

                      • 11. Re: Same query running with different  plan, giving inferior execution time in 12c than in 10g
                        Mohamed Houri

                        I think that Jonathan Lewis has pointed out the main issue with your query which is the ‘’here’’ inappropriate group by placement transformation. You see this in the execution plan via the operation n° 3 VW_GBF_15 and via the apparition of the predicate n°2:

                         

                        Predicate Information (identified by operation id):

                        ---------------------------------------------------

                           2 - access("DL"."DEPT"="ITEM_2" AND "DL"."CLASS"="ITEM_1")

                         

                        Where ITEM_1 and ITEM_2 are the symptoms of Oracle having refactored the original query.

                         

                        It’s not very clear for me according to the estimations you’ve shown how Oracle thinks that aggregating the CLASS table before joining it with the rest of the tables is better than joining first and grouping later.

                         

                        This group by placement transformation is not new. It exists since 11g I think. And, while I am working on troubleshooting a couple of Oracle 12c applications, I still have not encountered a problematic case involving this group by placement. But have encountered several issues with a Decorrelated Lateral view instead.

                         

                        In addition to what Jonathan has suggested with the use of the no_place_group_by hint I would like to suggest you to stop using SQL Plan Directives. They are known to create a lot of issues.

                         

                        Best Regards

                        Mohamed Houri

                        • 12. Re: Same query running with different  plan, giving inferior execution time in 12c than in 10g
                          Nimish Garg

                          First thing which is to be noted is

                          this is an adaptive plan

                           

                          When a plan is adaptive, you can get more information by

                          - DBMS_XPLAN.display_cursor(format => 'adaptive')

                          also do not forget to add /*+ GATHER_PLAN_STATISTICS */ in your query

                           

                          In my experience Oracle 12c is highly sensitive to statistics. You might want to gather stats of all the tables and retry it.

                           

                          Also you can play with OPT_PARAM hint with OPTIMIZER_ADAPTIVE_FEATURES

                          and in worse case scenario try using optimizer_features_enable='11.2.0.4'

                          • 13. Re: Same query running with different  plan, giving inferior execution time in 12c than in 10g
                            seekeroftruth

                            Thanks Nimish.

                            yes it's an Adaptive Plan.

                             

                            Thing is stats in 12c is more updated than 10g.

                             

                            Please see below.

                             

                            Capture.PNG

                            • 14. Re: Same query running with different  plan, giving inferior execution time in 12c than in 10g
                              seekeroftruth

                              How do I know if it's the reason for query running longer? Please elaborate.

                              1 2 Previous Next