Performance Issue When Opening the Receiving Page in Oracle iProcurement (Doc ID 2114495.1)

Last updated on MARCH 08, 2017

Applies to:

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

Symptoms

In Oracle iProcurement, Release 12.1.3, a performance issue occurs at times when users attempt to open the Receiving page.
Due to this issue, users are intermittently experiencing delays in opening the main Receiving page in Oracle iProcurement.

When several users start using the iProcurement responsibility, the database node is consuming almost all CPU resources (40 CPU's).
Most of the time this query is running fine and is not causing high CPU usage.

 

SELECT * FROM (SELECT distinct ad.ATTACHED_DOCUMENT_ID,
  ad.DOCUMENT_ID AS DOCUMENT_ID,
  ad.LAST_UPDATE_DATE,
  ad.LAST_UPDATED_BY,
  u.USER_NAME LAST_UPDATED_BY_NAME,
  ad.ENTITY_NAME,
  ad.PK1_VALUE,
  ad.PK2_VALUE,
  ad.PK3_VALUE,
  ad.PK4_VALUE,
  ad.PK5_VALUE,
  node.short_name LOCATION,
  d.DOCUMENT_ID DOCUMENT_ID1,
  d.DATATYPE_ID,
  dd.USER_NAME DATATYPE_NAME,
  dl.DESCRIPTION,
  d.MEDIA_ID,
  d.dm_type,
  d.dm_node,
  d.dm_folder_path,
  e.DATA_OBJECT_CODE,
  e.DOCUMENT_ENTITY_ID,
  'ALLOW_ATTACH_UPDATE' ALLOW_ATTACH_UPDATE,
  'ALLOW_ATTACH_DELETE' ALLOW_ATTACH_DELETE,
  cl.category_id category_id_query,
  cl.user_name attachment_category_name,
  ad.status,
  ad.creation_date,
  u1.user_name ATTACHED_BY_NAME,
  d.security_id,
  d.security_type,
  d.publish_flag
  FROM FND_DOCUMENTS d,
  FND_DOCUMENTS_TL dl,
  FND_DOCUMENT_DATATYPES dd,
  FND_ATTACHED_DOCUMENTS ad,
  FND_DOCUMENT_ENTITIES e,
  FND_USER u,
  FND_USER u1,
  FND_DOCUMENT_CATEGORIES_TL cl,
  FND_DM_NODES node
  WHERE ad.DOCUMENT_ID = d.DOCUMENT_ID
  and ad.ENTITY_NAME = e.DATA_OBJECT_CODE(+)
  and ad.LAST_UPDATED_BY = u.USER_ID(+)
  and ad.CREATED_BY = u1.user_id (+)
  and cl.language = userenv ('LANG')
  and cl.category_id = nvl (ad.category_id, d.category_id)
  and d.document_id = dl.document_id
  and dl.language = userenv ('LANG')
  and d.datatype_id = dd.datatype_id
  and dd.language = userenv ('LANG')
  and nvl (d.dm_node, 0) = node.node_id (+)) QRSLT WHERE ((
  (entity_name = :1 and pk1_value = :2 and category_id_query in
  (1, :3, :4, :5, :6) ) OR (entity_name = :7 and pk1_value = :8
  ) ) and datatype_id in (6,2,1,5) AND (SECURITY_TYPE=4 OR
  PUBLISH_FLAG='Y'))

Bind Variables :
 1 - (VARCHAR2(128)):RCV_HEADERS
 2 - (VARCHAR2(32)):2842598
 3 - (NUMBER):1000474
 4 - (NUMBER):42
 5 - (NUMBER):37
 6 - (NUMBER):35
 7 - (VARCHAR2(128)):PO_VENDORS
 8 - (VARCHAR2(32)):127




Steps To Reproduce

The issue can be reproduced at will with the following steps:
1. Log in to Oracle Applications.
2. Select the iProcurement responsibility > iProcurement Home Page.
3. On the Shop home page, click the Receiving tab. The performance issue occurs at this point.



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