My Oracle Support Banner

Performance Issue in CLM iProcurement When Searching Document Catalog by Description (Doc ID 2614438.1)

Last updated on DECEMBER 05, 2019

Applies to:

Oracle Contract Lifecycle Management for Public Sector - Version 12.2.6 and later
Information in this document applies to any platform.

Symptoms

In Oracle iProcurement and Oracle Application Object Library, with Oracle Contract Lifecycle Management (CLM) enabled, Release 12.2.6, there is a performance issue when searching the attachment Document Catalog by a specific Description.

Due to this issue, users cannot efficiently search the document catalog by description for known attachments.


The trace file shows the following statement having the performance problem:

SELECT * FROM (SELECT FD.DOCUMENT_ID,
  FD.DATATYPE_ID,
  FD.DATATYPE_NAME,
  FD.DESCRIPTION,
  FD.FILE_NAME,
  FD.MEDIA_ID,
  FD.STORAGE_TYPE,
  FD.CATEGORY_ID,
  FD.SECURITY_TYPE,
  FD.SECURITY_ID,
  FD.IMAGE_TYPE,
  FD.USAGE_TYPE,
  FD.START_DATE_ACTIVE,
  FD.END_DATE_ACTIVE,
  FD.REQUEST_ID,
  FD.PROGRAM_APPLICATION_ID,
  FD.PROGRAM_ID,
  FD.LAST_UPDATE_DATE,
  FD.CREATED_BY,
  FD.CREATION_DATE,
  FD.LAST_UPDATED_BY,
  u.USER_NAME LAST_UPDATED_BY_NAME,
  u.USER_ID AS UPDATED_BY_USER_ID,
  c.USER_NAME AS CREATED_BY_USER_NAME,
  c.USER_ID AS CREATED_BY_USER_ID,
  FD.USAGE_TYPE_DESCR,
  FD.CATEGORY_DESCRIPTION,
  FD.PUBLISH_FLAG,
  decode(FD.datatype_id, 5, nvl(FD.title,FD.description)||'('||substr(FD.URL, 1, least(length(FD.URL),15))||'...)',
decode(FD.datatype_id, 6, nvl(FD.title, FD.file_name),
decode(FD.TITLE, null, (select message_text from fnd_new_messages where message_name = 'FND_UNDEFINED' and application_id = 0 and language_code = userenv('LANG')), FD.TITLE))) FILE_NAME_SORT,
  FD.URL,
  FD.TITLE
FROM FND_DOCUMENTS_VL FD, FND_USER u, FND_USER c
WHERE FD.last_updated_by = u.user_id(+) AND FD.created_by = c.user_id(+)) QRSLT WHERE ((upper(DESCRIPTION) LIKE :0) AND upper(LAST_UPDATED_BY_NAME) LIKE :1 AND LAST_UPDATE_DATE like :2 AND upper(FILE_NAME_SORT) like :3 AND upper(USAGE_TYPE) like :4 AND USAGE_TYPE <> 'O' AND datatype_id like :5 AND NVL(START_DATE_ACTIVE, SYSDATE-1) <= SYSDATE AND NVL(END_DATE_ACTIVE, SYSDATE+1) >= SYSDATE AND CATEGORY_ID like :6 AND DATATYPE_ID in (1, 2, 5, 6) AND (SECURITY_TYPE=4 OR PUBLISH_FLAG='Y') AND CATEGORY_ID IN (1, :7, :8, :9, :10, :11, :12))

call     count      cpu    elapsed       disk      query    current      rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        2     0.00       0.00          0          0          0          0
Execute      2     0.21       0.21          0          0          0          0
Fetch       33     4.57      74.17      22055    1317770          0         63
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total       37     4.80      74.39      22055    1317770          0         63




Steps To Reproduce

The issue can be reproduced at will with the following steps:
1. Log in to Oracle Applications, using a CLM iProcurement responsibility.
2. Shop tab > Non-Catalog Request subtab.
3. Add a non-catalog item to cart.
4. View Cart and Checkout.
5. On the Checkout: Requisition Information page, scroll down to the Attachments region and click the Add Attachment button.
6. In the Add Attachment pop-up window, select Attachment Type = Document Catalog.
7. Still in the Add Attachment pop-up window, enter the "Description" search field with the EXACT value of the description for a known attachment.
8. Click the Go button. It take more than one minute to return the exact document in the search results.



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


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.