ASA SQL User's Guide
  Monitoring and Improving Performance
    Performance analysis tools

Request-level logging

Request-level logging is a good starting point for performance analysis of a specific application when it is not obvious whether the server or the client is at fault. It is also useful in determining the specific request to the server that might be responsible for problems.

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

Logged information includes timestamps, connection ids, and request type, for example. You can use the -zr database server option to specify what type of information is logged. You can redirect the output to a file for further analysis using the -zo option.

The sa_get_request_times ( [ request_log_filename [, connection_id ] ] ) stored procedure reads a request-level log and populates a global temporary table satmp_request_time with statements from the log and their execution times. The time recorded is straightforward for INSERT/UPDATE/DELETE statements. 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 candidates. Statements that are cheap but frequently executed may represent performance problems

You can use sa_get_request_profile( [ request_log_filename [, connection_id ] ] ) to call sa_get_request_times() and summarize the resulting satmp_request_time into another global temporary table satmp_request_profile. This procedure also groups statements together and provides the number of calls, execution times, and so on.

Filtering request-level logs 

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

To filter according to a connection

• Use the following syntax:

  call sa_server_option('requests_for_connection',connection-id)

  where connection-id can be obtained from sa_conn_info().

To filter according to a database

• Use the following syntax:

  call sa_server_option('requests_for_database',database-id)

  The database-id can be obtained from connection_property('DBNumber') when connected to that database. Filtering remains in effect until explicitly reset, or until the server is shut down.

To reset filtering

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

  call sa_server_option('requests_for_connection',-1)

  call sa_server_option('requests_for_database',-1)

Outputting host variables to request-level logs 

Host variable values can be output to a request log.

To include host variable values

• To include host variable values in the request-level log:

  • use the -zr server command line option with a value of sql+hostvars

  • execute the following:

    call sa_server_option('request_level_logging','sql+host')

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. For database versions before 9.0.0 where this temporary table does not exist, host variable values are ignored.