Performance Issue Seen On Price Update Process
(Doc ID 2700381.1)
Last updated on AUGUST 12, 2020
Applies to:
Oracle Advanced Pricing - Version 12.1.3 and laterInformation in this document applies to any platform.
Symptoms
On : 12.1.3 version, Reports & Concurrrent Requests
ACTUAL BEHAVIOR
---------------
Customer is experiencing performance when the business revising the item price, in recent days, we have started witnessing that overall system performance going down and jobs related to price update and/or reflecting the new price across various contracts are taking too longer duration to complete, and at the same time, it's creating bottleneck for other jobs which deal with price/contract tables.
This is happening primarily when business perform mass price updates (for month-end / for new Qtr / vendor price change for more items).
The mass price updates are happening through custom job "Edgepark Price List to SSD Update" which invokes OZF_SD_REQUEST_PUB API (procedure: update_sd_request)
While this job is running, DBA observed that below SQL is stuck for min. 30+ mins for each request
“INSERT INTO QP_LIST_HEADER_PHASES (LIST_HEADER_ID, PRICING_PHASE_ID,PRIC_PROD_ATTR_ONLY_FLAG) ( SELECT DISTINCT LIST_HEADER_ID , PRICING_PHASE_ID,'N' FROM QP_LIST_LINES WHERE PRICING_PHASE_ID > 1 AND QUALIFICATION_IND IN (2,6,8,10,12,14,22,28,30) AND LIST_HEADER_ID = :B1 )” – SQLID: 75x78njcfgc78.
EXPECTED BEHAVIOR
-----------------------
Expect no performance issues
STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Invoke the custom process to mass update prices
2. See performance issue
BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot effieciently or effectively update prices.
Changes
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 |
Changes |
Cause |
Solution |
References |