The PeopleSoft Tipster Blog

PeopleSoft Timings Records October 14, 2008

Have you ever wanted to retrieve timing information for a process only to find out the the purge has removed all trace from Process Monitor?  Or have you ever wanted more granular information about which parts of an App Engine took the longest?  Or maybe you’re not troubleshooting an individual process but want to establish a baseline for long-term performance gathering on your batch window to identify potential future issues.

There is an easy way to accomplish all of the above as PeopleSoft can (although it doesn’t by default) write some really useful data on processes to database records, all ready to be queried.

There are three records that data is logged to, all keyed by Process Instance.  The records are:

BAT_TIMINGS_LOG – Parent/Header record
BAT_TIMINGS_DTL – Child/Detail record
BAT_TIMINGS_FN – Peoplecode Function details

PS_BAT_TIMINGS_LOG contains standard fare like the Process Name, Oprid and Run Control ID, plus timing information.  In addition to start and end DateTime there is a time elapsed, Time in PeopleCode and Time in SQL.  There are also some trace level flags.  If you’re just after processing times this would be the first stop.

If you’re after more granular data then look into PS_BAT_TIMINGS_DTL.  In this table the timing data is broken down to App Engine Step/Action level (where the Step contains a SQL action) and even within the PeopleCode programs to individual SQL statement level where the PeopleCode actions contain SQLExecs or RowSet code.

I’ve not found a use for the third table (PS_BAT_TIMINGS_FN) yet, but it appears to hold further PeopleCode level timings and execution counts.

Whether these records are populated or not can be controlled at the Process Scheduler level, or on the individual process using the -TRACE flag (either on the command line if running 2-tier or in the appedn flags on the Process Definition if using the Process Scheduler).  The trace value should include a value of 1024 to activate population of these records.

To set this on a Process Scheduler edit the AE Tracing Bitfield section.

; AE Tracing Bitfield
;
; Bit       Type of tracing
; —       —————
; 1         – Trace STEP execution sequence to AET file
; 2         – Trace Application SQL statements to AET file
; 4         – Trace Dedicated Temp Table Allocation to AET file
; 8         – not yet allocated
; 16        – not yet allocated
; 32        – not yet allocated
; 64        – not yet allocated
; 128       – Timings Report to AET file
; 256       – Method/BuiltIn detail instead of summary in AET Timings Report
; 512       – not yet allocated
; 1024      – Timings Report to tables
; 2048      – DB optimizer trace to file
; 4096      – DB optimizer trace to tables
TraceAE=1024

Set the TraceAE value to a value containing 1024 to output the timings to tables.