sysquerymetrics view

Field	Definition
uid	User ID
gid	Group ID
id	Unique ID
hashkey	Hash key over the SQL query text
sequence	Sequence number for a row when multiple rows are required for the text of the SQL code
exec_min	Minimum execution time
exec_max	Maximum execution time
exec_avg	Average execution time
elap_min	Minimum elapsed time
elap_max	Maximum elapsed time
elap_avg	Average elapsed time
lio_min	Minimum logical I/O
lio_max	Maximum logical I/O
lio_avg	Average logical I/O
pio_min	Minimum physical I/O
pio_max	Maximum physical I/O
pio_avg	Average physical I/O
cnt	Number of times the query has been executed
abort_cnt	Number of times a query is aborted by the resource governor when a resource limit is exceeded
qtext	Query text

Average values in this view are calculated using:

new_avg = (old_avg * old_count + new_value )/ (old_count + 1) = old_avg + round((new_value - old_avg)/(old_count + 1))

This is an example of the sysquerymetrics view:

select * from sysquerymetrics

uid   gid   hashkey   id   sequence   exec_min 
exec_max   exec_avg   elap_min   elap_max   elap_avg   lio_min 
lio_max   lio_avg   pio_min   pio_max   pio_avg   cnt   abort_cnt 
qtext
----------- ----------- ----------- ----------- ----------- -----------
----------- ----------- ----------- ----------- ----------- -----------
----------- ----------- ----------- ----------- ----------- ----------- 
-------------------------------------------------------------------------
1   1   106588469   480001710   0   0 
0   0   16   33   25   4
4   4   0   4   2   2   0 
select distinct c1 from t_metrics1 where c2 in (select c2 from t_metrics2)

The above example displays a record for a SQL statement. The query text of the statement is select distinct c1 from t_metrics1 where c2 in (select c2 from t_metrics2):

This statement has been executed twice so far (cnt = 2).
The minimum elapsed time is 16 milliseconds; the maximum elapsed time is 33 milliseconds, and the average elapsed time is 25 milliseconds
All the execution times are 0, and this may be due to the CPU execution time being less than 1 millisecond.
The maximum physical I/O is 4, which is consistent with the maximum logical I/O. However, the minimum physical I/O is 0 because data is already in cache in the second run. The logical I/O, at 4, should be static whether or not the data is in memory