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.

select  p.message_parm recname, r.prcsname
, count(*) occurances
, max(l.dttm_stamp_sec) last_occurance
, max(p.process_instance) process_instance
from ps_message_log l
, ps_message_logparm p
left outer join psprcsrqst r
on r.prcsinstance = p.process_instance
where l.message_set_nbr = 108
and l.message_nbr = 544
and p.process_instance = l.process_instance
and p.message_seq = l.message_seq
and l.dttm_stamp_sec >= sysdate - 7
group by p.message_parm, r.prcsname
order by 1,2

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.