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 19:21:16 GMT
Also, I am thinking of updating the wiki page on optimizer
tracing(http://wiki.apache.org/db-derby/OptimizerTracing) with
following additional information for releases prior to 10.10. I will
go ahead and add following changes if there are no comments on the
content/wording of the text below. Also, I hope that it is ok to share
this undocumented behavior on the wiki.

*************************************************
The tracing option explained above is available in 10.10 release.
Prior to 10.10 release, one can use following undocumented way to
enable optimizer tracing.

Define two stored procedures in the user application.

    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 surround the sql statement that needs to be traced with call
trace() before the statement to trace, and printTrace() after the
statement to trace as shown below:

    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 11:55 AM, Mamta Satoor <msatoor@gmail.com> wrote:
> 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