ETL9.2: Poor Performance with Real Time Admin Rules (RTTA) (Doc ID 1632999.1)

Last updated on JULY 21, 2023

Applies to:

Symptoms

On : 9.2 version, TL Installation and Setup

Doing load testing for 9.2 real time rules feature, 500 users submitting time has significant performance delay. It takes approximately 80 seconds due to prior requests to commit update to installation table:

Real Time Time-Administration is the cause of the issue. The following SQL within Real Time Time-Admin (RTTA is taking the most of the time is

UPDATE PS_TL_INSTALLATION SET LAST_SEQ_PAY_TIME = LAST_SEQ_PAY_TIME + 1

From what we understand the SQL is not committing until the process is complete as a result all other concurrent request remains in the queue. This table has only 1 row with lot of columns so adding an index or collecting a statistics from Database perspective is not going to help to tune the performance. We wanted to check with Oracle Support / T&L Development why the process is designed in such a way. We would like to understand the reason behind the design and would like to understand what are the risk of doing a commit right after the update. So the subsequent / concurrent request are not waiting for the 1st process to complete to update the counter on the field.

Would expect that the performance would be much quicker than 80 seconds for results to be returned. End users will get frustrated with the wait times.

The issue can be reproduced at will with the following steps:
1. report time online with real time rules setup to process upon submit of the timesheet
2. have numerous users all reporting time at the same time or use a load testing program


Due to this issue, they will not be able to implement with the real time rules feature which is one of the reasons they upgraded to 9.2.

Cause

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