My Oracle Support Banner

F1_SYNC_REQ Related Tables Growing Exponentially With Duplicate M1-SyncCrewShiftToHost Records v2.3.0.2 (Doc ID 2469770.1)

Last updated on MARCH 12, 2019

Applies to:

Oracle Real-Time Scheduler - Version 2.3.0.2.0 and later
Information in this document applies to any platform.

Symptoms

We have noticed that some batch threads go into stuck state and remain in 'In Progress' without actually moving forward. This causes an issue where the batch monitors are long running. For the same number of records, batch instances get completed in 3-4 seconds, but in this instance is running for more than 40000 seconds.

Also, we see that the records in F1_SYNC_REQ table are increasing exponentially by the day. The current count is around 28 million and approx 650-750k records are added daily. We have the Sync Request Monitor batch running every 60 seconds and we see it queries on an average 20k records per batch run. However, not all 20k updates are marked for transmission to the host system.

This behaviour has cost us many days of system instability and performance issues. The CPU of the application server increases because of this reaching ~95%

The below SQL (with different bind variables) run every day multiple times. The tables which is queries is too heavy.

SELECT COUNT(*) MSGCOUNT
FROM F1_SYNC_REQ A, F1_BUS_OBJ_STATUS B, F1_BUS_OBJ C
WHERE A.MAINT_OBJ_CD = 'M1-CREWSHIFT' AND A.PK_VALUE1 = '89169107201635' AND A.BUS_OBJ_CD = 'M1-SyncCrewShiftToHost' AND A.BUS_OBJ_CD = C.BUS_OBJ_CD AND C.LIFE_CYCLE_BO_CD = B.BUS_OBJ_CD AND A.BO_STATUS_CD = B.BO_STATUS_CD AND B.BO_STATUS_COND_FLG <> 'F1FL' /* SQL for Zone 'M1-CHKSRQSH' */ /* com.splwg.base.web.dataExplorer.common.DataExplorerSQLExecuter */

Current database table include:

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!


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