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.