TraceMagic

I have not worked much with this tool, but I have done a little playing around with it, and it produces some great information.

PeopleSoft Says:  TraceMagic is a utility that gives PeopleSoft system administrators, programmers and support engineers the ability to quickly isolate performance bottlenecks in SQL Statements and/or PeopleCode functions. It accomplishes this by turning the text-based, time-ordered tracesql file into a sortable-grid display, allowing the user to quickly locate system performance issues.

Check out My Oracle Support Case:  TraceMagic: Utility for analyzing *.tracesql trace files (Doc ID 1470578.1)

 

Trace Application Engine

An application engine program can be traced by simply changing the trace parameters in the process scheduler configuration file, however, that requires the process scheduler to be brought down and it will trace every application engine ran through the process scheduler.  However, you can append those trace parameters to the process definition and it will trace the individual process.  Navigate to the process definition (Home>PeopleTools>Process Scheduler>Processes –> Override Options)  in the parameter list select append and enter in -TRACE xxx -TOOLSTRACEPC xxx -TOOLSTRACESQL xxx, where xxx is the bit reference based on the needs of the trace. So for example if you wanted options 1, 2, and 128 for trace, you add 1+2+128 = 131, and you enter 131 as the trace parameter: -TRACE 131.

-TRACE parameters:

Value Parameter Description
0 Disables tracing.
1 Initiates the Application Engine step trace.
2 Initiates the Application Engine SQL trace.
4 Initiates the trace for dedicated temporary table allocation to an Application Engine trace (AET) file. You can trace how the system allocates, locks, and releases temporary tables during program runs.
128 Initiates the statement timings trace to a file, which is similar to the COBOL timings trace to a file.
256 Initiates the PeopleCode detail to the file for the timings trace.
1024 Initiates the statement timings trace, but stores the results in the PS_BAT_TIMINGS_LOG and PS_BAT_TIMINGS_DTL tables.
2048 Requests a database optimizer trace file.
4096 Requests a database optimizer to be inserted in the Explain Plan table of the current database.
8192 Sets a trace for PeopleSoft Integration Broker transform programs.

-TOOLSTRACEPC parameters

Value Parameter Description
1 Trace Evaluator instructions
2 List Evaluator program
4 Show assignments to variables
8 Show fetched values
16 Show stack
64 Trace start of programs
128 Trace external function calls
256 Trace internal function calls
512 Show parameter values
1024 Show function return value
2048 Trace each statement in program

-TOOLSTRACESQL parameters

Value Parameter Description
1 SQL statements
2 SQL statement variables
4 SQL connect, disconnect, commit and rollback
8 Row Fetch (indicates that it occurred, not data)
16 All other API calls except ssb
32 Set Select Buffers (identifies the attributes of columns to be selected)
64 Database API specific calls
128 COBOL statement timings
256 Sybase Bind information
512 Sybase Fetch information
1024 SQL Informational Trace
4096 Manager information
8192 Mapcore information

Note that output from the -TRACE flag goes to the application engine trace (.AET) file. Output from the -TOOLSTRACEPC and -TOOLSTRACESQL output goes to the PeopleTools trace file (.TRC).