For information on how to produce an xml-formatted trace of your SQL statements, see XmlOptimizerTracing.

You can trace the optimizer's analysis, one statement at a time. Optimizer tracing is enabled by this command:

After enabling tracing, issue the query you want to trace. E.g.:

Finally, turn tracing off and dump the trace. By default, the trace is printed on standard out. However, you can also force the trace to go into a file. Here's how you disable tracing and print the trace on standard out...

...and here's how you disable tracing, printing the trace to the file z1.txt:

The tracing option explained above is available in 10.10 release. Prior to 10.10 release, one can use following undocumented way to enable optimizer tracing.

Define two stored procedures in the user application.

And then surround the sql statement that needs to be traced with call trace() before the statement to trace, and printTrace() after the statement to trace as shown below:

OptimizerTracing (last edited 2013-12-13 18:04:57 by RichardHillegas)