E-AE/MSSQL: Massive Parallel Execution of App Engine Processes May Cause Blocking on Temp Table Allocation Phase on Non-Oracle Database Platform (Doc ID 2804710.1)

Last updated on NOVEMBER 20, 2023

Applies to:

Symptoms

Sample environment is on PeopleTools (PT) 8.54.34/MS SQL server platform.

With MAX allowed AE processes=70 in the PSNT process scheduler configuration file (psprcs.cfg), launching many Application Engine (AE) processes simultaneously causes session blocking. The result is that some AE processes stay in a New status, while others remain in a Processing status for a long time due to the PS_AELOCMGR update being blocked.

Replication steps

-------------

1. Create an Application Designer project that creates three AE (Application Engine) programs, and two temp tables.

2. Build the temp tables, and add the two AE process definitions shown below.

AETEST1

AETEST2

3. Run AETEST2 (navigation: PeopleTools > Process Scheduler > Sample Processes, and enter the Run Control ID).

4. As soon as AETEST1 process shows up in the Process Monitor, quickly do the following:

5. Switch to SQL Studio, refresh the 'Top Transaction by blocked transaction count' report frequently, and it will show the problematic blocking SQL (for example, blocked by the DELETE SQL...).

Changes

Cause

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