Slow Performance or Timeout for Supplier User at View Order Details for Purchase Order or Blanket in iSupplier Portal (Doc ID 1538192.1)

Last updated on JUNE 18, 2022

Applies to:

Symptoms

External Supplier users get slow performance in the iSupplier Portal Home Page. The following query shows slow performance in the trace.  Performance is slow for Standard Purchase Orders as well as Blankets.  Performance is slow for Orders with small, medium, and large number of lines.

SELECT * FROM (SELECT
  'PO' PO_RELEASE_FLAG,
  POH.APPROVED_DATE ,
  POH.COMMENTS ,
  POH.PO_HEADER_ID,
  POH.VENDOR_CONTACT_ID,
  POH.VENDOR_ID,
  POH.VENDOR_SITE_ID,
  POH.SEGMENT1 PO_NUMBER,
  TO_NUMBER(NULL) RELEASE_NUM,
  NULL RELEASE_TYPE,
  TO_NUMBER(NULL) PO_RELEASE_ID,
  POH.CREATION_DATE,
  POH.AGENT_ID,
  POH.ORG_ID
FROM
  PO_HEADERS_ARCHIVE_ALL POH, PO_HEADERS_ALL PH
WHERE
  POH.TYPE_LOOKUP_CODE IN ('BLANKET','CONTRACT','PLANNED','STANDARD') AND
  NVL(POH.CLOSED_CODE,'OPEN') not in ('CLOSED','FINALLY CLOSED') AND
  POH.LATEST_EXTERNAL_FLAG = 'Y' AND
  POH.PO_HEADER_ID = PH.PO_HEADER_ID AND
  POH.APPROVED_DATE > sysdate - 180
UNION ALL
 SELECT /*+ USE_NL(POR POH) */
  'RELEASE' PO_RELEASE_FLAG,
  POR.APPROVED_DATE ,
  POH.COMMENTS ,
  POR.PO_HEADER_ID,
  POH.VENDOR_CONTACT_ID,
  POH.VENDOR_ID,
  POH.VENDOR_SITE_ID,
  POH.SEGMENT1||'-'||POR.RELEASE_NUM PO_NUMBER,
  POR.RELEASE_NUM,
  POR.RELEASE_TYPE,
  POR.PO_RELEASE_ID,
  POR.CREATION_DATE,
  POR.AGENT_ID,
  POR.ORG_ID
 FROM
  PO_RELEASES_ARCHIVE_ALL POR, PO_RELEASES_ALL PR,
  PO_HEADERS_ARCHIVE_ALL POH
 WHERE POH.PO_HEADER_ID = POR.PO_HEADER_ID AND
  POH.TYPE_LOOKUP_CODE IN ( 'BLANKET','PLANNED') AND
  NVL(POR.CLOSED_CODE,'OPEN') NOT IN ('CLOSED','FINALLY CLOSED') AND
  POH.LATEST_EXTERNAL_FLAG = 'Y' AND
  POR.LATEST_EXTERNAL_FLAG = 'Y' AND
  POR.PO_RELEASE_ID = PR.PO_RELEASE_ID AND
  POR.APPROVED_DATE > sysdate - 180) QRSLT WHERE (VENDOR_ID in (:1)) ORDER BY approved_date desc

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 1 2.04 82.88 12211 48357 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2.05 82.89 12211 48357 0 6


STEPS TO REPRODUCE
-------------------
1. Navigate to the external url for iSupplier Portal as a supplier user
2. Login
3. Navigate to iSupplier Portal Home Page
- see slow performance and long delay in displaying the home page
4.  Click an Order to view order details and see very slow peformance

IMPACT
Sometimes it runs in seconds. Sometimes it takes 5 minutes or even gives timeout error.

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.