Object documentationCOMMANDSTATISTICS Locate this document in the navigation structure

 

The system table SYSINFO.COMMANDSTATISTICS describes SQL statements that are stored in the command cache, and also contains statistical information on the activities of these SQL statements collected since the last time the database was started. The command cache contains the SQL statements and their execution plans shared by different database sessions.

Structure

COMMANDSTATISTICS

COMMANDID

CHAR(8) BYTE

Identification of the SQL statement

INTERNAL

CHAR(3)

SQL statement was generated implicitly (YES | NO)

HASHVALUE

FIXED(10)

Hash value

HASHTABLESLOT

FIXED(5)

Slot in the hash table

STATUS

CHAR(10)

Status (PREPARING | PREPARED | INVALID | NEW | DROPPED)

USERNAME

CHAR(32)

Name of the database user

SCHEMANAME

CHAR(32)

Current schema when execution plan was created

SQLMODE

CHAR(8)

SQL mode when execution plan was created

ISOLATIONLEVEL

FIXED(3)

Isolation level when execution plan was created

DATEFORMAT

CHAR(50)

Date and time format when execution plan was created

MASSCOMMAND

CHAR(3)

SQL statement can be used as an ARRAY command (YES | NO)

STATEMENTCODETYPE

CHAR(15)

Code attribute of the SQL statement (ASCII | UNICODE | UNICODE SWAP)

QUERYREWRITE

CHAR(3)

Indicates whether a query rewrite took place (YES | NO)

STATEMENTSIZE

FIXED(10)

Length of the SQL statement (bytes)

STATEMENT

CLOB

SQL statement

PLANSIZE

FIXED(10)

Memory requirements of the execution plan (bytes)

DBPROCEDURESCHEMANAME

CHAR(32)

Schema name of the calling database procedure

DBPROCEDURENAME

CHAR(32)

Name of the calling database procedure

APPLICATIONINFORMATION

CHAR(120)

Optional information given by the executing application

APPLICATIONLINENUMBER

FIXED(10)

Optional line number given by the executing application

PREPARECOUNT

FIXED(10)

Number of execution preparations

REFERENCECOUNT

FIXED(5)

Number of database sessions for which the SQL statement has been prepared for execution

CURRENTEXECUTECOUNT

FIXED(5)

Current number of executions

EXECUTECOUNT

FIXED(10)

Number of executions

PREPARETIME

FIXED(20)

Parse time (microseconds)

EXECUTETIME

FIXED(20)

Total duration of executions (microseconds)

AVGEXECUTETIME

FIXED(20)

Average execution time (microseconds)

MINEXECUTETIME

FIXED(20)

Minimum execution time (microseconds)

MAXEXECUTETIME

FIXED(20)

Maximum execution time (microseconds)

READROWCOUNT

FIXED(20)

Number of rows read

QUALIFIEDROWCOUNT

FIXED(20)

Number of qualified rows

VIRTUALREADCOUNT

FIXED(20)

Number of reads in the data cache

PHYSICALREADCOUNT

FIXED(20)

Number of reads in the data cache that triggered reads on the disk

FETCHROWCOUNT

FIXED(20)

Number of rows (in the result tables generated by the SQL statement) sent to the application

SUSPENDCOUNT

FIXED(20)

Number of suspends

WAITCOUNT

FIXED(20)

Number of waits caused by lock collisions

READAHEADREQUESTCOUNT

FIXED(20)

Number of read ahead requests

READAHEADIGNOREREQUESTCOUNT

FIXED(20)

Number of ignored read ahead requests

READAHEADPHYSICALREADPAGECOUNT

FIXED(20)

Number of pages read via read ahead

More Information

COMMANDSTATISTICSRESET