12 Replies Latest reply: Jul 13, 2013 5:47 AM by Hoek RSS

    SQL statement tuning request

    967473

      The following SQL statement has been identified to perform poorly.

       

      This is the statement:

       

      {code}

      TRUNCATE TABLE order_hdr_work;

      INSERT

        /*+ APPEND */

      INTO order_hdr_work

        (SELECT ORDER_KEY,

            MIN(COMPANY_NB) COMPANY_NB,

            MIN(ENTITY_NB) ENTITY_NB,

            MIN(MASTER_CHANNEL) MASTER_CHANNEL,

            MIN(CHANNEL) CHANNEL,

            MIN(df.ORDER_NB) ORDER_NB,

            MIN(df.STORE_NB) STORE_NB,

            MIN(df.ORDER_DATE) ORDER_DATE,

            MIN(ORDER_TIME) ORDER_TIME,

            MIN(CUSTOMER_NB) CUSTOMER_NB,

            MIN(TENDER_SERIAL_NO) TENDER_SERIAL_NO,

            MAX(dp.PAY_TYPE_CD) PAY_TYPE_CD_DIR,

            MAX(rp.PAY_TYPE_CD) PAY_TYPE_CD_RET,

            MIN(DEFER_BILL_IN) DEFER_BILL_IN,

            MIN(CREDIT_PLAN_ID) CREDIT_PLAN_ID,

            MIN(SOURCE_CD_HDR) SOURCE_CD,

            MIN(SUBSTR(COUPON_CD, 1, 10)) COUPON_CD,

            MIN(TYPE_CD) TYPE_CD,

            MIN(STATUS_CD) STATUS_CD,

            SUBSTR(MAX(TO_CHAR(FILE_DATE,'YYYYMMDD')

            ||SYS_HOLD_CD),9) SYS_HOLD_CD,

            SUBSTR(MAX(TO_CHAR(FILE_DATE,'YYYYMMDD')

            ||USR_HOLD_CD),9) USR_HOLD_CD,

            MIN(EMPLOYEE_ID) EMPLOYEE_ID,

            MIN(EQUIPMENT_NB) EQUIPMENT_NB,

            MIN(RETURN_ONLY_IND) RETURN_ONLY_IND,

            MIN(RECIPIENT_QTY) RECIPIENT_QTY,

            MIN(SHIPPED_DATE) FIRST_SHIP_DATE,

            MIN(RETURN_DATE) FIRST_RETURN_DATE,

            MAX(SHIPPED_DATE) LAST_SHIP_DATE,

            MAX(RETURN_DATE) LAST_RETURN_DATE,

            SUM(

            CASE

              WHEN EXCLUDE_IND = 'N'

              THEN ORDERED_QTY

              ELSE 0

            END) PROD_ORDERED_QTY,

            SUM(

            CASE

              WHEN EXCLUDE_IND = 'N'

              THEN SHIPPED_QTY

              ELSE 0

            END) PROD_SHIPPED_QTY,

            SUM(

            CASE

              WHEN EXCLUDE_IND = 'Y'

              THEN 0

              WHEN DIRECT_RETURN_AT_RETAIL = 'Y'

              THEN 0

              ELSE RETURNED_QTY

            END) PROD_RETURNED_QTY,

            SUM(SHIPPED_QTY) TOTAL_SHIPPED_QTY,

            (SUM(SHIPPED_QTY) - SUM(RETURNED_QTY)) TOTAL_NET_QTY,

            SUM(

            CASE

              WHEN EXCLUDE_IND = 'N'

              THEN ORDERED_GROSS_AMT

              ELSE 0

            END) PROD_ORDERED_GROSS_AMT,

            SUM(

            CASE

              WHEN EXCLUDE_IND = 'N'

              THEN ORDERED_SOLD_AMT

              ELSE 0

            END) PROD_ORDERED_SOLD_AMT,

            SUM(

            CASE

              WHEN EXCLUDE_IND = 'N'

              THEN ORDERED_COST_AMT

              ELSE 0

            END) PROD_ORDERED_COST_AMT,

            SUM(

            CASE

              WHEN EXCLUDE_IND = 'N'

              THEN SHIPPED_GROSS_AMT

              ELSE 0

            END) PROD_SHIPPED_GROSS_AMT,

            SUM(

            CASE

              WHEN EXCLUDE_IND = 'N'

              THEN SHIPPED_SOLD_AMT

              ELSE 0

            END) PROD_SHIPPED_SOLD_AMT,

            SUM(

            CASE

              WHEN EXCLUDE_IND = 'N'

              THEN SHIPPED_COST_AMT

              ELSE 0

            END) PROD_SHIPPED_COST_AMT,

            SUM(

            CASE

              WHEN EXCLUDE_IND = 'N'

              THEN RETURNED_GROSS_AMT

              ELSE 0

            END) PROD_RETURNED_GROSS_AMT,

            SUM(

            CASE

              WHEN EXCLUDE_IND = 'N'

              THEN RETURNED_SOLD_AMT

              ELSE 0

            END) PROD_RETURNED_SOLD_AMT,

            SUM(

            CASE

              WHEN EXCLUDE_IND = 'N'

              THEN RETURNED_COST_AMT

              ELSE 0

            END) PROD_RETURNED_COST_AMT,

            SUM(

            CASE

              WHEN EXCLUDE_IND = 'N'

              THEN NET_SALES_AMT

              ELSE 0

            END) PROD_NET_SALES_AMT,

            SUM(

            CASE

              WHEN SKU_NB        = '9999998'

              AND master_channel = 'R'

              THEN ORDERED_SOLD_AMT

              ELSE 0

            END) SHIPPING_AMT,

            SUM((

            CASE

              WHEN EXCLUDE_IND = 'N'

              THEN ORDERED_GROSS_AMT

              ELSE 0

            END) + (

            CASE

              WHEN GIFTCARD_IND = 'Y'

              THEN ORDERED_GROSS_AMT

              ELSE 0

            END)) PROD_GC_ORDERED_GROSS_AMT,

            SUM(ORDERED_GROSS_AMT) TOTAL_ORDERED_GROSS_AMT,

            SUM(ORDERED_SOLD_AMT) TOTAL_ORDERED_SOLD_AMT,

            SUM(NET_SALES_AMT) TOTAL_NET_SALES_AMT,

            SUM(

            CASE

              WHEN GIFTCARD_IND = 'Y'

              THEN ORDERED_QTY

              ELSE 0

            END) GIFTCARD_QTY,

            SUM(

            CASE

              WHEN GIFTCARD_IND = 'Y'

              THEN ORDERED_GROSS_AMT

              ELSE 0

            END) GIFTCARD_AMT,

            0 GIFTCARD_REDEEMED_AMT

          FROM test_agg df

            -----

          LEFT OUTER JOIN retail_pay rp

          ON df.store_nb    = rp.store_nb

          AND df.order_nb   = rp.order_nb

          AND df.order_date = rp.order_date

            -----

          LEFT OUTER JOIN direct_pay dp

          ON df.order_nb = dp.order_nb

            -----

          WHERE trim(CUST_EXCLUDE_IND) = 'N'

          AND trim(CLOSED_IND)         = 'N'

          AND trim(DUPE_IND)           = 'N'

          AND (trim(ENTITY_NB)        IS NULL

          OR trim(ENTITY_NB)           = '30')

          GROUP BY ORDER_KEY

        );

      {/code}

       

      The version of the database is 11.2.0.1.0.

       

      These are the parameters relevant to the optimizer:

      {code}

      SQL>
      SQL> show parameter optimizer

      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      optimizer_capture_sql_plan_baselines boolean     FALSE
      optimizer_dynamic_sampling           integer     2
      optimizer_features_enable            string      11.2.0.1
      optimizer_index_caching              integer     0
      optimizer_index_cost_adj             integer     100
      optimizer_mode                       string      ALL_ROWS
      optimizer_secure_view_merging        boolean     TRUE
      optimizer_use_invisible_indexes      boolean     FALSE
      optimizer_use_pending_statistics     boolean     FALSE
      optimizer_use_sql_plan_baselines     boolean     TRUE

      SQL>

      SQL> show parameter db_file_multi

      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      db_file_multiblock_read_count        integer     128

      SQL>

      SQL> show parameter db_block_size

      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      db_block_size                        integer     8192

      SQL>

      SQL> show parameter cursor_sharing

      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      cursor_sharing                       string      EXACT

      SQL>

      {code}

       

      Here is the output of EXPLAIN PLAN:

       

      {code}

      EXECUTION_PATH                                                                           COST               MEGS OPTIMIZER
      -------------------------------------------------------------------------------- ------------ ------------------ ---------
        HASH GROUP BY                                                                      10527656             84,864
          HASH JOIN RIGHT OUTER                                                            10519557             84,864
            TABLE ACCESS FULL RETAIL_PAY                                                      64692              1,386
            HASH JOIN RIGHT OUTER                                                           6305777             79,325
              TABLE ACCESS FULL DIRECT_PAY                                                    26359                325
              TABLE ACCESS FULL TEST_AGG                                                    2291111             77,347

      6 rows selected.

      {code}

       

      I'm looking forward for suggestions how to improve the performance of this statement.

       

      Thanks!

       

       

       

      {code}

        • 1. Re: SQL statement tuning request
          Hoek

          Not sure how you obtained the execution plan.  Where are the estimated rows/cardinalities? Can't you use dbms_xplan?

          See: About Oracle: dbms_xplan.display_cursor for examples.

          Also I, given the append hint, I'd expect a 'LOAD AS SELECT' in the execution path as well.

           

          Besides that,  Parallel DML is something you could try, ofcourse.

          Enable it (you need to enable it explicitly by altering your session) and it might make a (huge) difference.

          See: Types of Parallelism

          • 2. Re: SQL statement tuning request
            SomeoneElse

            Are all those TRIMs necessary?  They can prevent the use of an index.

             

            Your statement is hard to read because you haven't qualified the column names with a table alias (or the table name itself).

             

            When we look at a column name, we have no idea which table it came from.


            • 3. Re: SQL statement tuning request
              967473

              I have the query running without the append hint and added:

               

              ALTER SESSION FORCE PARALLEL DDL;

               

              SomeoneElse:

               

              The only columns coming from joined tables are:

                 MAX(dp.PAY_TYPE_CD) PAY_TYPE_CD_DIR,

                 MAX(rp.PAY_TYPE_CD) PAY_TYPE_CD_RET,

               

              All other columns are from TEST_AGG df

               

              Seems {code} isn't working anymore?

              • 4. Re: SQL statement tuning request
                967473

                Plan hash value: 3827034007

                ----------------------------------------------------------------------------------------------
                | Id  | Operation               | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                ----------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT        |            |   207M|    82G|       |    10M  (1)| 35:05:32 |
                |   1 |  HASH GROUP BY          |            |   207M|    82G|       |    10M  (1)| 35:05:32 |
                |*  2 |   HASH JOIN RIGHT OUTER |            |   207M|    82G|  1979M|    10M  (1)| 35:03:55 |
                |   3 |    TABLE ACCESS FULL    | RETAIL_PAY |    51M|  1385M|       | 64692   (1)| 00:12:57 |
                |*  4 |    HASH JOIN RIGHT OUTER|            |   207M|    77G|   715M|  6305K  (1)| 21:01:10 |
                |   5 |     TABLE ACCESS FULL   | DIRECT_PAY |    34M|   325M|       | 26359   (1)| 00:05:17 |
                |*  6 |     TABLE ACCESS FULL   | TEST_AGG   |   207M|    75G|       |  2291K  (1)| 07:38:14 |
                ----------------------------------------------------------------------------------------------

                Predicate Information (identified by operation id):
                ---------------------------------------------------

                   2 - access("DF"."ORDER_DATE"="RP"."ORDER_DATE"(+) AND
                              "DF"."ORDER_NB"="RP"."ORDER_NB"(+) AND "DF"."STORE_NB"="RP"."STORE_NB"(+))
                   4 - access("DF"."ORDER_NB"="DP"."ORDER_NB"(+))
                   6 - filter(TRIM("DF"."CUST_EXCLUDE_IND")='N' AND TRIM("DF"."CLOSED_IND")='N' AND
                              TRIM("DF"."DUPE_IND")='N' AND (TRIM("DF"."ENTITY_NB") IS NULL OR
                              TRIM("DF"."ENTITY_NB")='30'))

                Note
                -----
                   - dynamic sampling used for this statement (level=2)

                • 5. Re: SQL statement tuning request
                  Hoek

                  Did you just remove the append hint?

                  You'll need to replace it with the parallel hint....

                  And you'll need an Enterprise Edition of your database version. You can check that by issuing a select * from v$version.

                  • 6. Re: SQL statement tuning request
                    967473

                    We are using SE One.

                    • 7. Re: SQL statement tuning request
                      Hoek

                      In that case my suggestion to try Parallel DML will not be of much use...

                      Another thing you need to find out is why your APPEND hint is being ignored.

                      Are your tables in logging or nologging mode? Is the database in archive mode or noarchivelog mode?

                      See Loading Tables

                      and Ask Tom "Append hint in archivelog mode."

                      • 8. Re: SQL statement tuning request
                        Nikolay Savvinov

                        Hi,

                         

                        the plan says the query would return 207M rows -- is this within the right ballpark? If yes, what is the application doing with all these data (it's obviously far more than a human user can read within a reasonable time)?

                         

                        Best regards,

                        Nikolay

                        • 9. Re: SQL statement tuning request
                          967473

                          I added the append hint after switching the table to nologging. The db is in noarchivelog mode.

                          • 10. Re: SQL statement tuning request
                            Hoek

                            And did you get a different execution plan?

                            • 11. Re: SQL statement tuning request
                              967473

                              With append hint:

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

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

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

                              |   0 | INSERT STATEMENT         |                |   207M|    84G|       |    12M  (1)| 41:53:16 |

                              |   1 |  LOAD AS SELECT          | ORDER_HDR_WORK |       |       |       |            |          |

                              |   2 |   HASH GROUP BY          |                |   207M|    84G|       |    12M  (1)| 41:53:16 |

                              |*  3 |    HASH JOIN RIGHT OUTER |                |   207M|    84G|  1782M|    12M  (1)| 41:51:39 |

                              |   4 |     TABLE ACCESS FULL    | RETAIL_PAY     |    41M|  1307M|       | 64624   (1)| 00:12:56 |

                              |*  5 |     HASH JOIN RIGHT OUTER|                |   207M|    78G|   781M|  6315K  (1)| 21:03:01 |

                              |   6 |      TABLE ACCESS FULL   | DIRECT_PAY     |    31M|   420M|       | 26346   (1)| 00:05:17 |

                              |*  7 |      TABLE ACCESS FULL   | TEST_AGG       |   207M|    75G|       |  2289K  (1)| 07:37:55 |

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

                               

                              without append hint:

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

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

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

                              |   0 | INSERT STATEMENT         |                |   207M|    84G|       |    12M  (1)| 41:53:16 |

                              |   1 |  LOAD TABLE CONVENTIONAL | ORDER_HDR_WORK |       |       |       |            |          |

                              |   2 |   HASH GROUP BY          |                |   207M|    84G|       |    12M  (1)| 41:53:16 |

                              |*  3 |    HASH JOIN RIGHT OUTER |                |   207M|    84G|  1782M|    12M  (1)| 41:51:39 |

                              |   4 |     TABLE ACCESS FULL    | RETAIL_PAY     |    41M|  1307M|       | 64624   (1)| 00:12:56 |

                              |*  5 |     HASH JOIN RIGHT OUTER|                |   207M|    78G|   781M|  6315K  (1)| 21:03:01 |

                              |   6 |      TABLE ACCESS FULL   | DIRECT_PAY     |    31M|   420M|       | 26346   (1)| 00:05:17 |

                              |*  7 |      TABLE ACCESS FULL   | TEST_AGG       |   207M|    75G|       |  2289K  (1)| 07:37:55 |

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

                               

                              No difference?

                              • 12. Re: SQL statement tuning request
                                Hoek

                                Well, we now see a LOAD AS SELECT in the execution plan, but besides that, yes, no difference...

                                Given your predicates (the WHERE CLAUSE) , you might want to add Function Based Indexes for the columns you're TRIMming. Here's a good explanation of how they work:

                                http://www.oracle-base.com/articles/8i/function-based-indexes.php