sa_performance_diagnostics system procedure

Returns a summary of request timing information for all connections when the database server has request timing logging enabled.

Syntax

sa_performance_diagnostics( )

Result set

Column name	Data type	Description
Number	INTEGER	Returns the connection ID (a number) for the current connection.
Name	VARCHAR(255)	Returns the name of the current connection. You can specify a connection name using the ConnectionName (CON) connection parameter. The following names are used for temporary connections created by the database server: INT:ApplyRecovery INT:BackupDB INT:Checkpoint INT:Cleaner INT:CloseDB INT:CreateDB INT:CreateMirror INT:DelayedCommit INT:DiagRcvr INT:DropDB INT:EncryptDB INT:Exchange INT:FlushMirrorLog INT:FlushStats INT:HTTPReq INT:PromoteMirror INT:PurgeSnapshot INT:ReconnectMirror INT:RecoverMirror INT:RedoCheckpoint INT:RefreshIndex INT:ReloadTrigger INT:RenameMirror INT:RestoreDB INT:StartDB INT:VSS
Userid	VARCHAR(255)	Returns the user ID for the connection.
DBNumber	INTEGER	Returns the ID number of the database.
LoginTime	TIMESTAMP	Returns the date and time the connection was established. This value is affected by simulated time zone.
TransactionStartTime	TIMESTAMP	Returns the date and time the database was first modified after a COMMIT or ROLLBACK, or an empty string if no modifications have been made to the database since the last COMMIT or ROLLBACK. This value is affected by simulated time zone.
LastReqTime	TIMESTAMP	Returns the date and time at which the last request for the specified connection started. This column can contain null for internal connections, such as events. This value is affected by simulated time zone.
ReqType	VARCHAR(255)	Returns the type of the last request. If a connection has been cached by connection pooling, its ReqType value is CONNECT_POOL_CACHE.
ReqStatus	VARCHAR(255)	Returns the status of the request. It can be one of the following values: Idle The connection is not currently processing a request. Unscheduled* The connection has work to do and is waiting for an available database server worker. BlockedIO* The connection is blocked waiting for an I/O. BlockedContention* The connection is blocked waiting for access to shared database server data structures. BlockedLock The connection is blocked waiting for a locked object. Executing The connection is executing a request. The values marked with an asterisk (*) are only returned when logging of request timing information has been turned on for the database server using the -zt server option. If request timing information is not being logged (the default), the values are reported as Executing.
ReqTimeUnscheduled	DOUBLE	Returns the amount of unscheduled time, or NULL if the -zt option was not specified.
ReqTimeActive	DOUBLE	Returns the amount of time, in seconds, spent processing requests, or NULL if the -zt option was not specified.
ReqTimeBlockIO	DOUBLE	Returns the amount of time, in seconds, spent waiting for I/O to complete, or NULL if the -zt option was not specified.
ReqTimeBlockLock	DOUBLE	Returns the amount of time, in seconds, spent waiting for a lock, or NULL if the -zt option was not specified.
ReqTimeBlockContention	DOUBLE	Returns the amount of time, in seconds, spent waiting for atomic access, or NULL if the RequestTiming server property is set to Off.
ReqCountUnscheduled	INTEGER	Returns the number of times the connection waited for scheduling, or NULL if the -zt option was not specified.
ReqCountActive	INTEGER	Returns the number of requests processed, or NULL if the RequestTiming server property is set to Off.
ReqCountBlockIO	INTEGER	Returns the number of times the connection waited for I/O to complete, or NULL if the -zt option was not specified.
ReqCountBlockLock	INTEGER	Returns the number of times the connection waited for a lock, or NULL if the -zt option was not specified.
ReqCountBlockContention	INTEGER	Returns the number of times the connection waited for atomic access, or NULL if the -zt option was not specified.
LastIdle	INTEGER	Returns the number of ticks between requests.
BlockedOn	INTEGER	Returns zero if the current connection isn't blocked, or if it is blocked, the connection number on which the connection is blocked because of a locking conflict.
UncommitOp	INTEGER	Returns the number of uncommitted operations.
CurrentProcedure	VARCHAR(255)	Returns the name of the procedure that a connection is currently executing. If the connection is executing nested procedure calls, the name is the name of the current procedure. If there is no procedure executing, an empty string is returned.
EventName	VARCHAR(255)	Returns the name of the associated event if the connection is running an event handler. Otherwise, an empty string is returned.
CurrentLineNumber	INTEGER	Returns the current line number of the procedure or compound statement a connection is executing. The procedure can be identified using the CurrentProcedure property. If the line is part of a compound statement from the client, an empty string is returned.
LastStatement	LONG VARCHAR	Returns the most recently prepared SQL statement for the current connection. The LastStatement value is set when a statement is prepared, and is cleared when a statement is dropped. Only one statement string is remembered for each connection. If sa_conn_activity reports a non-empty value for a connection, it is most likely the statement that the connection is currently executing. If the statement had completed, it would likely have been dropped and the property value would have been cleared. If an application prepares multiple statements and retains their statement handles, then the LastStatement value does not reflect what a connection is currently doing. When client statement caching is enabled, and a cached statement is reused, this property returns an empty string.
LastPlanText	LONG VARCHAR	Returns the long text plan of the last query executed on the connection. You control the remembering of the last plan by setting the RememberLastPlan option of the sa_server_option system procedure, or using the -zp server option.
AppInfo	LONG VARCHAR	Returns information about the client that made the connection. For HTTP connections, this includes information about the browser. For connections using older versions of jConnect or SAP Open Client, the information may be incomplete. The API value can be DBLIB, ODBC, OLEDB, ADO.NET, iAnywhereJDBC, CAPI_JavaScript-XS, CAPI_Node.js, CAPI_PerlDBD, CAPI_PHP, CAPI_PYTHON, CAPI_RUBY, DBEXPRESS, or any other user-defined value.
LockCount	INTEGER	Returns the number of locks held by the connection.
SnapshotCount	INTEGER	Returns the number of snapshots associated with the connection.

Remarks

The sa_performance_diagnostics system procedure returns a result set consisting of a set of request timing properties and statistics if the server has been told to collect the information. Recording of request timing information must be turned on the database server before calling sa_performance_diagnostics. To do this, specify the -zt option when starting the database server or execute the following:

CALL sa_server_option( 'RequestTiming','ON' );

Privileges

You must have EXECUTE privilege on the system procedure, as well as the MONITOR system privilege.

Side effects

None

Example

Find all requests that are currently executing, and have been executing for more than 60 seconds:

SELECT  Number, Name,
      CAST( DATEDIFF( second, LastReqTime, CURRENT TIMESTAMP ) AS DOUBLE ) AS ReqTime
FROM  sa_performance_diagnostics()
WHERE ReqStatus <> 'IDLE' AND ReqTime > 60.0
ORDER BY ReqTime DESC;