Performance Issue Seen On Price Update Process (Doc ID 2700381.1)

Last updated on AUGUST 12, 2020

Applies to:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.