OracleFree calculatorPDF, Excel, Word exportOracle PeopleSoftAll Oracle PeopleSoft tools

PeopleSoft Application Engine Trace Flag Calculator

This calculator adds up the bit values for Application Engine, SQL and PeopleCode tracing so you can pass the right numbers to psae or put them in psprcs.cfg. It uses the values documented for PeopleTools 8.5x and 8.6x.

Your numbers

AE: step trace (1)

AE: SQL trace (2)

AE: dedicated temp table allocation (4)

AE: temporary table data (8)

Writes a _temptable.DAT file of rows affected by insert, update and delete statements.

AE: statement timings to file (128)

AE: PeopleCode detail timings (256)

AE: statement timings to tables (1024)

PS_BAT_TIMINGS_LOG and PS_BAT_TIMINGS_DTL.

AE: DB optimizer trace to file (2048)

AE: DB optimizer trace to tables (4096)

AE: Integration Broker transform programs (8192)

TraceSQL: statements (1) + variables (2) + connect/commit/rollback (4) + fetch (8)

TraceSQL extras: other API calls (16), set select buffers (32), DB API calls (64), COBOL timings (128)

TracePC preset

Your results

TraceAE / -TRACE value

131

Enter as -TRACE on the psae command line or TraceAE in psprcs.cfg.

TraceSQL / -TOOLSTRACESQL value

TracePC / -TOOLSTRACEPC value

Timings report active (1 = yes)

Optimizer trace active (1 = yes)

Optimizer traces are expensive; use on a test copy.

Bit values follow PeopleBooks (Enabling Application Engine Tracing and the psprcs.cfg Trace section, PeopleTools 8.56 and later). PeopleBooks for 8.56 and later also documents 16384 for SQL timings to PS_AE_TIMINGS_LG and PS_AE_TIMINGS_DT; add it by hand if you need it.

Save results:

Have a slow Application Engine?

Send us an AET timings trace and we will point out the steps and SQL worth tuning first.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Turn on the AE options you need, pick a TraceSQL and TracePC level, and read the three values. A common starting point for a slow program is 1 + 2 + 128 = 131 (step, SQL and timings), which is the example PeopleBooks gives. Add 1024 to keep timings in tables for comparison between runs, and only use the optimizer options on a test copy. On the command line the values go into psae -TRACE 131 -TOOLSTRACESQL 3 -TOOLSTRACEPC 1984. A -TRACE value on the command line or process definition overrides the TraceAE value in the configuration file or Configuration Manager.

Process definition: Override Options, Parameter List Append, add -TRACE 131.
Server wide: TraceAE in the Trace section of psprcs.cfg, then reconfigure the domain.
Two-tier runs: Configuration Manager, Trace tab.

Where the trace files land

Process Scheduler writes each request into a subdirectory of the Log/Output Directory named <process type>_<program>_<process instance>. The AE trace is named AE_<program>_<process instance>_<date/time stamp>.AET, and the SQL and PeopleCode traces go to a .trc file in the same place unless TraceAECombineOutput is set to Y, which merges them into the .AET. The temporary table data option writes a _temptable.DAT file alongside. When an AET file exceeds the AE Trace File Size set in Configuration Manager (default 500 MB), logging switches to a new file, so a long run can leave several files.

Reading results and the AE tables

In the timings report, sort by total time and compare execution counts with rows processed: a step executed once per row usually means a Do Select loop that should be set-based. The program definition you are tracing lives in PSAEAPPLDEFN (program), PSAESECTDEFN (sections), PSAESTEPDEFN (steps) and PSAESTMTDEFN (SQL and PeopleCode actions), which is handy for finding which section a traced statement belongs to. Timings to tables go to PS_BAT_TIMINGS_LOG and PS_BAT_TIMINGS_DTL, keyed by process instance.

TraceSQL 3 shows statements with bind values, usually enough.
TracePC options 1 and 2 are very verbose; prefer the call and parameter options.
Switch tracing off afterwards; step, SQL and PeopleCode tracing add noticeable overhead to run time.

Frequently Asked Questions

What does -TRACE 131 mean in Application Engine?

131 is 1 + 2 + 128: the step trace, the SQL trace and the statement timings report, all written to the AET file. It is the standard first trace for a slow or failing Application Engine program and is the example value used in PeopleBooks.

Where are Application Engine trace files stored?

For scheduled runs, in the request subdirectory under the Process Scheduler Log/Output Directory, named after the process type, program and process instance. You can also open them from Process Monitor, View Log/Trace. For command-line or two-tier runs, they go to the trace directory set in Configuration Manager or the client temp directory.

What is the difference between TraceAE and TraceSQL?

TraceAE controls Application Engine specific output such as steps, AE SQL, timings and optimizer plans in the AET file. TraceSQL is the generic PeopleTools SQL trace of API calls such as statements, binds, fetches and commits. TracePC traces PeopleCode run by the program. Each is a separate bit field.

Can I set the trace for only one run of a program?

Yes. Add -TRACE n (and optionally -TOOLSTRACESQL m and -TOOLSTRACEPC p) in the process definition override parameters or on the run control as allowed, or run psae from the command line. The command-line value overrides psprcs.cfg, so other programs on the server are not traced.

If you want admins to ask in plain English which AE steps got slower this month, we can put a private AI layer over your trace and timing tables on your own servers.

Talk to an Expert Explore More Free Tools