This discussion is archived
12 Replies Latest reply: Jul 13, 2013 3:47 AM by Hoek RSS

SQL statement tuning request

967473 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    We are using SE One.

  • 7. Re: SQL statement tuning request
    Hoek Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

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

  • 10. Re: SQL statement tuning request
    Hoek Guru
    Currently Being Moderated

    And did you get a different execution plan?

  • 11. Re: SQL statement tuning request
    967473 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points