ETL9.2: Batch Time Admin Running Long At Step TL_SCHRES_AE.JA000. (Doc ID 1918600.1)

Last updated on SEPTEMBER 14, 2015

Applies to:

PeopleSoft Enterprise HRMS Time and Labor - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

Customer is running TA in 9.2 and experiencing severe performance issue on above steps. Their major performance issue is on schedule step. On every ta run this message is issued (17.34.06 ......(TL_SCHRES_AE.AA000.Step180) (Do
Fetch) 17.34.06 ......(TL_SCHRES_AE.AA000.Step180) (Log Message)If payable time is not created verify schedule set-up for Employee: 00658M, 0 (13502,109) for every employee.Their schedule setup was reviewed and it looked fine. Online
instance of temp table is set to blank and fileds are grayed out but for batch one the instance count of 78 is set up on app engine properties (please refer -Properties_tab.docx in attached zip file).  They provided awr reported and sqlts for and  tools db engineer made some index recommendations along with other db related parameter changes etc, it is documented in file GSCDBenginerrecommendations.txt in attached zip file. However those did not help much.

As per customer -We have seen some marked improvement in the performance of the problem step by adding an additional record (PS_SCH_GOUP_TBL)  to the sql steps¿ join (TL_SCHRES_AE.JA000.Step010, TL_SCHRES_AE.JA000.Step020,
TL_SCHRES_AE.JA000.Step030,TL_SCHRES_AE.JA000.Step040) and we are currently testing the performance associated with more than one concurrent job run. The updates sqls are in attached zip file. We would want to avoid any customization and need developments assistance with this

 REPLICATION STEPS
=================
TA setup and run schedule:  We have batch time admin split into 46 threads .9 threads are scheduled to run parallel at a time. We also have Real time processing and batch time admin both running(Though for different employees).Each thread has 10000 employees in them.we have 180k employees to process.We are using exadata. Each thread is taking more then 6 hours to complete.

We have downloaded the sqlt tool and then generated the sqlt's for the sqls that are running long.There are two sqls that were running long.we did two test.1) Running single time admin thread with 10k employees. 2) Running two
threads parallel with 10k employees in each.

Cause

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