Chapter 8 Tracing Application Engine Programs 第八章 跟踪应用程序引擎程序

Chapter 8 Tracing Application Engine Programs第八章 跟踪应用程序引擎程序

Understanding Tracing Application Engine Programs

了解跟踪应用程序引擎程序

You can set the following traces to monitor the performance of Application Engine programs:

您可以设置以下跟踪来监视应用程序引擎程序的性能:

• • Application Engine step trace.
  • 应用引擎步骤跟踪。
  • Application Engine SQL trace.
  • 应用程序引擎SQL跟踪。
  • Application Engine statement timings trace.
  • 应用程序引擎语句计时跟踪。
  • Database optimizer trace.
  • 数据库优化器跟踪。

Note: The general PeopleTools SQL and PeopleCode traces also apply to Application Engine programs.

注意：一般的PeopleTools SQL和PeopleCode跟踪也适用于应用程序引擎程序。

Understanding Trace Results

了解跟踪结果

This section discusses: 本节讨论：

• Trace file sections.

·跟踪文件部分。

• • Step traces.
  • Step追踪
  • SQL traces.
  • SQL的痕迹。
  • Statement timings traces.
  • 语句计时跟踪。
  • Database optimizer traces.
  • 数据库优化器跟踪。

Trace File Sections

跟踪文件节

At the top of each trace, useful information helps you to identify the PeopleTools version, the database name, and the database platform type.

在每个跟踪的顶部，有用的信息可帮助您识别PeopleTools版本、数据库名称和数据库平台类型。

SQL Counts and Timings Section

SQL计数和计时部分

The first section of a trace file is the SQL section. It records the performance of application-specific SQL. The trace values appear within a series of columns and sections. The following table describes each column within the first section of the trace file:

跟踪文件的第一部分是SQL部分。它记录特定于应用程序的SQL的性能。跟踪值出现在一系列列和节中。下表描述跟踪文件第一节中的每一列：

