11 Replies Latest reply on Jul 18, 2014 9:15 PM by rp0428 Branched to a new discussion.

    improve query performance

    Jitendra-OC

      SQL Query

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

       

      SELECT DISTINCT '1' AS

      status,

        2 AS

      partner_type,

      CASE

      WHEN f.ship_to_cust_id > ' ' THEN

        f.ship_cust_name1 || '|' || f.ship_to_cust_id

      ELSE

        f.ship_cust_name1 || '|' || f.cust_name || '|' || f.location

      END AS

      partner_name,

        CASE

      WHEN f.ship_to_cust_id > ' ' THEN

        f.ship_to_cust_id

      ELSE

        f.location

      END AS

      partner_number,

        2 AS

      deleted_flag,

        TRUNC(sysdate) AS

      last_update_date,

        'P' AS

      sr_instance_code,

        1 AS

      process_flag,

        '0' AS

      cost_account_number

      FROM sysadm.ps_pl_item_attrib a,

        sysadm.ps_bu_items_inv b,

        sysadm.ps_master_item_tbl c,

        sysadm.ps_pl_group_in e,

        sysadm.ps_in_demand f

      WHERE a.planned_by <> '4'

      AND a.business_unit = b.business_unit

      AND a.inv_item_id = b.inv_item_id

      AND b.itm_status_current < '4'

      AND b.inv_item_id = c.inv_item_id

      AND c.itm_status_current < '4'

      AND c.setid =

        (SELECT d.setid

         FROM sysadm.ps_set_cntrl_rec d

         WHERE d.recname = 'MASTER_ITEM_TBL'

         AND d.setcntrlvalue = a.business_unit)

      AND a.business_unit = e.business_unit

      AND e.bu_group = 'ASCP'

      AND b.business_unit = f.business_unit

      AND b.inv_item_id = f.inv_item_id

      AND f.in_fulfill_state <= 60

      AND f.demand_source NOT IN('SF',   'WM')

      AND TRUNC(f.sched_dttm) <= to_date('12/31/' || to_char(add_months(sysdate,   36),   'YYYY'),   'MM/DD/YYYY')

      ORDER BY partner_name ;

       

       

      Explain Plan

       

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

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

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

      |   0 | SELECT STATEMENT                  |                    |    43 |  6149 | 92566   (1)| 00:18:31 |

      |   1 |  SORT UNIQUE                      |                    |    43 |  6149 | 92565   (1)| 00:18:31 |

      |   2 |   NESTED LOOPS                    |                    |       |       |            |          |

      |   3 |    NESTED LOOPS                   |                    |   680 | 97240 | 92516   (1)| 00:18:31 |

      |   4 |     NESTED LOOPS                  |                    |   680 | 84320 | 91155   (1)| 00:18:14 |

      |*  5 |      HASH JOIN                    |                    |  6866 |   750K| 91155   (1)| 00:18:14 |

      |   6 |       NESTED LOOPS                |                    |       |       |            |          |

      |   7 |        NESTED LOOPS               |                    |  6957 |   631K| 84200   (1)| 00:16:51 |

      |*  8 |         TABLE ACCESS FULL         | PS_IN_DEMAND       |  6957 |   502K| 70281   (1)| 00:14:04 |

      |*  9 |         INDEX UNIQUE SCAN         | PS_BU_ITEMS_INV    |     1 |       |     1   (0)| 00:00:01 |

      |* 10 |        TABLE ACCESS BY INDEX ROWID| PS_BU_ITEMS_INV    |     1 |    19 |     2   (0)| 00:00:01 |

      |* 11 |       TABLE ACCESS FULL           | PS_PL_ITEM_ATTRIB  |  1579K|    28M|  6948   (2)| 00:01:24 |

      |* 12 |      INDEX UNIQUE SCAN            | PS_PL_GROUP_IN     |     1 |    12 |     0   (0)| 00:00:01 |

      |* 13 |     INDEX UNIQUE SCAN             | PS_MASTER_ITEM_TBL |     1 |       |     1   (0)| 00:00:01 |

      |* 14 |      INDEX RANGE SCAN             | PSASET_CNTRL_REC   |     1 |    27 |     3   (0)| 00:00:01 |

      |* 15 |    TABLE ACCESS BY INDEX ROWID    | PS_MASTER_ITEM_TBL |     1 |    19 |     2   (0)| 00:00:01 |

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

       

       

      Predicate Information (identified by operation id):

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

       

       

         5 - access("A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT" AND "A"."INV_ITEM_ID"="B"."INV_ITEM_ID")

         8 - filter(TO_NUMBER("F"."IN_FULFILL_STATE")<=60 AND

                    TRUNC(INTERNAL_FUNCTION("F"."SCHED_DTTM"))<=TO_DATE('12/31/'||TO_CHAR(ADD_MONTHS(SYSDATE@!,36),'

                    YYYY'),'MM/DD/YYYY') AND "F"."DEMAND_SOURCE"<>'WM' AND "F"."DEMAND_SOURCE"<>'SF')

         9 - access("B"."BUSINESS_UNIT"="F"."BUSINESS_UNIT" AND "B"."INV_ITEM_ID"="F"."INV_ITEM_ID")

        10 - filter("B"."ITM_STATUS_CURRENT"<'4')

        11 - filter("A"."PLANNED_BY"<>'4')

        12 - access("E"."BU_GROUP"='ASCP' AND "A"."BUSINESS_UNIT"="E"."BUSINESS_UNIT")

        13 - access("C"."SETID"= (SELECT "D"."SETID" FROM "SYSADM"."PS_SET_CNTRL_REC" "D" WHERE

                    "D"."RECNAME"='MASTER_ITEM_TBL' AND "D"."SETCNTRLVALUE"=:B1) AND

                    "B"."INV_ITEM_ID"="C"."INV_ITEM_ID")

        14 - access("D"."SETCNTRLVALUE"=:B1 AND "D"."RECNAME"='MASTER_ITEM_TBL')

        15 - filter("C"."ITM_STATUS_CURRENT"<'4')

        • 1. Re: improve query performance
          sb92075

          >AND TRUNC(f.sched_dttm) <= to_date('12/31/' || to_char(add_months(sysdate,   36),   'YYYY'),   'MM/DD/YYYY')

          not as above but  as below

          AND f.sched_dttm <= to_date('12/31/' || to_char(add_months(sysdate,   36),   'YYYY'),   'MM/DD/YYYY')

          & make sure that SCHED_DTTM is indexed

          • 2. Re: improve query performance
            Martin Preiss

            some comments without particular order:

            • is the distinct necessary?
            • are there indexes on PS_IN_DEMAND? And what indexes are definied on the other tables?
            • is the optimizer's expectation that the conditions for PS_IN_DEMAND reduce the number of rows to 6957 somehow in the right ballpark?

                   f.in_fulfill_state <= 60

               AND f.demand_source NOT IN('SF',   'WM')

               AND TRUNC(f.sched_dttm) <= to_date('12/31/' || to_char(add_months(sysdate,   36),   'YYYY'),   'MM/DD/YYYY')

            If the cardinalities are much bigger than expected then the following Nested Loops joins will become quite expensive.

            If the given predicates are selective then maybe a corresponding index could be helpful - since the optimizer already expects the FULL TABLE SCAN on PS_IN_DEMAND to be a lot of work.

            • 3. Re: improve query performance
              Jitendra-OC

              Yes Distinct is necessary.

              indexes on PS_IN_DEMAND as below

               

               

               

              TABLE_NAME                          UNQ INDEX_NAME                     COLUMN_NAME        FuncIxd TABLESPACE_NAME

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

              SYSADM.PS_IN_DEMAND                 NON PSAIN_DEMAND                   PICK_BATCH_ID              PSINDEX

                                                                                     BUSINESS_UNIT              PSINDEX

                                                                                     PICKLIST_LINE_NO           PSINDEX

               

               

                                                  NON PSBIN_DEMAND                   IN_PROCESS_DATE            PSINDEX

               

               

                                                  NON PSCIN_DEMAND                   OMB_PROCESS_DATE           PSINDEX

               

               

                                                  NON PSDIN_DEMAND                   INV_ITEM_ID                PSINDEX

                                                                                     BUSINESS_UNIT              PSINDEX

               

               

                                                  NON PSEIN_DEMAND                   DELIVERY_ID                PSINDEX

                                                                                     BUSINESS_UNIT              PSINDEX

               

               

                                                  NON PSFIN_DEMAND                   BUSINESS_UNIT              PSINDEX

                                                                                     LOAD_ID                    PSINDEX

                                                                                     IN_FULFILL_STATE           PSINDEX

               

               

                                                  NON PSGIN_DEMAND                   BUSINESS_UNIT              PSINDEX

                                                                                     SHIP_ID                    PSINDEX

               

               

                                                  NON PSHIN_DEMAND                   BUSINESS_UNIT              PSINDEX

                                                                                     IN_FULFILL_STATE           PSINDEX

                                                                                     SHIP_DTTM                  PSINDEX

                                                                                     CANCEL_DTTM                PSINDEX

               

               

                                                  NON PSNIN_DEMAND                   LINE_FIELD_DTTM_A          PSINDEX

                                                                                     DEMAND_SOURCE              PSINDEX

                                                                                     SCHED_DTTM                 PSINDEX

                                                                                     SCHED_ARRV_DTTM            PSINDEX

               

               

                                                  NON PSOIN_DEMAND                   SOURCE_BUS_UNIT            PSINDEX

                                                                                     DEMAND_SOURCE              PSINDEX

                                                                                     ORDER_NO                   PSINDEX

                                                                                     ORDER_INT_LINE_NO          PSINDEX

                                                                                     SCHED_LINE_NBR             PSINDEX

                                                                                     IN_FULFILL_STATE           PSINDEX

               

               

                                                  NON PSPIN_DEMAND                   BUSINESS_UNIT              PSINDEX

                                                                                     ORDER_NO                   PSINDEX

               

               

                                                  NON PSQIN_DEMAND                   BUSINESS_UNIT              PSINDEX

                                                                                     INV_ITEM_ID                PSINDEX

                                                                                     IN_FULFILL_STATE           PSINDEX

               

               

              Fri Jul 18                                                                                                page    2

                                                                 Index listing

               

               

              TABLE_NAME                          UNQ INDEX_NAME                     COLUMN_NAME        FuncIxd TABLESPACE_NAME

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

              SYSADM.PS_IN_DEMAND                 NON PSQIN_DEMAND                   DEPLETED_DTTM              PSINDEX

               

               

                                                  NON PSRIN_DEMAND                   BUSINESS_UNIT              PSINDEX

                                                                                     INTERUNIT_ID               PSINDEX

                                                                                     INTERUNIT_STAT             PSINDEX

                                                                                     INTERUNIT_FLG              PSINDEX

                                                                                     IN_FULFILL_STATE           PSINDEX

               

               

                                                  NON PSSIN_DEMAND                   IN_FULFILL_STATE           PSINDEX

                                                                                     INTERUNIT_FLG              PSINDEX

               

               

                                                  NON PSXIN_DEMAND                   BUSINESS_UNIT              PSINDEX

                                                                                     SHIP_ID                    PSINDEX

                                                                                     REQ_ID                     PSINDEX

                                                                                     ORDER_INT_LINE_NO          PSINDEX

               

               

                                                  NON PSYIN_DEMAND                   INV_ITEM_ID                PSINDEX

                                                                                     SOURCE_BUS_UNIT            PSINDEX

                                                                                     DEMAND_SOURCE              PSINDEX

                                                                                     ORDER_NO                   PSINDEX

                                                                                     ORDER_INT_LINE_NO          PSINDEX

                                                                                     SCHED_LINE_NBR             PSINDEX

               

               

                                                  NON PSZIN_DEMAND                   DESTIN_BU                  PSINDEX

                                                                                     INTERUNIT_FLG              PSINDEX

                                                                                     IN_FULFILL_STATE           PSINDEX

                                                                                     INV_ITEM_ID                PSINDEX

                                                                                     INTERUNIT_ID               PSINDEX

               

               

                                                  UNI PS_IN_DEMAND                   BUSINESS_UNIT              PSINDEX

                                                                                     DEMAND_SOURCE              PSINDEX

                                                                                     SOURCE_BUS_UNIT            PSINDEX

                                                                                     ORDER_NO                   PSINDEX

                                                                                     ORDER_INT_LINE_NO          PSINDEX

                                                                                     SCHED_LINE_NBR             PSINDEX

                                                                                     INV_ITEM_ID                PSINDEX

                                                                                     DEMAND_LINE_NO             PSINDEX

               

               

               

              TABLE_NAME                          UNQ INDEX_NAME                     COLUMN_NAME        FuncIxd TABLESPACE_NAME

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

              SYSADM.PS_BU_ITEMS_INV              NON PSABU_ITEMS_INV                CYCLE_INSTANCE             PSINDEX

               

               

                                                  NON PSBBU_ITEMS_INV                INV_ITEM_ID                PSINDEX

                                                                                     BUSINESS_UNIT              PSINDEX

               

               

                                                  NON PSEBU_ITEMS_INV                BUSINESS_UNIT              PSINDEX

                                                                                     ISOLATE_ITEM_FLG           PSINDEX

                                                                                     INV_ITEM_ID                PSINDEX

               

               

                                                  NON PSXBU_ITEMS_INV                DT_TIMESTAMP               PSINDEX

                                                                                     INV_ITEM_ID                PSINDEX

                                                                                     BUSINESS_UNIT              PSINDEX

               

               

                                                  UNI PS_BU_ITEMS_INV                BUSINESS_UNIT              PSINDEX

                                                                                     INV_ITEM_ID                PSINDEX

               

               

                                                                 Index listing

               

               

              TABLE_NAME                          UNQ INDEX_NAME                     COLUMN_NAME        FuncIxd TABLESPACE_NAME

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

              SYSADM.PS_PL_ITEM_ATTRIB            UNI PS_PL_ITEM_ATTRIB              BUSINESS_UNIT              PSINDEX

                                                                                     INV_ITEM_ID                PSINDEX

              • 4. Re: improve query performance
                Martin Preiss

                so SCHED_DTTM is only included in PSNIN_DEMAND and not the leading column - and of course trunc(SCHED_DTTM) is not indexed. And IN_FULFILL_STATE is part of multiple indexes and leads in PSSIN_DEMAND - but I guess that in_fulfill_state <= 60 is not extremely selective (i.e. returns only a small part of the table); furthermore I guess that in_fulfill_state is not of type number (so we see an implicit type conversion). Maybe a selective index access is not possible at all.

                 

                So the next question is: how many rows are in PS_IN_DEMAND and how many of them match the given condition?

                • 5. Re: improve query performance
                  Jitendra-OC

                  After making the changes cost has increased.

                   

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

                  Plan hash value: 1653778414

                   

                   

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

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

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

                  |   0 | SELECT STATEMENT                  |                    |   781 |   109K|       |   106K  (1)| 00:21:17 |

                  |   1 |  SORT UNIQUE                      |                    |   781 |   109K|       |   106K  (1)| 00:21:17 |

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

                  |*  3 |    HASH JOIN                      |                    | 12500 |  1745K|  1664K|   105K  (1)| 00:21:11 |

                  |*  4 |     HASH JOIN                     |                    | 12500 |  1513K|  9384K|   102K  (1)| 00:20:30 |

                  |*  5 |      HASH JOIN                    |                    |   154K|  7564K|  6576K| 31242   (1)| 00:06:15 |

                  |   6 |       NESTED LOOPS                |                    |       |       |       |            |          |

                  |   7 |        NESTED LOOPS               |                    |   156K|  4738K|       |  6404   (1)| 00:01:17 |

                  |*  8 |         INDEX RANGE SCAN          | PS_PL_GROUP_IN     |     4 |    48 |       |     1   (0)| 00:00:01 |

                  |*  9 |         INDEX RANGE SCAN          | PS_PL_ITEM_ATTRIB  |  4172 |       |       |    17   (0)| 00:00:01 |

                  |* 10 |        TABLE ACCESS BY INDEX ROWID| PS_PL_ITEM_ATTRIB  | 39129 |   726K|       |  1601   (1)| 00:00:20 |

                  |* 11 |       TABLE ACCESS FULL           | PS_BU_ITEMS_INV    |  1591K|    28M|       | 22172   (1)| 00:04:27 |

                  |* 12 |      TABLE ACCESS FULL            | PS_IN_DEMAND       |   121K|  8770K|       | 70282   (1)| 00:14:04 |

                  |* 13 |     TABLE ACCESS FULL             | PS_MASTER_ITEM_TBL |   311K|  5786K|       |  2887   (1)| 00:00:35 |

                  |* 14 |    INDEX RANGE SCAN               | PSASET_CNTRL_REC   |     1 |    27 |       |     3   (0)| 00:00:01 |

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

                   

                   

                  Predicate Information (identified by operation id):

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

                   

                   

                     2 - filter("C"."SETID"= (SELECT "D"."SETID" FROM "SYSADM"."PS_SET_CNTRL_REC" "D" WHERE

                                "D"."RECNAME"='MASTER_ITEM_TBL' AND "D"."SETCNTRLVALUE"=:B1))

                     3 - access("B"."INV_ITEM_ID"="C"."INV_ITEM_ID")

                     4 - access("B"."BUSINESS_UNIT"="F"."BUSINESS_UNIT" AND "B"."INV_ITEM_ID"="F"."INV_ITEM_ID")

                     5 - access("A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT" AND "A"."INV_ITEM_ID"="B"."INV_ITEM_ID")

                     8 - access("E"."BU_GROUP"='ASCP')

                     9 - access("A"."BUSINESS_UNIT"="E"."BUSINESS_UNIT")

                    10 - filter("A"."PLANNED_BY"<>'4')

                    11 - filter("B"."ITM_STATUS_CURRENT"<'4')

                    12 - filter(TO_NUMBER("F"."IN_FULFILL_STATE")<=60 AND

                                "F"."SCHED_DTTM"<=TO_DATE('12/31/'||TO_CHAR(ADD_MONTHS(SYSDATE@!,36),'YYYY'),'MM/DD/YYYY') AND

                                "F"."DEMAND_SOURCE"<>'WM' AND "F"."DEMAND_SOURCE"<>'SF')

                    13 - filter("C"."ITM_STATUS_CURRENT"<'4')

                    14 - access("D"."SETCNTRLVALUE"=:B1 AND "D"."RECNAME"='MASTER_ITEM_TBL')

                  • 6. Re: improve query performance
                    Martin Preiss

                    increased cost is not necessarily a problem. Cost is only a unit the optimizer uses to determine which access strategy is the best one - based on the known characteristics of data. At the end of the day the question is: how long does it take the query to complete using the different plans? (and another question would be: do you need the trunc to produce the correct results?)

                    • 7. Re: improve query performance
                      Jitendra-OC

                      15:01:46 SYSADM/fssup/FSSUP>select count(*) from ps_in_demand;

                       

                       

                        COUNT(*)

                      ----------

                         2798026

                       

                       

                      1 row selected.

                       

                       

                      15:14:49 SYSADM/fssup/FSSUP>select count(*) from ps_in_demand where in_fulfill_state<=60;

                       

                       

                        COUNT(*)

                      ----------

                           25086

                       

                      15:16:41 SYSADM/fssup/FSSUP>select count(*) from ps_in_demand where demand_source not in ('SF','VM');

                       

                       

                        COUNT(*)

                      ----------

                         2798026

                       

                      15:17:56 SYSADM/fssup/FSSUP> select count(*) from ps_in_demand where TRUNC(sched_dttm) <= to_date('12/31/' || to_char(add_months(sysdate,   36),   'YYYY'),   'MM/DD/YYYY');

                       

                       

                        COUNT(*)

                      ----------

                         2797996

                       

                       

                      15:19:11 SYSADM/fssup/FSSUP>  select count(*) from ps_in_demand f where f.in_fulfill_state <= 60

                      15:19:23   2   AND f.demand_source NOT IN('SF',   'WM')

                      15:19:23   3   AND TRUNC(f.sched_dttm) <= to_date('12/31/' || to_char(add_months(sysdate,   36),   'YYYY'),   'MM/DD/YYYY');

                       

                       

                        COUNT(*)

                      ----------

                           24465

                      • 8. Re: improve query performance
                        Jitendra-OC

                        Result has been changed after removing trunc. Also row count is different with different plan.

                        • 9. Re: improve query performance
                          sb92075

                          what is result of following SQL

                           

                          select count(*) from ps_in_demand f where

                          TRUNC(f.sched_dttm) <= to_date('12/31/' || to_char(add_months(sysdate,   36),   'YYYY'),   'MM/DD/YYYY');

                           

                          select count(*) from ps_in_demand f where

                          f.sched_dttm <= to_date('12/31/' || to_char(add_months(sysdate,   36),   'YYYY'),   'MM/DD/YYYY');


                          • 10. Re: improve query performance
                            Jitendra-OC

                            15:50:19   2  TRUNC(f.sched_dttm) <= to_date('12/31/' || to_char(add_months(sysdate,   36),   'YYYY'),   'MM/DD/YYYY');

                             

                             

                              COUNT(*)

                            ----------

                               2797996

                            15:50:54 SYS/fssup/FSSUP>select count(*) from sysadm.ps_in_demand f where

                            15:51:01   2  f.sched_dttm <= to_date('12/31/' || to_char(add_months(sysdate,   36),   'YYYY'),   'MM/DD/YYYY');

                             

                             

                              COUNT(*)

                            ----------

                               2797996

                            • 11. Re: improve query performance
                              rp0428

                              Please don't just post a bunch of code in the forum.

                               

                              What is your question or issue?

                               

                              If you have a question about tuning please read the FAQ for how to submit a tuning request and provide the info needed to help you.

                               

                              The most important piece of info is some evidence/proof that the query actually needs to be tuned.