7 Replies Latest reply: Sep 23, 2013 9:19 AM by Jitendra-OC RSS

    need to improve sql performance

    Jitendra-OC

      Sql Query :-

       

      SELECT   b.last_cycle_count, b.inv_item_id

          FROM ps_bu_items_inv b, ps_bu_item_util_cd u

         WHERE b.business_unit = 'BUSTO'

           AND (b.last_cycle_count <= TO_DATE('2013-08-20','YYYY-MM-DD') OR b.last_cycle_count IS NULL) 

           AND b.cycle_instance <> 31648910

           AND u.business_unit = b.business_unit

           AND u.inv_item_id = b.inv_item_id

           AND u.utiliz_group = 'CYCL'

           AND u.utiliz_cd = 'A'

           AND b.itm_status_current <> 5

           AND b.itm_status_current <> 6

           AND b.itm_status_current <> 7

           AND b.inv_item_id IN (

                  SELECT DISTINCT p.inv_item_id

                             FROM ps_physical_inv p, ps_stor_loc_inv s

                            WHERE p.business_unit = b.business_unit

                              AND s.business_unit = p.business_unit

                              AND s.storage_area = p.storage_area

                              AND s.stor_level_1 = p.stor_level_1

                              AND s.stor_level_2 = p.stor_level_2

                              AND s.stor_level_3 = p.stor_level_3

                              AND s.stor_level_4 = p.stor_level_4

                              AND s.delete_storloc_flg <> 'Y')

           AND NOT EXISTS (

                  SELECT ' '

                    FROM ps_count_tmp_inv t

                   WHERE t.process_instance = 31648910

                     AND t.business_unit = b.business_unit

                     AND t.inv_item_id = b.inv_item_id)

      ORDER BY last_cycle_count;

       

       

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

      Plan hash value: 1443343317

       

       

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

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

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

      |   0 | SELECT STATEMENT                  |                    |     1 |    53 |  2720   (1)| 00:00:33 |

      |   1 |  SORT ORDER BY                    |                    |     1 |    53 |  2720   (1)| 00:00:33 |

      |   2 |   NESTED LOOPS                    |                    |       |       |            |          |

      |   3 |    NESTED LOOPS                   |                    |     1 |    53 |    24   (0)| 00:00:01 |

      |*  4 |     TABLE ACCESS BY INDEX ROWID   | PS_BU_ITEMS_INV    |     1 |    29 |    22   (0)| 00:00:0

      |*  5 |      INDEX RANGE SCAN             | PS_BU_ITEMS_INV    |    10 |       |    18   (0)| 00:00:01 |

      |   6 |       NESTED LOOPS                |                    |       |       |            |          |

      |   7 |        NESTED LOOPS               |                    |     1 |    62 |     6   (0)| 00:00:01 |

      |*  8 |         INDEX RANGE SCAN          | PSEPHYSICAL_INV    |     1 |    36 |     3   (0)| 00:00:01 |

      |*  9 |         INDEX RANGE SCAN          | PSASTOR_LOC_INV    |     1 |       |     2   (0)| 00:00:01 |

      |* 10 |        TABLE ACCESS BY INDEX ROWID| PS_STOR_LOC_INV    |     1 |    26 |     3   (0)| 00:00:0

      |* 11 |       INDEX RANGE SCAN            | PS_COUNT_TMP_INV   |     1 |    21 |     3   (0)| 00:00:01 |

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

      |* 13 |    TABLE ACCESS BY INDEX ROWID    | PS_BU_ITEM_UTIL_CD |     1 |    24 |     2   (0)| 00:00:01

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

       

       

      Predicate Information (identified by operation id):

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

       

       

         4 - filter("B"."ITM_STATUS_CURRENT"<>:SYS_B_06 AND "B"."ITM_STATUS_CURRENT"<>:SYS_B_07 AND

                    "B"."ITM_STATUS_CURRENT"<>:SYS_B_08 AND ("B"."LAST_CYCLE_COUNT" IS NULL OR

                    "B"."LAST_CYCLE_COUNT"<=TO_DATE(:SYS_B_01,:SYS_B_02)) AND

                    "B"."CYCLE_INSTANCE"<>TO_NUMBER(:SYS_B_03))

         5 - access("B"."BUSINESS_UNIT"=:SYS_B_00)

             filter( EXISTS (SELECT 0 FROM "PS_STOR_LOC_INV" "S","PS_PHYSICAL_INV" "P" WHERE

                    "P"."INV_ITEM_ID"=:B1 AND "P"."BUSINESS_UNIT"=:B2 AND "S"."STOR_LEVEL_4"="P"."STOR_LEVEL_4" A

                    "S"."STOR_LEVEL_3"="P"."STOR_LEVEL_3" AND "S"."STOR_LEVEL_2"="P"."STOR_LEVEL_2" AND

                    "S"."STOR_LEVEL_1"="P"."STOR_LEVEL_1" AND "S"."STORAGE_AREA"="P"."STORAGE_AREA" AND

                    "S"."BUSINESS_UNIT"=:B3 AND "S"."DELETE_STORLOC_FLG"<>:SYS_B_09 AND

                    "S"."BUSINESS_UNIT"="P"."BUSINESS_UNIT") AND  NOT EXISTS (SELECT 0 FROM "PS_COUNT_TMP_INV" "T

                    WHERE "T"."INV_ITEM_ID"=:B4 AND "T"."BUSINESS_UNIT"=:B5 AND

                    "T"."PROCESS_INSTANCE"=TO_NUMBER(:SYS_B_11)))

         8 - access("P"."BUSINESS_UNIT"=:B1 AND "P"."INV_ITEM_ID"=:B2)

         9 - access("S"."BUSINESS_UNIT"=:B1 AND "S"."STORAGE_AREA"="P"."STORAGE_AREA" AND

                    "S"."STOR_LEVEL_1"="P"."STOR_LEVEL_1" AND "S"."STOR_LEVEL_2"="P"."STOR_LEVEL_2" AND

                    "S"."STOR_LEVEL_3"="P"."STOR_LEVEL_3" AND "S"."STOR_LEVEL_4"="P"."STOR_LEVEL_4")

             filter("S"."BUSINESS_UNIT"="P"."BUSINESS_UNIT")

        10 - filter("S"."DELETE_STORLOC_FLG"<>:SYS_B_09)

        11 - access("T"."PROCESS_INSTANCE"=TO_NUMBER(:SYS_B_11) AND "T"."BUSINESS_UNIT"=:B1 AND

                    "T"."INV_ITEM_ID"=:B2)

        12 - access("U"."BUSINESS_UNIT"=:SYS_B_00 AND "U"."INV_ITEM_ID"="B"."INV_ITEM_ID" AND

                    "U"."UTILIZ_GROUP"=:SYS_B_04)

       

       

      PLAN_TABLE_OUTPUT

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

        13 - filter("U"."UTILIZ_CD"=:SYS_B_05)

       

       

       

      48 rows selected.

       

       

       

       

      ***Above sql is called 3300 times in a job. SO i need to improve sql execution time. Kindly let me know How can I do it.

        • 1. Re: need to improve sql performance
          saratpvv

          How long this sql running in real time..? how do you judge that it was running 3300 times..?

          • 2. Re: need to improve sql performance
            Mark D Powell

            You query shows almost complete use of indexed access paths so there question becomes are the indexes being used the right ones, and are the indexes in question optimal for this query?  That is how many of the where clause conditions are part of the index supporting eliminatiing accessing the base table for a significant portion of the potential rows based on the condtions.

            I would look at the in clause and if either "ps_physical_inv p" or  "ps_stor_loc_inv s" has an available index on inv_item_id I would test using a coordinated sub-query in place of the in clause to if this would produce an benefit.

             

            HTH -- Mark D Powell --

            • 3. Re: need to improve sql performance
              Hoek

              Couple of pointers that should be investigated:

              Your plan shows that database parameter CURSOR_SHARING is no longer 'EXACT' (the default value), but probably 'FORCE'.

              This is usually a bad idea. Find out why that parameter has been set to non-default.

              Furthermore: the DISTINCT in your query shuld be avoided as well, it is an expensive operation, and usually indicates missing joins.

              Lastly":

              Always provide a database version etc.

              Follow these step-by-step instructions:

              Oracle related stuff: Basic SQL statement performance diagnosis - HOW TO, step by step instructions

              • 4. Re: need to improve sql performance
                Jitendra-OC

                I can not remove distinct as there are duplicate values inv_item_id. We changed to cursor_sharing to FORCE as it is recommendation from application side. We are using 11.2.0.2 as database version.

                • 5. Re: need to improve sql performance
                  Jitendra-OC

                  09:06:02 SYSADM/fssup/FSSUP>@ind

                  Enter Table Name like : PS_STOR_LOC_INV

                  Enter Index Name like :

                   

                  Mon Sep 23                                                                                                page    1

                                                                     Index listing

                   

                  TABLE_NAME                          UNQ INDEX_NAME                     COLUMN_NAME        FuncIxd TABLESPACE_NAME

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

                  SYSADM.PS_STOR_LOC_INV              UNI PSASTOR_LOC_INV                BUSINESS_UNIT              PSINDEX

                                                                                         STORAGE_AREA               PSINDEX

                                                                                         STOR_LEVEL_1               PSINDEX

                                                                                         STOR_LEVEL_2               PSINDEX

                                                                                         STOR_LEVEL_3               PSINDEX

                                                                                         STOR_LEVEL_4               PSINDEX

                                                                                         SUSPEND_FLG                PSINDEX

                                                                                         NETTABLE_FLG               PSINDEX

                                                                                         PHYS_CYC_INV_FLG           PSINDEX

                                                                                         WIP_LOC_FLG                PSINDEX

                   

                   

                   

                  10 rows selected.

                   

                  09:06:22 SYSADM/fssup/FSSUP>

                   

                   

                   

                  09:06:22 SYSADM/fssup/FSSUP>@ind

                  Enter Table Name like : ps_physical_inv

                  Enter Index Name like :

                   

                  Mon Sep 23                                                                                                page    1

                                                                     Index listing

                   

                  TABLE_NAME                          UNQ INDEX_NAME                     COLUMN_NAME        FuncIxd TABLESPACE_NAME

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

                  SYSADM.PS_PHYSICAL_INV              NON IDX$$_17D300002                BUSINESS_UNIT      ENABLED PSDEFAULT

                                                                                         INV_ITEM_ID        ENABLED PSDEFAULT

                                                                                         AVAIL_STATUS       ENABLED PSDEFAULT

                                                                                         STAGED_DATE        ENABLED PSDEFAULT

                                                                                         SYS_NC00025$       ENABLED PSDEFAULT

                   

                                                      NON IDX$$_17F6B0001                BUSINESS_UNIT              PSDEFAULT

                                                                                         INV_ITEM_ID                PSDEFAULT

                                                                                         AVAIL_STATUS               PSDEFAULT

                                                                                         STAGED_DATE                PSDEFAULT

                   

                                                      NON PSAPHYSICAL_INV                INV_ITEM_ID                PSINDEX

                                                                                         BUSINESS_UNIT              PSINDEX

                                                                                         AVAIL_STATUS               PSINDEX

                   

                                                      NON PSBPHYSICAL_INV                INV_ITEM_ID                PSINDEX

                                                                                         BUSINESS_UNIT              PSINDEX

                                                                                         INV_LOT_ID                 PSINDEX

                                                                                         AVAIL_STATUS               PSINDEX

                   

                                                      NON PSCPHYSICAL_INV                BUSINESS_UNIT              PSINDEX

                                                                                         STORAGE_AREA               PSINDEX

                   

                                                      NON PSDPHYSICAL_INV                BUSINESS_UNIT              PSINDEX

                                                                                         STORAGE_AREA               PSINDEX

                                                                                         STOR_LEVEL_1               PSINDEX

                                                                                         STOR_LEVEL_2               PSINDEX

                                                                                         STOR_LEVEL_3               PSINDEX

                                                                                         STOR_LEVEL_4               PSINDEX

                   

                                                      NON PSEPHYSICAL_INV                BUSINESS_UNIT              PSINDEX

                                                                                         INV_ITEM_ID                PSINDEX

                                                                                         STORAGE_AREA               PSINDEX

                                                                                         STOR_LEVEL_1               PSINDEX

                                                                                         STOR_LEVEL_2               PSINDEX

                                                                                         STOR_LEVEL_3               PSINDEX

                                                                                         STOR_LEVEL_4               PSINDEX

                   

                                                      NON PSFPHYSICAL_INV                CONTAINER_ID               PSINDEX

                                                                                         BUSINESS_UNIT              PSINDEX

                   

                                                      NON PSGPHYSICAL_INV                INV_ITEM_ID                PSINDEX

                                                                                         BUSINESS_UNIT              PSINDEX

                                                                                         STAGED_DATE                PSINDEX

                   

                  Mon Sep 23                                                                                                page    2

                                                                     Index listing

                   

                  TABLE_NAME                          UNQ INDEX_NAME                     COLUMN_NAME        FuncIxd TABLESPACE_NAME

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

                  SYSADM.PS_PHYSICAL_INV              NON PSGPHYSICAL_INV                INV_LOT_ID                 PSINDEX

                                                                                         SERIAL_ID                  PSINDEX

                                                                                         STORAGE_AREA               PSINDEX

                                                                                         STOR_LEVEL_1               PSINDEX

                                                                                         STOR_LEVEL_2               PSINDEX

                                                                                         STOR_LEVEL_3               PSINDEX

                                                                                         STOR_LEVEL_4               PSINDEX

                                                                                         CONTAINER_ID               PSINDEX

                                                                                         UNIT_OF_MEASURE            PSINDEX

                   

                                                      UNI PS_PHYSICAL_INV                BUSINESS_UNIT              PSINDEX

                                                                                         STAGED_DATE                PSINDEX

                                                                                         INV_LOT_ID                 PSINDEX

                                                                                         SERIAL_ID                  PSINDEX

                                                                                         INV_ITEM_ID                PSINDEX

                                                                                         STORAGE_AREA               PSINDEX

                                                                                         STOR_LEVEL_1               PSINDEX

                                                                                         STOR_LEVEL_2               PSINDEX

                                                                                         STOR_LEVEL_3               PSINDEX

                                                                                         STOR_LEVEL_4               PSINDEX

                                                                                         CONTAINER_ID               PSINDEX

                                                                                         UNIT_OF_MEASURE            PSINDEX

                   

                   

                   

                  57 rows selected.

                   

                  09:07:41 SYSADM/fssup/FSSUP>

                  • 6. Re: need to improve sql performance
                    Hoek

                    You can the value change cursor_sharing on session level.

                    Using FORCE will give you a 'one-size-fits-all' execution plan, which could be totally wrong for your query/data.

                    I'd test this first, especially on an 11.2 database. The recommendation from application side is nowadays usually the wrong way to go, it's a recommendation from the previous century.

                     

                    Ask Tom: On Sharing, Splitting, and Deleting

                    • 7. Re: need to improve sql performance
                      Jitendra-OC

                      0.27 seconds.