XPLAIN style tables

Derby optionally creates database tables to hold statistics information captured using XPLAIN style. You can have zero, one, or many sets of these tables; each set of tables is stored in a separate schema. The schema which is used for capturing statement execution information is specified using the SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA system procedure

You can query these tables to analyze the behavior of statement execution.

All of the above system tables reside in the schema which you specified. Because this is not the default schema, qualify all queries accessing the system tables with the schema name.

You can create the schema and tables ahead of time if you wish, but usually it is easier to let Derby automatically create the schema and the tables for you. You can capture multiple sets of data into the same tables, or you can specify a different schema each time.

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

SYSXPLAIN_STATEMENTS system table
The SYSXPLAIN_STATEMENTS table captures information about statements which have been executed using RUNTIMESTATISTICS with XPLAIN style.
SYSXPLAIN_STATEMENT_TIMINGS system table
The SYSXPLAIN_STATEMENT_TIMINGS table captures information about statement timings which occurred during statements that were executed using RUNTIMESTATISTICS with XPLAIN style.
SYSXPLAIN_RESULTSETS system table
The SYSXPLAIN_RESULTSETS table captures information about each result set which is part of a statement that has been executed using RUNTIMESTATISTICS with XPLAIN style.
SYSXPLAIN_RESULTSET_TIMINGS system table
The SYSXPLAIN_RESULTSET_TIMINGS table captures timing information about result set accesses which occurred during statements that were executed using RUNTIMESTATISTICS with XPLAIN style.
SYSXPLAIN_SCAN_PROPS system table
The SYSXPLAIN_SCAN_PROPS table captures information about table/index accesses which occurred during statements that were executed using RUNTIMESTATISTICS with XPLAIN style.
SYSXPLAIN_SORT_PROPS system table
The SYSXPLAIN_SORT_PROPS table captures information about row sorting actions which occurred during statements that were executed using RUNTIMESTATISTICS with XPLAIN style.