When RUNTIMESTATISTICS is turned on for a connection, Derby maintains
information about the execution plan for each statement executed within the
connection (except for COMMIT) until the attribute is turned off.
For the most recently executed query, RUNTIMESTATISTICS returns information
about:
- the length of the compile time and the execute time.
This can help in benchmarking queries.
- the statement execution plan.
This is a description
of result set nodes, whether an index was used, what the join order was, how
many rows qualified at each node, and how much time was spent in each node.
This information can help you determine whether you need to add indexes or
rewrite queries.
The exact details presented, as well as the format of presentation, can
change. There are two techniques for retrieving the RUNTIMESTATISTICS
information:
- call SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS, which retrieves
the RUNTIMESTATISTICS information as formatted text
- call SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA before executing your
statements, which causes
Derby
to store the RUNTIMESTATISTICS information in the SYSXPLAIN
database tables, which can then be queried
later to retrieve the data.