Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (nike.apache.org: domain of rick.hillegas@oracle.com
 designates 156.151.31.81 as permitted sender)
Message-ID: <5130AC87.4070107@oracle.com>
Date: Fri, 01 Mar 2013 05:26:31 -0800
From: Rick Hillegas <rick.hillegas@oracle.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.7; en-US;
 rv:1.9.2.18) Gecko/20110616 Thunderbird/3.1.11
MIME-Version: 1.0
To: derby-dev@db.apache.org
Subject: Re: Is there a way to dump all the plans considered by optimizer
 into log?
References: 
 <CAFy7pk-ZY_xPQHTjUpnC-rB-aEfpyecVdjDtGnoWvwBg=vUF+A@mail.gmail.com>
	<86zjyoolo2.fsf@oracle.com>	<5130075D.8030509@gmail.com>
 <CAFy7pk9XmzWKDeF2X-j7OFKYUbTr=efFBcamrMEK3Y+khgWNkQ@mail.gmail.com>
In-Reply-To: 
 <CAFy7pk9XmzWKDeF2X-j7OFKYUbTr=efFBcamrMEK3Y+khgWNkQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
> <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
>>