The system table SYSINFO.COMMANDSTATISTICSRESET 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 RESET COMMANDSTATISTICS or RESET ALL. The command cache contains the SQL statements and their execution plans shared by different database sessions.
RESETDATE |
TIMESTAMP |
Time of the last RESET for these entries |
COMMANDID |
CHAR(8) BYTE |
Identification of the SQL statement |
USERNAME |
CHAR(32) |
Name of the database user |
PREPARECOUNT |
FIXED(10) |
Number of execution preparations |
EXECUTECOUNT |
FIXED(10) |
Number of executions |
EXECUTETIME |
FIXED(20) |
Total duration of executions (µs) |
AVGEXECUTETIME |
FIXED(20) |
Average execution time (µs) |
MINEXECUTETIME |
FIXED(20) |
Minimum execution time (µs) |
MAXEXECUTETIME |
FIXED(20) |
Maximum execution time (µs) |
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 |
FETCHCOUNT |
FIXED(20) |
Number of fetch calls executed |
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 |
DISPATCHCOUNT |
FIXED(20) |
Number of assignments of the CPU during execution of this SQL statement |
TASKSELFSUSPENDCOUNT |
FIXED(20) |
Number of voluntary releases of the CPU during execution of this SQL statement |
REGIONACCESSCOUNT |
FIXED(20) |
Number of region accesses during execution of this SQL statement |
SELFIOREADCOUNT |
FIXED(20) |
Number of read calls done during execution of this SQL statement (no request to I/O threads) |
SELFIOREADPAGECOUNT |
FIXED(20) |
Number of pages read during SELFIOREADCOUNT during execution of this SQL statement |
AVGSELFIOREADABSOLUTETIME |
FIXED(20) |
Average time needed for each read operation during execution of this SQL statement (microseconds) |
SELFIOREADABSOLUTETIME |
FIXED(20) |
Total time needed for each read operation during execution of this SQL statement (microseconds) |
SELFIOWRITECOUNT |
FIXED(20) |
Number of write calls during execution of this SQL statement (no request to I/O threads) |
SELFIOWRITEPAGECOUNT |
FIXED(20) |
Number of pages written during SELFIOWRITECOUNT during execution of this SQL statement |
AVGSELFIOWRITEABSOLUTETIME |
FIXED(20) |
Average time needed for each write operation during execution of this SQL statement (microseconds) |
SELFIOWRITEABSOLUTETIME |
FIXED(20) |
Total time needed for each write operation during execution of this SQL statement (microseconds) |
IOREADCOUNT |
FIXED(20) |
Number of read calls requested during execution of this SQL statement and done by an I/O thread |
IOREADPAGECOUNT |
FIXED(20) |
Number of pages read by an I/O thread during execution of this SQL statement |
AVGIOREADRELATIVETIME |
FIXED(20) |
Average time needed for each read operation during execution of this SQL statement (microseconds) |
IOREADRELATIVETIME |
FIXED(20) |
Total time needed for each read operation done by an I/O thread during execution of this SQL statement (microseconds) |
AVGIOREADABSOLUTETIME |
FIXED(20) |
Average waiting time between read request and getting the replay during execution of this SQL statement (microseconds) |
IOREADABSOLUTETIME |
FIXED(20) |
Total waiting time between read request and getting the replay during execution of this SQL statement (microseconds) |
IOWRITECOUNT |
FIXED(20) |
Number of write calls during execution of this SQL statement and done by an I/O thread |
IOWRITEPAGECOUNT |
FIXED(20) |
Number of pages written by an I/O thread during execution of this SQL statement |
AVGIOWRITERELATIVETIME |
FIXED(20) |
Average time needed for each write operation done by an I/O thread during execution of this SQL statement (microseconds) |
IOWRITERELATIVETIME |
FIXED(20) |
Total time needed for each write operation done by an I/O thread during execution of this SQL statement (microseconds) |
AVGIOWRITEABSOLUTETIME |
FIXED(20) |
Average waiting time between write request and getting the replay during execution of this SQL statement (microseconds) |
IOWRITEABSOLUTETIME |
FIXED(20) |
Total waiting time between write request and getting the replay during execution of this SQL statement (microseconds) |
AVGSUSPENDRELATIVETIME |
FIXED(20) |
Average time for each suspend during execution of this SQL statement (microseconds) |
SUSPENDRELATIVETIME |
FIXED(20) |
Total time for each suspend during execution of this SQL statement (microseconds) |
AVGSUSPENDABSOLUTETIME |
FIXED(20) |
Average waiting time between suspending itself and getting the resume during execution of this SQL statement (microseconds) |
SUSPENDABSOLUTETIME |
FIXED(20) |
Total waiting time between suspending itself and getting the resume during execution of this SQL statement (microseconds) |
AVGWAITRELATIVETIME |
FIXED(20) |
Average waiting time for a lock during execution of this SQL statement (microseconds) |
WAITRELATIVETIME |
FIXED(20) |
Total waiting time for a lock during execution of this SQL statement (microseconds) |
AVGWAITABSOLUTETIME |
FIXED(20) |
Average waiting time between suspending itself and getting the lock during execution of this SQL statement (microseconds) |
WAITABSOLUTETIME |
FIXED(20) |
Total waiting time between suspending itself and getting the lock during execution of this SQL statement (microseconds) |
SLEEPCOUNT |
FIXED(20) |
Number of sleep requests during execution of this SQL statement |
AVGSLEEPRELATIVETIME |
FIXED(20) |
Average sleeping time during execution of this SQL statement (microseconds) |
SLEEPRELATIVETIME |
FIXED(20) |
Total sleeping time during execution of this SQL statement (microseconds) |
AVGSLEEPABSOLUTETIME |
FIXED(20) |
Average time between starting of sleep and being woken up during execution of this SQL statement (microseconds) |
SLEEPABSOLUTETIME |
FIXED(20) |
Total time between starting of sleep and being woken up during execution of this SQL statement (microseconds) |
DATASOURCEWAITCOUNT |
FIXED(20) |
Number of waits for external data source during execution of this SQL statement |
AVGDATASOURCEWAITABSOLUTETIME |
FIXED(20) |
Average waiting time for external data source during execution of this SQL statement (microseconds) |
DATASOURCEWAITABSOLUTETIME |
FIXED(20) |
Total waiting time for external data source during execution of this SQL statement (microseconds) |
RUNNINGCOUNT |
FIXED(20) |
Number of changes to state running during execution of this SQL statement |
AVGRUNNINGABSOLUTETIME |
FIXED(20) |
Average time needed at state running during execution of this SQL statement (microseconds) |
RUNNINGABSOLUTETIME |
FIXED(20) |
Total time needed at state running during execution of this SQL statement (microseconds) |
SCHEDULINGCOUNT |
FIXED(20) |
Number of scheduler calls during execution of this SQL statement |
AVGSCHEDULINGABSOLUTETIME |
FIXED(20) |
Average time needed for scheduling during execution of this SQL statement (microseconds) |
SCHEDULINGABSOLUTETIME |
FIXED(20) |
Total time needed for scheduling during execution of this SQL statement (microseconds) |