Mamta Satoor <[email protected]> writes:
> Hi,
>
> I was wondering if there was a way to dump all the plans considered by
> the optimizer. I realize that it can be a huge output since optimizer
> can be going through many different plan options but it will be a good
> information to have when looking at why one plan was picked over the
> other. I am particularly looking for this info for DERBY-6045. Knut
> mentioned in DERBY-6011 that he enabled optimizer tracing to test out
> a script and compared two plans for a given query. I will look further
> into code to see how to enable optimizer trace but wanted to check on
> the list if Knut or anyone recalls on how to enable the optimizer
> tracing?
I didn't find any easy way to enable it (like setting a property). So
what I ended up with, was to define two stored procedures:
public static void trace() {
org.apache.derby.iapi.db.OptimizerTrace.setOptimizerTrace(true);
}
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 call trace() before the statement to trace, and printTrace()
after:
s.execute("call trace()");
s.execute("select 1 from sys.sysschemas natural join sys.systables");
s.execute("call print_trace()");
--
Knut Anders
