db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: Is there a way to dump all the plans considered by optimizer into log?
Date Thu, 28 Feb 2013 09:03:09 GMT
Mamta Satoor <msatoor@gmail.com> 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

Mime
View raw message