EGL92: FS_ALLC App Engine Performance Issue In SQL In FS_ALLC_CLOGuAdjAP uAcctPerD Section
Last updated on SEPTEMBER 25, 2017
Applies to:PeopleSoft Enterprise FIN General Ledger - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.
FS_ALLC app engine running for long duration in the SQL: FS_ALLC_CLOGuAdjAP uAcctPerD app engine section.
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.
FS_ALLC program should not run for over 20+ hours
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
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