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: Why is the optimizer choosing such a bad path
Date Mon, 01 Apr 2013 18:55:55 GMT
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/derby-dev@db.apache.org/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 <BBergquist@canoga.com> 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 [rick.hillegas@oracle.com]
> Sent: Friday, March 29, 2013 2:10 PM
> To: derby-dev@db.apache.org
> 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
>
>

Mime
View raw message