Understanding XPLAIN style database tables

XPLAIN style is an enhanced form of RUNTIMESTATISTICS processing which preserves captured statistics information in database tables. Once the statistics have been collected and saved in the tables, they can be queried for analysis purposes.

Rows accumulate in the SYSXPLAIN_* database tables until you empty the tables by dropping them or executing DELETE FROM statements against them.

Note that, although these tables have stylized names which all start with the prefix "SYSXPLAIN_*", they are not in fact special system catalogs, but are merely ordinary database tables, which can be accessed in all the standard ways that any other database table is accessed. The tables are automatically created if they are not present when statistics are being captured. The tables are all located in a particular schema which is specified by the SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA() system procedure.

The XPLAIN style database tables are summarized here. For more information about the structure and content of each table, see "XPLAIN style tables" in the Derby Reference Manual.

SYSXPLAIN_STATEMENTS
This table contains one row for each statement which has had statistics captured. This row contains the text of the statement, as well as identifiers which can be used to join with the other tables to find more data about how this statement was executed.
SYSXPLAIN_RESULTSETS
This table contains one row for each result set which was used during the execution of a particular explained statement. Most queries have one or several result sets; some complex queries can have many result sets.
SYSXPLAIN_SCAN_PROPS
This table contains one row for each result set which performed a scan of a table, index, or constraint. Using the information in this row, you can determine how much data needed to be examined by the scan.
SYSXPLAIN_SORT_PROPS
This table contains one row for each result set which performed a sort of data. Using the information in this row, you can determine how much data needed to be sorted.
SYSXPLAIN_STATEMENT_TIMINGS
This table contains timing information at the statement level. Timing information is optional, but if it is captured, the data in this table can be used to determine how much time each statement took.
SYSXPLAIN_RESULTSET_TIMINGS
This table contains timing information at the result set level. Timing information is optional, but if it is captured, the data in this table can be used to determine how much time each result set took.
Related concepts
Statistics timing
Statement execution plan
Optimizer estimates
Optimizer overrides