Performance Issue with Ship Confirm in Shipping Transactions Form (Doc ID 2175257.1)

Last updated on MARCH 08, 2017

Applies to:

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

Symptoms

On : 12.2.4 version, Ship Confirm

When doing ship confirm using the Shipping Transactions Form, the performance is taking too much time for completion.
Have run gather statistics on the HZ tables but still no help. Scheduled gather statistics program is executed every week.

Have identified the problematic sql and it is available in the standard package WSHLOCMB.pls.
There is a difference in this particular sql between 11i and R12. Interestingly the same where clause condition is used twice in the same sql.
This is not related to the form but the package code.

 

TKPROF shows only 1 poorly performing SQL. Most of the 384.89 is from this SQL:

Event waited on                                 Times        Max. Wait   Total Waited
---------------------------------------- Waited ---------- ---------------------
db file sequential read                         73311       0.29          376.66


SELECT PLOC.LOCATION_ID INTERNAL_ORG_LOCATION_ID
FROM
 PO_LOCATION_ASSOCIATIONS_ALL PLOC, HZ_CUST_SITE_USES_ALL SITE_USES,
  HZ_CUST_ACCT_SITES_ALL ACCT_SITES, HZ_PARTY_SITES SITES WHERE
  PLOC.SITE_USE_ID = SITE_USES.SITE_USE_ID AND SITE_USES.CUST_ACCT_SITE_ID =
  ACCT_SITES.CUST_ACCT_SITE_ID AND ACCT_SITES.PARTY_SITE_ID =
  SITES.PARTY_SITE_ID AND PLOC.CUSTOMER_ID = ACCT_SITES.CUST_ACCOUNT_ID AND
  SITES.LOCATION_ID = :B2 AND SITES.LOCATION_ID = :B2 AND PLOC.CUSTOMER_ID =
  NVL(:B1 ,PLOC.CUSTOMER_ID)

call       count   cpu     elapsed    disk         query     current     rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse         1     0.00     0.00      0              0           0                0
Execute      2     0.00     0.00      0             0           0                0
Fetch         2   10.25 384.88  73311   145829       0                0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total          5    10.25 384.89 73311    145829      0                    0

Elapsed times include waiting on following events:

Event waited on                                 Times          Max. Wait Total Waited
---------------------------------------- Waited       ----------      ------------
library cache pin                                   3                  0.00         0.00
library cache lock                                 2                  0.00          0.00
Disk file operations I/O                       790               0.00           0.11
db file sequential read                         73311           0.29         376.66
gc cr grant 2-way                               8889             0.03          1.42
gc current grant 2-way                       5                   0.00           0.00
latch: gcs resource hash                     1                   0.00          0.00
********************************************************************************

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Shipping--Transactions
2. Query for delivery
3. Ship confirm the delivery and performance is poor

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
This is causing severe impact in UAT testing.

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