Thanks, Knut.
On Thu, Feb 28, 2013 at 1:03 AM, Knut Anders Hatlen <[email protected]> wrote: > 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
