Hi,
I was on vacation last week and hence could't participate in this
conversation earlier.
As Rick pointed out, DERBY-6022 has gone in as part of 10.10. But if
you are in need of doing optimizer tracing in a release prior to
10.10, you can try the suggestion postd by Knut in email thread("Is
there a way to dump all the plans considered by optimizer into log?")
at
http://www.mailinglistarchive.com/html/[email protected]/2013-02/msg00635.html.
I am copying Knut's suggestion below. I tried this on 10.9 and was
able to get all the optimizer plans considered by the optimzer for a
given query. You can then look at the details of each individual plan
to understand why optimizer picked a given plan.
**************************************************************
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()");
**************************************************************
On Mon, Apr 1, 2013 at 6:55 AM, Bergquist, Brett <[email protected]> wrote:
> I will be looking at this later in the week as of course my real work has
> interrupted :) Thanks for all of the valuable input!
>
> Brrett
> ________________________________________
> From: Rick Hillegas [[email protected]]
> Sent: Friday, March 29, 2013 2:10 PM
> To: [email protected]
> Subject: Re: Why is the optimizer choosing such a bad path
>
> On 3/29/13 9:30 AM, Mike Matrigali wrote:
>> On 3/29/2013 5:28 AM, Bergquist, Brett wrote:
>>> Mike I have a reproducible case but the database is 132GB so it is
>>> not practical. I am willing to dig in and try to help debug this.
>>> On the issue that was pointed to, Mamta seemed to have used a debug
>>> procedure that would print out more information on why the optimizer
>>> is choosing what it does. This might be useful in this case as well.
>>>
>>> So if someone can point me in the right area of the code to start
>>> looking and adding some debug that would be helpful. I have done
>>> some fixes for Derby in the past so I am able to build and debug Derby.
>>>
>> That's great. I am out of my depth debugging optimizer problems,
>> hopefully rick and/or mamta can help with that tool they were working
>> on. I think rick did some work to make it easier to turn on/off using
>> optional tools.
>>
> The simpler api to optimizer tracing is part of the 10.10.1 release
> candidate which we're testing now. Instructions on how to use it can be
> found in my 2013-03-14 comment on
> https://issues.apache.org/jira/browse/DERBY-6022
>
> Hope this helps,
> -Rick
>
>
