db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: Is there a way to dump all the plans considered by optimizer into log?
Date Thu, 28 Feb 2013 18:05:17 GMT
Thanks, Knut.

On Thu, Feb 28, 2013 at 1:03 AM, Knut Anders Hatlen
<knut.hatlen@oracle.com> wrote:
> 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