Derby Reference Manual

SYSXPLAIN_RESULTSET_TIMINGS system table

This table captures timing information about result set accesses which occurred during statements that were executed using RUNTIMESTATISTICS with XPLAIN style (see the RUNTIMESTATISTICS section in the Derby Reference Manual for more information on how to configure this). Note that statistics timing must be configured by calling SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(). 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.

See "Working with RunTimeStatistics" in the Tuning Derby for additional information.

Column Name	Type	Length	Nullability	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		true	The time it took to construct this instance of this result set, in milliseconds.
OPEN_TIME	BIGINT		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		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		true	The time it took to close this instance of the result set, in milliseconds.
EXECUTE_TIME	BIGINT		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		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		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		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		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. I think this 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		true	Similar to TEMP_CONG_CREATE_TIME, this value is the time it took to retrieve rows from the materialized result set, in milliseconds.

Java DB

Apache Derby

SYSXPLAIN_RESULTSET_TIMINGS system table