Thursday, February 26, 2009

Do You Need More Temporary Table Instances?

When an Application Engine loads a program prior to execution, it attempts to allocate an instance of each temporary record specified in the program to itself. If the allocation of a table fails because there are no available instances, Application Engine is will use the shared instance (unless the program is configured to abort if non-shared tables cannot be assigned). In this case it will write an entry to the message log to warn that the shared instance of the record has been used.

When processes use the shared tables performance is likely to be degraded by contention on the table. The %TruncateTable() PeopleCode macro generates a DELETE by process instance on the shared table instead of a TRUNCATE command.

The problem is that unless you look in the message log, you will not know that this is happening. However, it easy to write a query that will look at the message log table and report whenever this has occurred.

This report tells you which programs failed to allocated instances of which record, how many times that has happened within the last 7 days.

            Processes Unable to Allocate Non-Shared Temporary Record

Record          Process                  Last                   Process
Name            Name         Occurrences Occurrence            Instance
--------------- ------------ ----------- ------------------- ----------
TL_ABS_WRK      TL_TIMEADMIN           4 08:24:39 01/01/2009      12345
TL_ATTND_HST1   TL_TIMEADMIN          10 08:23:40 01/01/2009      12345
TL_COMP_BAL     TL_TIMEADMIN          11 08:23:40 01/01/2009      12345

NB: Just because an Application Engine could not allocate a non-shared table, does not automatically imply that you need more instances of that record. It could be that other processes had failed, but the temporary tables are still allocated to the process until the process is either restarted and completes successfully, or the process is deleted or cancelled.

You might choose to create some spare instances of records to allow for failed processes, but if you do not clear failed processes you will eventually run out of instances.


Anonymous said...

add "and r.RUNSTATUS = '9'" in order to show only processes that have completed successfully. Or add that cloumn to the result set to get status but you would need to remove the aggregate functions.

Good stuff.


PeopleSoft Wiki said...

When an app engine starts or finishes, it clears out temp tables. I assume this is based on the field PROCESS_INSTANCE, so if you are sharing a temp table instance is it correct to assume that the only data cleared out relates to the process instance being executed?

David Kurtz said...

Yes. Non-shared instances of temporary tables are truncated when an Application Engine program allocates them to its process instance.