R12: Manage Buyer Workload Performance Issue. (Doc ID 2264923.1)

Last updated on MAY 11, 2017

Applies to:

Oracle Purchasing - Version 12.2.5 and later
Information in this document applies to any platform.
POXRQARQ.fmb

Symptoms

On : 12.2.5 version, Requisitions

When querying requisitions in Manage Buyer Workload the performance is extremely slow.
Once the requisition lines are finally displayed and selected for assignment to a Buyer and the record is saved the application just spins and never completes.
After killing the session and querying the requisition lines that were assigned show as assigned.

Here is the most the expensive query:

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  2      0.48     0.51          0          78            0            0
Fetch      2    92.30  4991.68   4487041 4528100      0            1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total      5     92.79  4992.20  4487041 4528178       0            1



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

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