The SYSXPLAIN_RESULTSET_TIMINGS table captures timing information about result set accesses which occurred during statements that were executed using RUNTIMESTATISTICS with XPLAIN style.
See "Working with RunTimeStatistics" in Tuning Derby 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 Derby 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.
|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.|