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.
REM tr_moreinst.sql
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
/
Processes Unable to Allocate Non-Shared Temporary Record Last 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 ...
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.
3 comments :
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.
John
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?
Yes. Non-shared instances of temporary tables are truncated when an Application Engine program allocates them to its process instance.
Post a Comment