Performance Issue with Ship Confirm in Shipping Transactions Form
(Doc ID 2175257.1)
Last updated on AUGUST 22, 2022
Applies to:
Oracle Shipping Execution - Version 12.2.4 and laterInformation 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.
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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |
| References |