Overview

This web page describes how to use the optional optimizerTracing and optimizerTracingViews tools to produce and view an xml-formatted trace of the optimizer's analysis of a query. This page starts out with a short example of how to use the tools. A more detailed explanation of the tools follows the example.

These tools were introduced by work on DERBY-6211.

Example

You load the optimizerTracing tool as follows:

call syscs_util.syscs_register_tool( 'optimizerTracing', true, 'xml' );

Then you issue the queries which you want to trace. When you are done tracing queries, you unload the tool as follows...

call syscs_util.syscs_register_tool( 'optimizerTracing', false, 'myTrace.xml' );

...where myTrace.xml is the name of the file which will hold the trace output. You can then browse the trace file with your favorite tool. I like using the Firefox browser because it lets me expand and collapse xml elements in order to highlight important information and suppress noise.

At this point, you can load the optimizerTracingViews tool, which lets you search and filter the trace output using SQL. The tool installs the planCost view (backed by a table function). By querying this view, you can display the plan shapes which the optimizer considered and the costs which the optimizer calculated for those plans. Here's how you load this tool...

call syscs_util.syscs_register_tool( 'optimizerTracingViews', true, 'myTrace.xml' );

...where myTrace.xml is the name of the trace output file dumped by the optimizerTracing tool. A sample query against planCost might look like this:

select distinct summary, estimatedCost from planCost
where complete and qbID = 1
order by summary;

When you are done querying plan shapes and costs, you unload the planCost view as follows:

call syscs_util.syscs_register_tool( 'optimizerTracingViews', false );

The rest of this web page explains these tools in greater detail.

Concepts

Before describing the tools, it's useful to understand a couple concepts which are important to the Derby optimizer:

XML Elements

The optimizerTracing tool produces xml output which includes the following important elements:

A plan summary (pcSummary element) looks like this:

( ( "SYS"."SYSSCHEMAS_HEAP" * "SYS"."SYSTABLES_INDEX1" ) * "SYS"."SYSCOLUMNS_INDEX1" )

It shows how the optimizables join to each other, including the details of the chosen decorations. Tables appear as schema-qualified conglomerate names. The * operator means NestedLoop and the # operator means HashJoin. Parentheses indicate the left-deep nesting of the plan.

The planCost View

The optimizerTracingViews tool creates a planCost view on the optimizer trace file. The rows in the view represent information from the planCost elements in the trace file. That view has the following shape:

Sample Script

Here is a sample script showing how to use these tools:

connect 'jdbc:derby:trunk/generated/toursdb/toursdb';

call syscs_util.syscs_register_tool( 'optimizerTracing', true, 'xml' );

select * from cities, countries
where cities.country_iso_code = countries.country_iso_code
and 1=2
order by city_name;

select *
from sys.sysschemas s, sys.systables t, sys.syscolumns c
where s.schemaid = t.schemaid
and t.tableid = c.referenceid
and 1=2
order by s.schemaname, t.tablename, c.columnname;

call syscs_util.syscs_register_tool( 'optimizerTracing', false, 'myTrace.xml' );

call syscs_util.syscs_register_tool( 'optimizerTracingViews', true, 'myTrace.xml' );

select distinct summary, estimatedCost from planCost
where complete and qbID = 1
order by summary;

call syscs_util.syscs_register_tool( 'optimizerTracingViews', false );

XmlOptimizerTracing (last edited 2013-12-13 18:11:00 by RichardHillegas)