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 Java DB 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.

Tuning Java DB contains general information about how to use XPLAIN style and the XPLAIN database tables.

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.