You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Mamta Satoor <ms...@gmail.com> on 2013/02/27 22:20:42 UTC
Is there a way to dump all the plans considered by optimizer into log?
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?
thanks,
Mamta
Re: Is there a way to dump all the plans considered by optimizer
into log?
Posted by Rick Hillegas <ri...@oracle.com>.
I added an optional, trailing filename argument to the command which
disables optimizer tracing. I linked the debugging tips wiki page to a
new page which explains how to use the new commands which toggle
optimizer tracing: http://wiki.apache.org/db-derby/OptimizerTracing
I also posed some questions about possible tracing improvements. See the
attachment comment for derby-6022-07-aa-optimizerTracingWithLogFile.diff
on https://issues.apache.org/jira/browse/DERBY-6022
Hope this is useful,
-Rick
Re: Is there a way to dump all the plans considered by optimizer
into log?
Posted by Bryan Pendleton <bp...@gmail.com>.
> I don't have strong feelings about whether we should document this tool in the user guides.
How about in the wiki?
thanks,
bryan
Re: Is there a way to dump all the plans considered by optimizer
into log?
Posted by Rick Hillegas <ri...@oracle.com>.
I have added an optional tool for turning on optimizer tracing and
dumping the traces to the console. This was patch
derby-6022-06-aa-optimizerTracing.diff, committed at subversion revision
1454537. Here's how you use this optional tool:
-- turn on optimizer tracing
call syscs_util.syscs_register_tool( 'optimizerTracing', true );
select columnname from sys.systables t, sys.syscolumns c where t.tableid
= c.referenceid and t.tablename = 'SYSALIASES';
-- dump the optimizer trace and turn off optimizer tracing
call syscs_util.syscs_register_tool( 'optimizerTracing', false );
I don't have strong feelings about whether we should document this tool
in the user guides.
Thanks,
-Rick
Re: Is there a way to dump all the plans considered by optimizer
into log?
Posted by Rick Hillegas <ri...@oracle.com>.
If we decide to make this part of the product, I would recommend using
the new optional tools feature rather than adding yet another syscs_diag
procedure. New optional tools are easier to add than new system
procedures. Something like the following would work:
-- this installs optimizer tracing
call syscs_util.syscs_register_tool( 'optimizerTracing', true );
...
-- the following procedure, installed by the call above,
-- might have some optional string varargs specifying how the output
-- should be formatted and where it should be written.
-- we could add those args in a later rev when we have more
-- experience with this tool.
-- but a first rev wouldn't need to be that fancy.
call printOptimizerTrace();
-- this turns off optimizer tracing
call syscs_util.syscs_register_tool( 'optimizerTracing', false );
I would recommend creating a new JIRA where we can discuss the api for
optimizer tracing.
Thanks,
-Rick
On 2/28/13 6:36 PM, Mamta Satoor wrote:
> 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
> <bp...@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
>>
Re: Is there a way to dump all the plans considered by optimizer into log?
Posted by Mamta Satoor <ms...@gmail.com>.
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
<bp...@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
>
Re: Is there a way to dump all the plans considered by optimizer
into log?
Posted by Bryan Pendleton <bp...@gmail.com>.
> 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
Re: Is there a way to dump all the plans considered by optimizer into log?
Posted by Mamta Satoor <ms...@gmail.com>.
Thanks, Knut.
On Thu, Feb 28, 2013 at 1:03 AM, Knut Anders Hatlen
<kn...@oracle.com> wrote:
> Mamta Satoor <ms...@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
Re: Is there a way to dump all the plans considered by optimizer into log?
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Mamta Satoor <ms...@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