See "Working with RunTimeStatistics" in
for information
on how to configure this.
Note that statistics timing must be configured by calling
SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1).
Each row in this table describes various timing information
for this particular result set in this particular statement.
Rows in this table are typically joined with rows in
SYSXPLAIN_STATEMENTS and SYSXPLAIN_RESULTSETS during analysis.
For example:
select rs.op_identifier, rst.execute_time
from my_stats.sysxplain_resultsets rs,
my_stats.sysxplain_resultset_timings rst
where rs.stmt_id = ? and
rs.timing_id = rst.timing_id
order by rst.execute_time desc
Rows in this table are added
automatically when
has been configured appropriately. The
rows remain in the table until you delete them or drop the table.
The following table shows the contents of the SYSXPLAIN_RESULTSET_TIMINGS
system table.
SYSXPLAIN_RESULTSET_TIMINGS system table
For each column in the SYSXPLAIN_RESULTSET_TIMINGS system table, this table provides its data type, its length in bytes, whether it is nullable, and a description of its contents.
Column Name
Type
Length
Nullable
Contents
TIMING_ID
CHAR
36
false
A unique ID for this particular row. This column can
be used to join against the TIMING_ID column in the
SYSXPLAIN_RESULTSETS table.
CONSTRUCTOR_TIME
BIGINT
20
true
The time it took to construct this instance of this
result set, in milliseconds.
OPEN_TIME
BIGINT
20
true
The time it took to open this instance of this
result set, in milliseconds. Note that if this result set was opened
multiple times, this column is the sum of all the individual open times.
NEXT_TIME
BIGINT
20
true
The accumulated time for all the calls to fetch the
next row from this result set, in milliseconds,
for all the opens of this result set.
CLOSE_TIME
BIGINT
20
true
The time it took to close this instance of the result
set, in milliseconds.
EXECUTE_TIME
BIGINT
20
true
The time for all operations performed by this
result set, excluding the time taken by all the children result sets of
this result set, in milliseconds.
AVG_NEXT_TIME_PER_ROW
BIGINT
20
true
If there was at least one row returned from this result
set, then this value is the NEXT_TIME value divided by the number of rows
returned from this result set, which thus is the average time, in
milliseconds, that it took to retrieve a row from this result set.
PROJECTION_TIME
BIGINT
20
true
This value is NULL unless this result set is a
PROJECTION result set, in which case this column contains the time,
in milliseconds, that it took to perform projection of columns from the
rows in this result set.
RESTRICTION_TIME
BIGINT
20
true
This value is NULL unless this result set is a
PROJECTION result set, in which case this column contains the time,
in milliseconds, that it took to perform restriction of rows from the
rows in this result set.
TEMP_CONG_CREATE_TIME
BIGINT
20
true
For result sets which involve a materialization of
a temporary intermediate result set, this value is the time it took to
create the materialized result set, in milliseconds. This materialization
may occur with hash joins where the number of rows in the intermediate
result is too large to hold in memory.
TEMP_CONG_FETCH_TIME
BIGINT
20
true
Similar to TEMP_CONG_CREATE_TIME, this value is
the time it took to retrieve rows from the materialized result set, in
milliseconds.