Differences between revisions 2 and 3
Revision 2 as of 2013-04-02 04:39:12
Size: 1897
Editor: MamtaSatoor
Comment:
Revision 3 as of 2013-12-13 18:04:57
Size: 2006
Comment:
Deletions are marked like this. Additions are marked like this.
Line 1: Line 1:
For information on how to produce an xml-formatted trace of your SQL statements, see XmlOptimizerTracing.

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:

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

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

  •  select columnname from sys.systables t, sys.syscolumns c where t.tableid = c.referenceid and t.tablename = 'SYSROLES'; 

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...

  •  call syscs_util.syscs_register_tool( 'optimizerTracing', false ); 

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

  •  call syscs_util.syscs_register_tool( 'optimizerTracing', false, '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.

  • public static void trace() { } public static void printTrace() {
    • System.out.println(org.apache.derby.iapi.db.OptimizerTrace.getOptimizerTraceOutput());

    } s.execute("create procedure trace() language java parameter style java external name '" + getClass().getName() + ".trace'"); s.execute("create procedure print_trace() language java parameter style java external name '" + getClass().getName() + ".printTrace'");

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:

  • s.execute("call trace()"); s.execute("select 1 from sys.sysschemas natural join sys.systables"); s.execute("call print_trace()");

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