Statement Timings Traces

The Application Engine statement timing trace report is similar to a COBOL timings trace in which you monitor COBOL programs to evaluate performance. This trace enables you to gather performance information to determine where program performance slows down. After you identify these spots, you might be able to modify your program to run more efficiently, or you might want to change the database schema and configuration to optimize program performance.

The statement timings trace is invaluable for tuning an Application Engine program. It may also be useful as a default trace level for all production runs to provide a metric for long-term performance trends.

By examining all of the figures in this trace, you can identify areas of your program that are not running as efficiently as possible. For instance, if compile counts are high, you can reduce the numbers by using the Application Engine reuse feature. If inserts appear to be running slow and you have many of them, you can increase performance by using the Application Engine bulk insert feature. Each value in the trace, including cumulative totals, is rounded to the nearest tenth of a second, but totals are calculated using non-rounded timings.

You can write this trace to a file or you can write the results to tables. Either way, timings trace overhead is minimal. Internal testing reveals that the Application Engine trace has an overhead between 2 percent and 5 percent of total runtime.

By storing timings information in a table, you can store historical data in the database, which enables you to produce reports that help with trend analysis, allow ad hoc SQL queries for longest running statements, and so on. By storing timings data in the database, you can manipulate and customize reports to show only the metrics in which you are most interested.

You can use third-party tools to query and present the data as detailed graphical representations of program performance. You can also implement alarms if the performance of a program reaches a specified maximum value in a particular area, such as SQL compile time.

Note:

Application Engine does not write the timings trace to a table for programs invoked by the CallAppEngine PeopleCode function. To write to a table, a process instance is required, and programs invoked by CallAppEngine are not assigned a process instance.

The Statements Timings (table) option, or 1024 -TRACE option, populates the following tables.

  • PS_BAT_TIMINGS_LOG (Parent)

    This table stores general information about a program run.

  • PS_BAT_TIMINGS_DTL (Child)

    This table stores detailed information about a program run, such as the execute count, fetch time, and so on.

The SQL Timings (table) option, or the 16834 -TRACE option, populates the following tables.

  • PS_AE_TIMINGS_LG (Parent)

    This table stores general information about a program run.

  • PS_AE_TIMINGS_DT (Child)

    This table stores detailed information about a program run, such as the execute count, fetch time, and so on.

    PS_BAT_TIMINGS_FN

    This table stores PeopleCode detailed timing information.

PeopleSoft software provides BATTIMES.SQR as an example of the type of reports you can generate to reflect the information stored in the BAT_TIMINGS tables. You can produce a summary report for all the programs for a specific run control ID, or you can get detailed data for a specific process instance.

To invoke the BATTIMES.SQR report through PeopleSoft Process Scheduler:

  1. Select PeopleTools, and then Process Scheduler, and then Define Batch Timings.

    The Batch Timings page appears.

  2. From the Report Type drop-down list, select Detail or Summary.

  3. In the Batch Timings For group box, enter the run control ID for summary reports and enter the process instance for detail reports.

  4. When you have made the appropriate selections, click the Run button.

To view batch timings using Process Monitor:

  1. Select PeopleTools, and then Process Scheduler, and then Process Monitor.

  2. Locate the program run associated with the current trace.

  3. Click the Job Details button.

  4. In the Process Detail dialog box, click the Batch Timings link.

    PeopleCode detail timings do not appear; they appear only in the file format.