EGL9.2: Allocation Process(FS_ALLC) Performance Issue On Step: FS_ALLC_CLOG.uAdjAP.uAcctPerD (Doc ID 2281097.1)

Last updated on MAY 26, 2020

Applies to:

Symptoms

ACTUAL BEHAVIOR
---------------
FS_ALLC app engine running for long duration on step FS_ALLC_CLOG.uAdjAP.uAcctPerD

There is a "Do Select" step in the "FS_ALLC_CLOG.uAdjAP.uAcctPerD" app engine section that pulls ACCOUNTING_PERIOD values for each row in the PS_ALC_CLOG_TAO4 temp table for a specific PROCESS_INSTANCE value. In the SQL action of that same app engine section it then does an update for each row setting the ACCOUNTING_PERIOD value to 996 or some other value where the PROCESS_INSTANCE equals the current process_instance and the ACCOUNTING_PERIOD equals the accounting_period from the "Do Select". In a customer scenario where there are 892,438 rows in the PS_ALC_CLOG_TAO4 temp table for a particular process isntance that ALL have the same accounting_period value of 12. This is causing the update SQL action of this app engine section to be executed 892,438 times(once for each row) where if the 'Do Select" step used "SELECT DISTINCT ACCOUNTING_PERIOD ..." it would only loop 1 time and execute the update SQL action once as well. ** This is delivered code in this app engine section.

EXPECTED BEHAVIOR
-----------------------
FS_ALLC program should not run for over 20+ hours

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1.set up Allocation Step(s)
2.set up Allocation Group
3.run the FS_ALLC process using the above Allocation Group

Cause

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