sa_get_request_times system procedure

Analyzes the request log to determine statement execution times.

Syntax

sa_get_request_times( filename
  [, conn_id
  [, first_file
  [, num_files ] ] ]
)

Arguments

filename Use this optional LONG VARCHAR parameter to specify the request logging file name.
conn_id Use this optional UNSIGNED INTEGER parameter to specify the ID number of a connection.
first_file Use this optional INTEGER parameter to specify the first file to analyze.
num_files Use this optional INTEGER parameter to specify the number of request log files to analyze.

Remarks

This procedure reads the specified request log and populates the global temporary table satmp_request_time with the statements from the log and their execution times.

For statements such as inserts and updates, the execution time is straightforward. For queries, the time is calculated from preparing the statement to dropping it, including describing it, opening a cursor, fetching rows, and closing the cursor. For most queries, this is an accurate reflection of the amount of time taken. In cases where the cursor is left open while other events take place, such as operator interaction or client processing, the time appears as a large value but is not a true indication that the query is costly.

This procedure recognizes host variables in the request log and populates the global temporary table satmp_request_hostvar with their values. For older databases where this temporary table does not exist, host variable values are ignored.

If you do not specify a log file, the default is the current log file that is specified in the command with -zo, or that has been specified by

sa_server_option( 'RequestLogFile', filename )

If a connection ID is specified, it is used to filter information from the log so that only requests for that connection are retrieved.

Permissions

DBA authority required

Side effects

Automatic commit

Example

The following command obtains the execution times for the requests in the files req.out.3, req.out.4, and req.out.5.

CALL sa_get_request_times('req.out',0,3,3);