Field |
Definition |
|---|---|
uid |
User ID |
gid |
Group ID |
id |
Unique ID |
hashkey |
Hashkey over the SQL query text |
sequence |
Sequence number for a row when multiple rows are required for the text of the SQL |
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 this formula:
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.
