EGL: Online Temporary Table Allocation Not Working As Expected (Doc ID 648770.1)

Last updated on APRIL 04, 2025

Applies to:

Symptoms

When Temp Table Instances (Total) = 10 and Temp Table Instances (Online) = 10, PS_AEONLINEINST is locking after the first online AE is executed.  Only able to run 1 online AE at a time.  

Online Instance Usage page only shows lock issued to the first temp table instance.  It is behaving as if the Online Temp Table Instance count is set to 1.

Online temp table allocation works as expected if the Online Temp Table Instance count  = 9 (or fewer).  We are able to run up to 9 instances of the same online AE program.  Online Instance Usage page shows locks issued on temp table instances 1 thru 9.

Test Case:
-Set Temp Table Instances (Total) = 10 and Temp Table Instances (Online) = 10 on PeopleTools Options page.
-Created a simple AE program with 1 step/PeopleCode action.  

    For &I = 1 To 1000000 Step 0.1;
  
End-For;

-Assigned a Temp Table to this AE program.
-Rebuid the Temp Table and verified that all 10 online temp table instances were built.

-In AppDesigner, open page AE_Request.  Right-Click on the "Test CallAppEngine" button and select View Page PeopleCode.  Comment out the page peoplecode.  This will reveal the button on the browser page.  Go to PeopleTools, Application Engine, Request AE to verify that the button is visible on the run control page.

-Go back to the AE_REQUEST page in AppDesigner.  Right-Click on the "Test CallAppEngine" button and select View Record PeopleCode.  Click on the right-hand drop down box and add a FieldChange peoplecode event.  Add the following CallAppEngine code to the FieldChange Record PeopleCode

CallAppEngine("<AEPROGNAME>");
Warning (MsgGet(108, 2, " "));

-In the browser, go to the Request AE page.  PeopleTools, Application Engine, Request AE.  Create a run control.
Once you are on the run control page, click on the New Window link in the right-hand corner of the PIA page.  Open between 5 and 10 new windows, and make sure all of them are on the run control page showing the Test CallAppEngine button.
-Click on the Test CallAppEngine button in each of the open browsers to run multiple Online AE programs at the same time.  Try to submitt them as close to the same time as possible.
-Once all the programs finish, go to the Online Instance Usage page to review the locks that were issued.  PeopleTools, Application Engine, Review Online Instance Usage.  You should see that the locks were only issued on the first temp table instance.

-Redo this same test with 9 Online Temp Table instances and you will see a more dispersed set of locks issued.

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.