Ship Confirm Run As Part Of Request Set Is Displaying Poor Performance

(Doc ID 2303594.1)

Last updated on SEPTEMBER 20, 2017

Applies to:

Oracle Shipping Execution - Version 12.2.5 and later
Information in this document applies to any platform.

Symptoms

On :  12.2.5 version,

ACTUAL BEHAVIOR  
---------------
Ship Confirm run as part of request set is displaying poor performance after patches applied.  

Patches


1)
india loc Patch 24459608:R12.JAI.C

SR 3-14485717701 - Interface Trip Stop completing in WARNING leading to Inventory not getting Interfaced   its lock   - add to bug after applying patch having warning


2) ITS lock -

25665457:R12.WSH.C  containing the following indexes

WSH_TRIP_STOPS_N6 ( batch_id)
WSH_TRIP_STOPS_N5 ( pending_interface_flag)

Following index is newly created as part of patch :
WSH_DELIVERY_DETAILS_N23 ( ship_set_id).
This is same as WSH_DELIVERY_DETAILS_XX1


3) test Fix for invalids  

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

For the AWR File - the Explain Plan for the select Query with issue as identified by customer is

SQL> explain plan for SELECT WND.ORGANIZATION_ID FROM WSH_TRIP_STOPS WTS, WSH_DELIVERY_LEGS WDL, WSH_NEW_DELIVERIES WND WHERE (WTS.STOP_ID = WDL.PICK_UP_STOP_ID OR WTS.STOP_ID = WDL.DROP_OFF_STOP_ID) AND WND.DELIVERY_ID = WDL.DELIVERY_ID AND WDL.DELIVERY_ID = DECODE(:B2 , 'DLVY', :B1 , WDL.DELIVERY_ID) AND WTS.TRIP_ID = DECODE(:B2 , 'TRIP', :B1 , WTS.TRIP_ID) AND WTS.STOP_ID = DECODE(:B2 , 'STOP', :B1 , WTS.STOP_ID) AND ROWNUM = 1;

Explained.

SQL> set linesize 2000 pagesize 2000
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3271199959

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                       |     2 |    80 |   159K  (3)| 00:00:07 |
|*  1 |  COUNT STOPKEY                    |                       |       |       |            |          |
|   2 |   CONCATENATION                   |                       |       |       |            |          |
|*  3 |    FILTER                         |                       |       |       |            |          |
|   4 |     NESTED LOOPS                  |                       |     1 |    40 | 79787   (3)| 00:00:04 |
|   5 |      NESTED LOOPS                 |                       |     1 |    40 | 79787   (3)| 00:00:04 |
|   6 |       NESTED LOOPS                |                       |     1 |    30 | 79785   (3)| 00:00:04 |
|*  7 |        TABLE ACCESS STORAGE FULL  | WSH_DELIVERY_LEGS     |     1 |    18 | 79783   (3)| 00:00:04 |
|*  8 |        TABLE ACCESS BY INDEX ROWID| WSH_TRIP_STOPS        |     1 |    12 |     2   (0)| 00:00:01 |
|*  9 |         INDEX UNIQUE SCAN         | WSH_TRIP_STOPS_U1     |     1 |       |     1   (0)| 00:00:01 |
|* 10 |       INDEX UNIQUE SCAN           | WSH_NEW_DELIVERIES_U1 |     1 |       |     1   (0)| 00:00:01 |
|  11 |      TABLE ACCESS BY INDEX ROWID  | WSH_NEW_DELIVERIES    |     1 |    10 |     2   (0)| 00:00:01 |
|* 12 |    FILTER                         |                       |       |       |            |          |
|  13 |     NESTED LOOPS                  |                       |     1 |    40 | 79787   (3)| 00:00:04 |
|  14 |      NESTED LOOPS                 |                       |     1 |    40 | 79787   (3)| 00:00:04 |
|  15 |       NESTED LOOPS                |                       |     1 |    30 | 79785   (3)| 00:00:04 |
|* 16 |        TABLE ACCESS STORAGE FULL  | WSH_DELIVERY_LEGS     |     1 |    18 | 79783   (3)| 00:00:04 |
|* 17 |        TABLE ACCESS BY INDEX ROWID| WSH_TRIP_STOPS        |     1 |    12 |     2   (0)| 00:00:01 |
|* 18 |         INDEX UNIQUE SCAN         | WSH_TRIP_STOPS_U1     |     1 |       |     1   (0)| 00:00:01 |
|* 19 |       INDEX UNIQUE SCAN           | WSH_NEW_DELIVERIES_U1 |     1 |       |     1   (0)| 00:00:01 |
|  20 |      TABLE ACCESS BY INDEX ROWID  | WSH_NEW_DELIVERIES    |     1 |    10 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   3 - filter(ROWNUM=1)
   7 - storage("WDL"."DELIVERY_ID"=TO_NUMBER(DECODE(:B2,'DLVY',:B1,TO_CHAR("WDL"."DELIVERY_ID"))))
       filter("WDL"."DELIVERY_ID"=TO_NUMBER(DECODE(:B2,'DLVY',:B1,TO_CHAR("WDL"."DELIVERY_ID"))))
   8 - filter("WTS"."TRIP_ID"=TO_NUMBER(DECODE(:B2,'TRIP',:B1,TO_CHAR("WTS"."TRIP_ID"))))
   9 - access("WTS"."STOP_ID"="WDL"."DROP_OFF_STOP_ID")
       filter("WTS"."STOP_ID"=TO_NUMBER(DECODE(:B2,'STOP',:B1,TO_CHAR("WTS"."STOP_ID"))))
  10 - access("WND"."DELIVERY_ID"="WDL"."DELIVERY_ID")
  12 - filter(ROWNUM=1)
  16 - storage("WDL"."DELIVERY_ID"=TO_NUMBER(DECODE(:B2,'DLVY',:B1,TO_CHAR("WDL"."DELIVERY_ID"))))
       filter("WDL"."DELIVERY_ID"=TO_NUMBER(DECODE(:B2,'DLVY',:B1,TO_CHAR("WDL"."DELIVERY_ID"))))
  17 - filter("WTS"."TRIP_ID"=TO_NUMBER(DECODE(:B2,'TRIP',:B1,TO_CHAR("WTS"."TRIP_ID"))))
  18 - access("WTS"."STOP_ID"="WDL"."PICK_UP_STOP_ID")
       filter("WTS"."STOP_ID"=TO_NUMBER(DECODE(:B2,'STOP',:B1,TO_CHAR("WTS"."STOP_ID"))) AND
              LNNVL("WTS"."STOP_ID"="WDL"."DROP_OFF_STOP_ID"))
  19 - access("WND"."DELIVERY_ID"="WDL"."DELIVERY_ID")



STEPS
-----------------------
The issue can be reproduced at will with the following steps:
Ship confirm being run as a request set



BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot efficiently process ship confirm in a timely manner

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms