Using PlanExporter
Derby Tools and Utilities Guide
65
PlanExporter
Use the
PlanExporter
tool to export query plan data for further analysis. The query
plan data can be exported in a variety of formats:
· XML, the base format for exported query plan data
· HTML, which helps you view graphically the execution plans of complex queries you
have executed
By using this tool, you can avoid querying XPLAIN style tables to get a basic idea of the
query plan followed by the optimizer.
You can specify other query plan export formats by specifying an appropriate XSL
stylesheet to transform the query plan data, or you can export the query plan data as
XML, then reformat as appropriate using any external XML-aware tool of your choice.
Note: The
PlanExporter
tool is in an experimental stage. The Derby team welcomes
comments and feedback on how to improve it.
Using PlanExporter
Before you run the
PlanExporter
tool, make sure that your classpath contains the
Derby jar files, including derbytools.jar.
Before you run the
PlanExporter
tool, you must capture the
stmt_id
of the query you
have executed from
SYSXPLAIN_STATEMENTS system table
. To do so, follow these
steps:
1. Use XPLAIN styles to capture the runtime statistics.
Refer to "SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA system procedure" in the
Derby Reference Manual to see how to do this.
Note: You must remember the
schema_name
.
2. Query the SYSXPLAIN_STATEMENTS system table to obtain the
stmt_id
of
the query you have executed.
Refer to "SYSXPLAIN_STATEMENTS system table" in the Derby Reference
Manual for information about the SYSXPLAIN_STATEMENTS system table.
You can run the tool as follows in the directory where your database is located.
java org.apache.derby.tools.PlanExporter derby_connection_URL schema_name
stmt_id options
The options can be passed according to your requirements. Consider the following
possible scenarios:
· To generate an XML file of the query plan, specify the following option:
-xml path
The path can be either absolute or relative. If the root filename does not have a
suffix, the tool appends
.xml
.
· To generate a plain HTML file of the query plan, you can use the default simple
style sheet provided with Derby. Specify the following options:
-html path
If the root filename does not have a suffix, the tool appends
.html
.