Column	Description
SQL Statement	Application Engine SQL actions and stored SQL objects always have a statement ID. The SQL Statement column shows the statement ID so that you can attribute trace values to individual SQL statements. In the case of SQLExec SQL,  a portion of the SQL statement appears in the first column to help you identify it. For SQL objects, use the TraceName property in the Create SQL so that you can uniquely identify it in the traces.
Compile Column	This column shows how many times the system compiled a SQL statement and how long the compilation took. The term compiled refers to the SQL statement being sent to the database to be parsed and optimized, and it also includes the time required for the first resolution of any PeopleSoft meta- SQL.
Execute Column	This column shows how many times the system executed the SQL statement and the time consumed doing so. The term executed refers to the system sending the compiled SQL to the database server to be run against the database.
Fetch Column	This column applies to Select statements. It shows how many rows your program fetched from the database and how much time this consumed. The system must first execute a Select statement against the database to find the relevant rows and generate an active set. After the set exists, the program must still fetch the rows. Some database APIs have buffered fetches,  which means that the fetch may include more than one row. Therefore, subsequent fetches are free until the buffer becomes empty.
Total Column	This column shows the sum of the compile, execute, and fetch times of the SQL statement. Some database application programming interfaces (APIs may defer a compile to the execute phase or defer an execute to the first fetch operation.
PeopleCode SQL	This subsection is for SQL run from PeopleCode actions.  Compile counts and times for such SQL is included in execute count and times because you do not explicitly control the ReUse feature. To determine whether ReUse is occurring, you must do a program run after enabling the generic PeopleTools trace for SQL statements, API calls, and so on. As a starting point, use a trace value of 31.
Column	Description
Application Engine SQL	This subsection reveals the time attributed to Application Engine overhead that is not directly related to the SQL within your program. For example, the values in this section represent the SQL generated for checkpoints, commits, and so on. If there are Commit statements without checkpoints, it indicates that restart has been disabled, or a restartable program has called a non-restartable program. If the time consumed performing a checkpoint or committing seems more than expected, you should try to reduce it if possible by setting the commit frequency of the steps containing Do loops.
AE Program: program_name	This subsection shows SQL actions for a particular program.  The action properties that affect performance are flagged. For example, BulkInsert. ReUse is not flagged because it is selfevident when the Execute count is higher than the compile count.

Note: When you run a SQL trace at the Application Engine level and the PeopleTools level simultaneously, you may see misleading results. Extra overhead is added to the overall SQL timings by the PeopleTools trace. Tracing SQL at the Application Engine level (-TRACE) adds to the non-SQL times because PeopleTools writes the trace data after timing the SQL.

PeopleCode Actions Section

PeopleCode行动组

The second section of the trace file, or PeopleCode section, records the performance associated with all the PeopleCode actions in your program. The following table describes each column in this section:

跟踪文件的第二部分，即PeopleCode部分，记录与程序中所有PeopleCode操作相关联的性能。下表描述了本节中的每一列：

Column	Description
PeopleCode	The names of the PeopleCode actions in your program.
Call	How many times each PeopleCode action is called during a program run.
Non-SQL	The time spent running non-SQL actions.
SQL	Time spent running SQL. The total SQL time should be similar to that of the PeopleCode SQL subsection in the first section of the trace file.
Total	The cumulative amount of time spent on an action.

Note: The system rounds to the first decimal place (tenths), but only after it calculates the sum of each action time.

附注：系统四舍五入到小数点后第一位（十分之一），但只有在它计算出每个动作时间的总和之后。

PeopleCode Built-ins and Methods Section

PeopleCode内建及方法组

The third section of the trace file contains either a list or summary of the PeopleCode built-ins and methods used. To see a list of built-ins and methods, you must enable the PeopleCode detail timings in addition to the statement timings trace.

跟踪文件的第三部分包含使用的PeopleCode内置和方法的列表或摘要。要查看内置和方法的列表，除了语句时间跟踪之外，还必须启用PeopleCode详细时间。

If a method or built-in function takes a large amount of time, you may want to consider alternatives. For example, if array processing dominates your runtime, consider inserting the data into temporary tables and performing the processing on tables in the database.

如果一个方法或内置函数需要大量的时间，您可能需要考虑替代方法。例如，如果数组处理主导了您的运行时，请考虑将数据插入到临时表中，并对数据库中的表执行处理。

Summary Data

汇总数据

The fourth section of the trace file contains summary data. The values in this section provide an overview of the program run without providing too many details.

跟踪文件的第四部分包含摘要数据。本节中的值提供了程序运行的概述，但没有提供太多的细节。

The following table describes the values that appear in this section:

下表描述了本节中出现的值：

Column	Description
Total run time	The overall amount of time a program required to complete from start to finish.
Time in application SQL	The time that your program spent executing SQL. This value includes SQL run by both PeopleCode and SQL actions.
Percent time in application SQL	The percentage of time spent running SQL compared to the entire program run.
Time in PeopleCode	The time the program spent running PeopleCode. Time in PeopleCode excludes SQL run from within PeopleCode.
Percent time in PeopleCode	The percentage of time spent running PeopleCode compared to the entire program run.
Total time in Cache	The amount of time the program spent retrieving objects from the cache or refreshing the cache. Total time in cache includes all memory cache access, file cache access, and SQL run to load managed objects such as Application Engine program components, metadata, and so on. Time varies according to where Application Engine finds an object. For instance,  retrieving an object that the system cached during a previous run is faster than retrieving it from the database.
Column	Description
Number of calls to Cache	The actual number of calls the program made to the cache. The number of calls to the cache remains constant for the same Application Engine program processing the same data.

Environment Information Section

环境资料科

The fifth section of the trace file contains environment information specific to Application Engine. If programs appear to be performing poorly, check the trace value that you set.

跟踪文件的第五部分包含特定于Application Engine的环境信息。如果程序运行不佳，请检查您设置的跟踪值。

Each trace produces an unavoidable degree of overhead. As a result, the more traces you have enabled, the more likely you are to see degraded performance. Run only the traces you need. This section of the trace file shows information about:

每个跟踪都会产生不可避免的开销。因此，您启用的跟踪越多，就越有可能看到性能下降。只运行您需要的跟踪。跟踪文件的这一部分显示有关以下内容的信息:

• SQL traces.
• SQL的痕迹。
• PeopleCode traces.
• PeopleCode的痕迹。
• Application Engine traces.
• 应用引擎跟踪。
• Application Engine DbFlags (%UpdateStats).
• 应用程序引擎DbFlags（%更新状态）。

Step Traces

步迹线

A step trace reports each step name that your program runs and the order it runs in. Associated with each step is a timestamp, a Do action level, and an action type.

步骤跟踪报告程序运行的每个步骤名称及其运行顺序。与每个步骤相关联的是时间戳、Do操作级别和操作类型。

The trace shows the steps that run within a called section by indented formatting. For example, a step that runs within a called section is preceded by two dots (..), while other steps are preceded by only one dot (.).

跟踪显示了通过缩进格式在被调用部分中运行的步骤。例如，在被调用的部分中运行的步骤前面有两个点（..），而其他步骤前面只有一个点（.）。

SQL Traces

SQL跟踪

The SQL trace report shows formatted SQL processes, including commits, rollbacks, and restarts. You can also view the buffers associated with each SQL statement. Use a SQL trace to spot errors in your SQL and to view your commit strategy.

SQL跟踪报告显示格式化的SQL进程，包括提交、回滚和重新启动。还可以查看与每个SQL语句关联的缓冲区。使用SQL跟踪来发现SQL中的错误并查看提交策略。

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.

Application Engine语句计时跟踪报告类似于COBOL计时跟踪，您可以在其中监视COBOL程序以评估性能。此跟踪使您能够收集性能信息，以确定程序性能降低的地方。识别这些点之后，您可以修改程序以更有效地运行，或者您可能希望更改数据库架构和配置以优化程序性能。

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.

您可以将此跟踪写入文件，也可以将结果写入表。无论哪种方式，定时跟踪开销都是最小的。内部测试显示，应用程序引擎跟踪的开销占总运行时的2%到5%。

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.

通过将时间信息存储在表中，可以将历史数据存储在数据库中，从而使您能够生成有助于趋势分析的报告，允许对运行时间最长的语句进行特殊SQL查询，等等。通过将时间数据存储在数据库中，您可以操作和自定义报表，仅显示您最感兴趣的指标。

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.

您可以使用第三方工具来查询数据，并将其显示为程序性能的详细图形表示。如果程序在特定区域（如SQL编译时）的性能达到指定的极大值，还可以实现警报。

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.

附注：对于由CallAppEngine PeopleCode函数调用的程序，应用程序引擎不会将计时跟踪写入表。若要写入表，需要流程实例，并且CallAppEngine调用的程序没有分配流程实例。

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

报表计时（表）选项或1024 -TRACE选项填充以下表。

• 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.

SQL计时（表）选项或16834 -TRACE选项填充以下表。

• 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.

此表存储PeopleCode详细的计时信息。

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.

PeopleSoft软件提供BATTIMES.SQR作为可以生成报告类型的示例反映BAT_TIMINGS表中存储的信息。您可以为特定运行控制ID的所有程序生成摘要报告，也可以获取特定流程实例的详细数据。

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

要通过PeopleSoft Process Scheduler调用BATTIMES.SQR报告：

1. Select PeopleTools > Process Scheduler > Define Batch Timings.

选择PeopleTools>进程调度程序>定义批处理时间。

The Batch Timings page appears.

此时将显示批处理计时页面。

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

从报表类型下拉列表中，选择详细信息或汇总。

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

在“Batch Timings For”组框中，为汇总报告输入运行控制ID，为详细报告输入流程实例。

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

做出适当的选择后，单击“运行”按钮。

To view batch timings using Process Monitor:

要使用Process Monitor查看批处理定时:

1. Select PeopleTools > Process Scheduler > Process Monitor.

选择PeopleTools>进程调度器>进程监视器。

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

找到与当前跟踪关联的程序运行。

1. Click the Job Details button.

单击“作业详细信息”按钮。

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

在“进程详细信息”对话框中，单击“批处理计时”链接。

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

PeopleCode详细计时不会显示；它们只以文件格式显示。

Database Optimizer Traces

数据库优化程序跟踪

The database optimizer trace provides the execution or query plan for the SQL that your Application Engine program generates. Each SQL statement is traced only once. You can write the trace to a file or a table.

数据库优化程序跟踪为应用程序引擎程序生成的SQL提供执行或查询计划。每个SQL语句只跟踪一次。您可以将跟踪写入文件或表。

How you view the results of this trace depends on the relational database management system (RDBMS) that you are currently using. For instance, on some platforms only the trace-to-file option is available, whereas on others only the trace-to-table option is available. The following table shows the options available for each of the supported platforms:

如何查看此跟踪的结果取决于当前使用的关系数据库管理系统（RDBMS）。例如，在一些平台上，只有跟踪到文件选项可用，而在另一些平台上，只有跟踪到表选项可用。下表显示了每个受支持平台的可用选项:

RDBMS	Output
Oracle	File and table
Db2 for z/OS	Table
DB2 for UDB (AIX, Sun Solaris, Microsoft Windows NT)	Table
Microsoft SQL Server	File and table

Note: PeopleTools applications do not collect optimizer data for SQL originating from PeopleCode actions unless you run Oracle and use file output. In this case, the system traces all SQL that runs after the first SQL action runs.

Oracle

When sending the trace to a file, Application Engine writes the trace file to the default Oracle trace directory specified on the database server. To read the trace file, use the TKPROF utility.

当将跟踪发送到文件时，应用程序引擎将跟踪文件写入数据库服务器上指定的默认甲骨文跟踪目录。要读取跟踪文件，请使用TKPROF实用程序。

To send the trace to a table on Oracle, a PLAN_TABLE table must exist and the statement_id must be of type VarChar2(254) instead of VarChar2(30).

When sending to a table, the PeopleSoft application updates the trace rows as follows:

• For the EXPLAIN PLAN SET STATEMENT_ID, PeopleSoft software updates the STATEMENT ID column:

EXPLAIN PLAN SET STATEMENT_ID = ApplId.Section.Step.Type FOR sqlstmt

• or the PLAN_TABLE REMARKS column, PeopleSoft software updates the REMARKS column:

PLAN_TABLE‘s REMARKS column = ‘ProcessInstance-RunControlId(QueryNo)’

Where queryno is a count of how many SQL statements have been traced up to a particular point.

Note: When tracing to a table with Oracle, PeopleSoft software does not perform optimizer traces on %UpdateStats and %TruncateTable unless the latter resolves into a Delete statement. Alternatively, sending the Oracle TKPROF utility to a file handles both the Analyze and Truncate commands.

Microsoft SQL Server

微软SQL服务器

When you send trace output to a file, Application Engine writes the optimizer trace to the following location: %TEMP%\psms<queueid><spid>.trc. To read the trace, use the SQL Server Profiler utility.

将跟踪输出发送到文件时，应用程序引擎将优化器跟踪写入以下位置:%TEMP%\psms<queueid><spid>.trc. 若要读取跟踪，请使用SQL Server事件探查器实用工具。

Note: The system writes the trace file to the server directory when you specify the trace on the client. If the client has %Temp% set to a drive or directory that does not exist on the server, then Application Engine does not generate a trace file.

注意：当您在客户端上指定跟踪时，系统会将跟踪文件写入服务器目录。如果客户端将%Temp%设置为服务器上不存在的驱动器或目录，则应用程序引擎不会生成跟踪文件。

When you send trace output to a table, Application Engine writes the trace data to the

dbo.PS_OPTIMIZER_TRC table. PeopleTools creates the table automatically when you run the trace for the first time. The trace data written to the table is identical to the data that appears in the optimizer trace file.

将跟踪输出发送到表时，应用程序引擎将跟踪数据写入dbo。PS_OPTIMIZER_TRC表首次运行跟踪时，PeopleTools会自动创建表。写入表的跟踪数据与优化器跟踪文件中显示的数据相同。

You use the SQL Server Profiler utility to view the optimizer results. To view the populated trace table, specify the current server and database in the Source Table dialog box. The Owner value must be dbo, and theTable value must be PS_OPTIMIZER_TRC.

您可以使用SQL Server事件探查器实用工具查看优化器结果。若要查看已填充的跟踪表，请在“源表”对话框中指定当前服务器和数据库。所有者值必须是dbo，Table值必须是PS_OPTIMIZER_TRC。

In the trace, you find information about text, duration, and start time for:

在跟踪中，您可以找到有关以下内容的文本、持续时间和开始时间的信息:

• Execution plans.
• 执行计划。
• Remote procedure calls.
• 远程过程调用。
• Insert statements (Update, Delete, and Select statements).
• 插入语句（更新、删除和选择语句）。
• PeopleSoft-generated user events that associate trace data with a PeopleSoft SQL identifier.
• PeopleSoft生成的将跟踪数据与PeopleSoft SQL标识符相关联的用户事件。

If the Application Engine program terminates while you are using the trace option, verify that Application

Engine was not tracing a SQL statement at the moment the program terminated. If it was tracing a SQL statement at that time, you must manually stop the trace. Otherwise, the trace thread on the server continues to run and will lock the trace file, and each time that server process ID (SPID) is reused by the server, new information will be appended to the locked trace file.

如果在使用跟踪选项时应用程序引擎程序终止，请验证在程序终止时应用程序引擎没有跟踪SQL语句。如果它当时正在跟踪SQL语句，则必须手动停止跟踪。否则，服务器上的跟踪线程将继续运行并锁定跟踪文件，每次服务器重用该服务器进程ID（SPID）时，都会向锁定的跟踪文件追加新信息。

To stop the trace manually, submit the following command from Query Analyzer:

要手动停止跟踪，请从查询分析器提交以下命令:

xp_trace_destroyqueue queueid

The queueid variable in the file name %TEMP%\psms_queueid_spid.trc is the ID that corresponds to the queue used for the first SQL statement that the system profiled. Because this trace is only designed to trace Application Engine SQL (not PeopleTools SQL), the queue is closed after every statement profiled. Therefore, the queue that must be destroyed may not be the queue ID used in the trace file.

文件名%中的队列id变量temp%\psms_queeid_spid.trc是与系统分析的第一条sql语句所使用的队列相对应的id。因为此跟踪仅用于跟踪应用程序引擎SQL（而不是PeopleTools SQL），所以在分析每个语句之后，队列都将关闭。因此，必须销毁的队列可能不是跟踪文件中使用的队列ID。

Note: If the %TEMP% variable is set to a location that does not exist, Application Engine does not generate a trace file.

注意：如果将%TEMP%变量设置为不存在的位置，则应用程序引擎不会生成跟踪文件。

Db2 for z/OS

适用于z/OS的Db2

For Db2 for z/OS, you can only send the optimizer trace to a table. To facilitate this trace:

对于Db2 for z/OS，您只能将优化器跟踪发送到表。为了便于跟踪:

• The PeopleSoft application selects the maximum query number from the PLAN_TABLE table, increments it by 1000 to avoid clashing with other processes, and then increments it by 1 for every SQL statement traced.
• PeopleSoft应用程序从PLAN_TABLE表中选择最大查询数，将其递增1000以避免与其他进程冲突，然后对于每一个跟踪的SQL语句，将其递增1。
• The PeopleSoft application sets the SET REMARKS parameter to this value: ApplId.Section.Step.Type-RunControlId(ProcessInstance)
• PeopleSoft应用程序将SET MERGATES参数设置为以下值：应用程序。一节。迈步类型-运行控制标识（进程实例）

Note: Before using the Database Optimizer Trace, you must first create a DB2 PLAN_TABLE. Refer to your Db2 for z/OS Administration Guide for the correct format and instructions for creating the

PLAN_TABLE.

注意：在使用数据库优化器跟踪之前，必须首先创建DB2 PLAN_TABLE。请参考您的Db2/OS管理指南，以获取正确的格式和创建PLAN_TABLE的说明。

Database Optimizer Trace and Performance

数据库优化器跟踪和性能

While the database optimizer trace is enabled, performance may be affected. Typically, you turn on this trace only when you are collecting detailed performance metrics. When you are not tuning system performance, turn off the optimizer trace.

启用数据库优化程序跟踪时，性能可能会受到影响。通常，只有在收集详细的性能指标时才打开此跟踪。不调优系统性能时，关闭优化器跟踪。

To prevent an administrator or perhaps a user from unwittingly turning the optimizer trace on or leaving it on after doing performance tuning, you can disable the database optimizer trace for an entire database.

若要防止管理员或用户无意中打开优化器跟踪或在执行性能调优后仍保持跟踪状态，可以对整个数据库禁用数据库优化器跟踪。

For example, if you have a production database and a development database, you might want to enable the optimizer trace for the development database but disable it for the production database.

例如，如果您有一个生产数据库和一个开发数据库，您可能希望为开发数据库启用优化器跟踪，但对生产数据库禁用它。

On the PeopleTools Options page, clear the Allow DB Optimizer Trace option to disable the optimizer trace for a database.

在“PeopleTools选项”页面上，清除“允许DB优化器跟踪”选项以禁用数据库的优化器跟踪。