R12: Performance Issue With Manage Buyer Workload Form
(Doc ID 2027537.1)
Last updated on APRIL 20, 2023
Applies to:
Oracle Purchasing - Version 12.1.3 and laterInformation in this document applies to any platform.
Symptoms
Performance issue with "Manage Buyer Workload" Form
Trace file shows the following SQL as the most expensive query:
SQL ID: az2zk7b87ybk6 Plan Hash: 3341979274
SELECT /*+ FIRST_ROWS LEADING (PO_REQUISITION_LINES_INQ_V.PRL) */ ORG_ID,
ITEM_REVISION,ITEM_DESCRIPTION,QUANTITY,UNIT_MEAS_LOOKUP_CODE,UNIT_PRICE,
NEED_BY_DATE,REQUISITION_NUM,LINE_NUM,LINE_TYPE,NOTE_TO_AGENT,
SUGGESTED_VENDOR_NAME,SUGGESTED_VENDOR_LOCATION,DELIVER_TO_LOCATION,
RATE_TYPE,ROW_ID,TO_PERSON_ID,LINE_TYPE_ID,DELIVER_TO_LOCATION_ID,
SUGGESTED_BUYER_ID,ITEM_ID,CURRENCY_CODE,SOURCE_TYPE_CODE,
DESTINATION_TYPE_CODE,CATEGORY_ID,MODIFIED_BY_AGENT_FLAG,URGENT,
ENCUMBERED_FLAG,RFQ_REQUIRED_FLAG,ON_RFQ_FLAG,ASSIGNMENT_END_DATE,
ASSIGNMENT_START_DATE,CANDIDATE_FIRST_NAME,CANDIDATE_LAST_NAME,
REQUISITION_HEADER_ID,REQUISITION_LINE_ID,JOB_ID,AMOUNT
FROM
PO_REQUISITION_LINES_INQ_V WHERE nvl(line_location_id , -999) = -999 AND
nvl(cancel_flag,'N')='N' AND nvl(closed_code,'OPEN')!='FINALLY CLOSED' AND
nvl(modified_by_agent_flag,'N')='N' AND source_type_code = 'VENDOR' AND (:1
IS NULL OR (nvl(currency_code, :2) = :3)) AND -1=-1 and (
suggested_buyer_id IS NULL AND nvl(suggested_buyer_id,-999) = -999 ) and (
:4 = authorization_status ) and (ORG_ID=:5) order by ITEM_REVISION DESC,
ITEM_DESCRIPTION,QUANTITY
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.47 0.45 0 0 0
0
Fetch 1 6.42 343.29 73777 79996 0
20
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 3 6.89 343.75 73777 79996 0
20
SELECT /*+ FIRST_ROWS LEADING (PO_REQUISITION_LINES_INQ_V.PRL) */ ORG_ID,
ITEM_REVISION,ITEM_DESCRIPTION,QUANTITY,UNIT_MEAS_LOOKUP_CODE,UNIT_PRICE,
NEED_BY_DATE,REQUISITION_NUM,LINE_NUM,LINE_TYPE,NOTE_TO_AGENT,
SUGGESTED_VENDOR_NAME,SUGGESTED_VENDOR_LOCATION,DELIVER_TO_LOCATION,
RATE_TYPE,ROW_ID,TO_PERSON_ID,LINE_TYPE_ID,DELIVER_TO_LOCATION_ID,
SUGGESTED_BUYER_ID,ITEM_ID,CURRENCY_CODE,SOURCE_TYPE_CODE,
DESTINATION_TYPE_CODE,CATEGORY_ID,MODIFIED_BY_AGENT_FLAG,URGENT,
ENCUMBERED_FLAG,RFQ_REQUIRED_FLAG,ON_RFQ_FLAG,ASSIGNMENT_END_DATE,
ASSIGNMENT_START_DATE,CANDIDATE_FIRST_NAME,CANDIDATE_LAST_NAME,
REQUISITION_HEADER_ID,REQUISITION_LINE_ID,JOB_ID,AMOUNT
FROM
PO_REQUISITION_LINES_INQ_V WHERE nvl(line_location_id , -999) = -999 AND
nvl(cancel_flag,'N')='N' AND nvl(closed_code,'OPEN')!='FINALLY CLOSED' AND
nvl(modified_by_agent_flag,'N')='N' AND source_type_code = 'VENDOR' AND (:1
IS NULL OR (nvl(currency_code, :2) = :3)) AND -1=-1 and (
suggested_buyer_id IS NULL AND nvl(suggested_buyer_id,-999) = -999 ) and (
:4 = authorization_status ) and (ORG_ID=:5) order by ITEM_REVISION DESC,
ITEM_DESCRIPTION,QUANTITY
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.47 0.45 0 0 0
0
Fetch 1 6.42 343.29 73777 79996 0
20
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 3 6.89 343.75 73777 79996 0
20
Steps To Reproduce:
1. Use Purchasing Responsibility .
2. Create and Approve the Requisition.
3. Navigate to Manage Buyer Workload form.
4. Enter the Requisition Number created above.
5. Click on Find Button. The query takes a very long time.
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 |