Performance Issues With Searches/Reports (Doc ID 2131703.1)

Last updated on AUGUST 21, 2016

Applies to:

Oracle Agile PLM Framework - Version 9.3.1.2 and later
Information in this document applies to any platform.

Symptoms

ACTUAL BEHAVIOR
---------------
Performance issue -search query is taking up to 5x longer to run

EXPECTED BEHAVIOR
-----------------------
Search query should only be taking 5 seconds to run

ADDITIONAL INFO
------------------------------
Query used to take only 5 seconds, but it is fluctuating between the normal 20 to 40 seconds.


STEPS
-----------------------
Query :

select /*+ ALL_ROWS */ ITEM_P2P3_QUERY.ID,ITEM_P2P3_QUERY.CLASS,ITEM_P2P3_QUERY.SUBCLASS,ITEM_P2P3_QUERY.FLAGS,ITEM_P2P3_QUERY.REV_FLAGS,NULL,NULL,ITEM_P2P3_QUERY.ITEM_NUMBER,ITEM_P2P3_QUERY.ITEM_NUMBER,ITEM_P2P3_QUERY.DESCRIPTION,ITEM_P2P3_QUERY.DESC_REV,ITEM_P2P3_QUERY.RELEASE_TYPE,ITEM_P2P3_QUERY.REV_NUMBER,ITEM_P2P3_QUERY.LATEST_RELEASED_ECO,ITEM_P2P3_QUERY.COMPLIANCY,ITEM_P2P3_QUERY.SUBCLASS,ITEM_P2P3_QUERY.MULTILIST10,ITEM_P2P3_QUERY.SUBCLASS,ITEM_P2P3_QUERY.MULTILIST49,ITEM_P2P3_QUERY.PRODUCT_LINES,ITEM_P2P3_QUERY.MULTILIST02,ITEM_P2P3_QUERY.RELEASE_TYPE,ITEM_P2P3_QUERY.CREATE_USER,ITEM_P2P3_QUERY.RELEASE_DATE,ITEM_P2P3_QUERY.CREATE_USER,NULL,NULL,ITEM_P2P3_QUERY.ITEM_NUMBER from ITEM_P2P3_QUERY where ((ITEM_P2P3_QUERY.TEXT22 = 'H9J7') OR (ITEM_P2P3_QUERY.TEXT21 = 'H9J7')) AND ITEM_P2P3_QUERY.CLASS = 10000 AND (NVL(ITEM_P2P3_QUERY.DELETE_FLAG,0) = 0) ORDER BY 28


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