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.

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 Output

Oracle

File and table

Db2 for z/OS

 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.

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

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.

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.

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.

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.

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.

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.

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.

Note:

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

Db2 for z/OS

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

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

  • The PeopleSoft application sets the SET REMARKS parameter to this value: ApplId.Section.Step.Type-RunControlId(ProcessInstance)

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.

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.