My Oracle Support Banner

Poor performance In Install Base Error Correction And Synchronization Program Running In Excess Of 26 Hours (Doc ID 1505392.1)

Last updated on JULY 11, 2021

Applies to:

Oracle Installed Base - Version 11.5.10.2 and later
Information in this document applies to any platform.

Symptoms

On : 11.5.10.2 version, Support Codes

ACTUAL BEHAVIOR
---------------
Poor performance of Correction and Synchronization program and the query taking long time is as following

SELECT M_MSI.CONCATENATED_SEGMENTS, M_MSI.COMMS_NL_TRACKABLE_FLAG, C_MSI.COMMS_NL_TRACKABLE_FLAG, M_MP.ORGANIZATION_CODE, C_MP.ORGANIZATION_CODE FROM MTL_SYSTEM_ITEMS_B C_MSI, MTL_PARAMETERS C_MP, MTL_SYSTEM_ITEMS_KFV M_MSI, MTL_PARAMETERS M_MP WHERE M_MP.ORGANIZATION_ID = M_MP.MASTER_ORGANIZATION_ID AND M_MSI.ORGANIZATION_ID = M_MP.ORGANIZATION_ID AND M_MSI.ORGANIZATION_ID = C_MP.MASTER_ORGANIZATION_ID AND C_MP.MASTER_ORGANIZATION_ID = M_MP.ORGANIZATION_ID AND C_MSI.ORGANIZATION_ID = C_MP.ORGANIZATION_ID AND M_MSI.ORGANIZATION_ID <> C_MP.ORGANIZATION_ID AND C_MSI.ORGANIZATION_ID <> M_MSI.ORGANIZATION_ID AND C_MSI.INVENTORY_ITEM_ID = M_MSI.INVENTORY_ITEM_ID AND NVL(M_MSI.COMMS_NL_TRACKABLE_FLAG, 'N') <> NVL(C_MSI.COMMS_NL_TRACKABLE_FLAG, 'N') AND EXISTS (SELECT 1 FROM HR_ALL_ORGANIZATION_UNITS HAOU WHERE DATE_TO IS NULL AND HAOU.ORGANIZATION_ID =M_MSI.ORGANIZATION_ID) ORDER BY M_MSI.INVENTORY_ITEM_ID


EXPECTED BEHAVIOR
-----------------------
Expect better performance

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. De-activate SFM Event manager queue services
2. run Correction and Synchronization program in S mode and from AWR_Report.pdf show the Elapsed Time of 98900

Changes

 

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


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