Request logging

Request logging logs individual requests received from, and responses sent to, an application. It is most useful for determining what the database server is being asked to do by the application.

Request logging is also a good starting point for performance analysis of a specific application when it is not obvious whether the database server or the client is at fault. You can use request logging to determine the specific request to the database server that might be responsible for problems.

Note

All the functionality and data provided by the request logging feature is also available using diagnostic tracing. Diagnostic tracing also offers additional features and data. See Advanced application profiling using diagnostic tracing.

Logged information includes such things as timestamps, connection IDs, and request type. For queries, it also includes the isolation level, number of rows fetched, and cursor type. For INSERT, UPDATE, and DELETE statements, it also includes the number of rows affected and number of triggers fired.

Caution

The request log can contain sensitive information because it contains the full text of SQL statements that contain passwords, such as the GRANT CONNECT, CREATE DATABASE, and CREATE EXTERNAL LOGIN statements. If you are concerned about security, you should restrict access to the request log file.

You can use the -zr server option to turn on request logging when you start the database server. You can redirect the output to a request log file for further analysis using the -zo server option. The -zn and -zs option let you specify the number of request log files that are saved and the maximum size of request log files.

For more information about these options, see:

Note

These server options do not impact diagnostic tracing in Sybase Central. File-based request logging is completely separate from the diagnostic tracing feature in Sybase Central, which makes use of dbo-owned diagnostic tables in the database to store request log information.

The sa_get_request_times system procedure reads a request log and populates a global temporary table (satmp_request_time) with statements from the log and their execution times. For INSERT/UPDATE/DELETE statements, the time recorded is the time when the statements were executed. For queries, the time recorded is the total elapsed time from PREPARE to DROP (describe/open/fetch/close). That means you need to be aware of any open cursors.

Analyze satmp_request_time for statements that could be candidates for improvements. Statements that are cheap, but frequently executed, may represent performance problems.

You can use sa_get_request_profile to call sa_get_request_times and summarize satmp_request_time into another global temporary table called satmp_request_profile. This procedure also groups statements together and provides the number of calls, execution times, and so on. See sa_get_request_times system procedure, and sa_get_request_profile system procedure.

Caution

When client statement caching is enabled, the max_client_statements_cached option should be set to 0 to disable client statement caching while the request log is captured if the log will be analyzed using the tracetime.pl Perl script. See max_client_statements_cached option.

Filtering request logs

Output to the request log can be filtered to include only requests from a specific connection or from a specific database, using the sa_server_option system procedure. This can help reduce the size of the log when monitoring a database server with many active connections or multiple databases. See sa_server_option system procedure.

To filter according to a connection

Use the following syntax:

CALL sa_server_option( 'RequestFilterConn' , connection-id );

You can obtain connection-id by executing CALL sa_conn_info( ).

To filter according to a database

Use the following syntax:
CALL sa_server_option( 'RequestFilterDB' , database-id );
The database-id can be obtained by executing SELECT CONNECTION_PROPERTY( 'DBNumber' ) when connected to that database. Filtering remains in effect until explicitly reset, or until the database server is shut down.

To reset filtering

Use either of the following two statements to reset filtering either by connection or by database:

CALL sa_server_option( 'RequestFilterConn' , -1 );

CALL sa_server_option( 'RequestFilterDB' , -1 );

Outputting host variables to request logs

Host variable values can be output to a request log.

To include host variable values

To include host variable values in the request log:

use the -zr server option with a value of hostvars

execute the following:

CALL sa_server_option( 'RequestLogging' , 'hostvars' );

The request log analysis procedure, sa_get_request_times, recognizes host variables in the log and adds them to the global temporary table satmp_request_hostvar.

Discuss this page in DocCommentXchange.