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 Fri, 01 Mar 2013 02:36:26 GMT
If we do decide to make this available as part of Derby release, the
work can go in as part of existing jira DERBY-837.

thanks,
Mamta

On Thu, Feb 28, 2013 at 5:41 PM, Bryan Pendleton
<bpendleton.derby@gmail.com> wrote:
>> 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()");
>
>
> Might we consider permanently including these, and putting them in, say,
> the SYSCS_DIAG schema, so it would be something like:
>
>         s.execute("call SYSCS_DIAG.OPTIMIZER_TRACE(1)");
>         ...
>         s.execute("call SYSCS_DIAG.OPTIMIZER_PRINT_TRACE()");
>         s.execute("call SYSCS_DIAG.OPTIMIZER_TRACE(0)");
>
> thanks,
>
> bryan
>

Mime
View raw message