My Oracle Support Banner

Product Workbench Item Search Performance Issue After Upgrade To 12.2.8 (Doc ID 2565564.1)

Last updated on JANUARY 07, 2020

Applies to:

Oracle Product Workbench - Version 12.2.8 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.8 version, Setup Issues

ACTUAL BEHAVIOR

=================

After upgrade to 12.2.8 , product workbench item search takes 2-3 mins to just list the item

Performance is fine in forms and the product hub pages.


EXPECTED BEHAVIOR
===============
Should return data in 2-3 seconds

STEPS
=====
The issue can be reproduced at will with the following steps:

Product Hub Responsibility  -> Launch Product Workbench
Select Organization
Enter Item
click on first search icon

Below SQL was the most expensive

 

SELECT *
FROM
( SELECT  I.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID_B, I.STYLE_ITEM_ID AS
 STYLE_ITEM_ID, I.STYLE_ITEM_FLAG AS STYLE_ITEM_FLAG_CODE, I.ORGANIZATION_ID
 AS ORGANIZATION_ID_B, :1 AS ORGANIZATION_CODE, I.ITEM_CATALOG_GROUP_ID AS
 ITEM_CATALOG_GROUP_ID, IR.REVISION_ID AS REVISION_ID, IR.REVISION AS
 REVISION, I.APPROVAL_STATUS AS APPROVAL_STATUS_CODE, null AS TEMPLATE_NAME,
 (SELECT LCP.NAME  FROM PA_PROJ_ELEMENTS LCP WHERE LCP.PROJ_ELEMENT_ID =
 I.CURRENT_PHASE_ID AND LCP.PARENT_STRUCTURE_ID = I.LIFECYCLE_ID AND
 LCP.PROJECT_ID = 0 AND LCP.OBJECT_TYPE = 'PA_TASKS' )  AS CURRENT_PHASE_ID,
 (SELECT ITL.DESCRIPTION  FROM MTL_SYSTEM_ITEMS_TL ITL WHERE
 ITL.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID AND ITL.ORGANIZATION_ID =
 I.ORGANIZATION_ID AND ITL.LANGUAGE = USERENV('LANG') )  AS DESCRIPTION,
 (SELECT mtl_item_status.INVENTORY_ITEM_STATUS_CODE_TL  FROM mtl_item_status
  WHERE mtl_item_status.INVENTORY_ITEM_STATUS_CODE =
 I.INVENTORY_ITEM_STATUS_CODE) AS INVENTORY_ITEM_STATUS_CODE, I.SEGMENT1 AS
 ITEM_NUMBER, I.SEGMENT1, (SELECT LC.NAME  FROM PA_PROJ_ELEMENTS LC WHERE
 LC.PROJ_ELEMENT_ID = I.LIFECYCLE_ID AND LC.PROJECT_ID = 0 AND
 LC.OBJECT_TYPE = 'PA_STRUCTURES' )  AS LIFECYCLE_ID, (SELECT
 ITL.LONG_DESCRIPTION  FROM MTL_SYSTEM_ITEMS_TL ITL WHERE
 ITL.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID AND ITL.ORGANIZATION_ID =
 I.ORGANIZATION_ID AND ITL.LANGUAGE = USERENV('LANG') )  AS LONG_DESCRIPTION,
  (SELECT ICGKFV.CONCATENATED_SEGMENTS  FROM MTL_ITEM_CATALOG_GROUPS_B_KFV
 ICGKFV WHERE ICGKFV.ITEM_CATALOG_GROUP_ID = I. ITEM_CATALOG_GROUP_ID )  AS
 CATALOG_GROUP, IR.REVISION_LABEL AS REVISION_LABEL FROM MTL_SYSTEM_ITEMS_B
 I, EGO_ITEM_TEXT_TL IM, MTL_ITEM_REVISIONS_B IR WHERE I.INVENTORY_ITEM_ID =
 IM.ITEM_ID AND I.ORGANIZATION_ID = IM.ORG_ID AND IM.LANGUAGE =
 USERENV('LANG')  AND ( ( (((CONTAINS(IM.TEXT,:3,1)>0))) ) )  AND
 I.ORGANIZATION_ID = :3 AND IR.ORGANIZATION_ID=I.ORGANIZATION_ID AND
 IR.INVENTORY_ITEM_ID=I.INVENTORY_ITEM_ID AND IR.REVISION_ID IN ( SELECT /*+
 no_unnest push_subq index(ir2,MTL_ITEM_REVISIONS_B_N1) */
 MAX(IR2.REVISION_ID)  KEEP (DENSE_RANK LAST ORDER BY IR2.EFFECTIVITY_DATE )
  FROM MTL_ITEM_REVISIONS_B IR2 WHERE IR2.ORGANIZATION_ID=I.ORGANIZATION_ID
 AND IR2.INVENTORY_ITEM_ID=I.INVENTORY_ITEM_ID AND IR2.EFFECTIVITY_DATE <=  
 SYSDATE AND IR2.IMPLEMENTATION_DATE IS NOT NULL ) AND 1 = 1  )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          0          0           0
Execute      1      0.02       0.02          0        190          0           0
Fetch        2     22.40     130.59     183468    1414562          0          38
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     22.44     130.63     183468    1414752          0          38

 

Changes

 Recent upgrade to 12.2.8

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
Changes
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.