R12: Performance Issue With Manage Buyer Workload Form (Doc ID 2027537.1)

Last updated on JUNE 01, 2016

Applies to:

Oracle Purchasing - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

Performance issue with "Manage Buyer Workloadf" 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   

  

 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