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.