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 2006/01/19 09:23:19 UTC
Enabling optimizer trace
Hi,
All the recent optimization related work on Derby inspired me to see if we
can have the optimizer send information about various plans that it
considered for a query into derby.log This hopefully will help debugging
optimizer bugs.
After some looking through the code, it looks like there is already code
which can put the optimization phase information in
GenericLanguageConnectionContext if optimizer trace is turned on. For
instance,
FromBaseTable.nextAccessPath calls optimizer.Trace with some tracing
information. optimizer is instanceof Level2OptimizerImpl which in it's trace
method checks via GenericLanguageConnectionContext.getOptimizerTrace to
see if optimizer tracing is on and if yes, it puts the tracing information
in a string varaible in GenericLanguageConnectionContext. The optimizer
trace switch in GenericLanguageConnectionContext gets switched on by
org.apache.derby.iapi.db.OptimizerTrace's static method setOptimizerTrace.
So looks like, we have the code to do the optimizer tracing but no property
or any other mechanism to trun that tracing on. We can decide to introduce a
new property which would be used to enable optimizer tracing. Something
similar to what we do for *derby.language.logStatementText. *Once Derby
will detect this property, we will need to add the code to dump all the
information gathered during the optimization phase into derby.log
My only concern is Derby seems to generate lot of optimizer tracing output
and that can clutter derby.log
I hacked the code by hardcoding the optimizer tracing switch to true in
GenericLanguageConnectionContext and adding the code to dump all the
optimization information into derby.log after the optimization phase. With
these change, a simple select * from t1 query (with no indexes/primary key
on t1) caused derby.log to have lot of optimization phase information in it.
Attached is derby.log for this query.
Wondered what do folks think about this optimizer tracing?
Mamta
Re: Enabling optimizer trace
Posted by Mamta Satoor <ms...@gmail.com>.
BTW, I have made a JIRA entry for this. The number is Derby-837. I have
marked it as a new feature. Another option could have been
improvement. Hopefully new feature is the right criteria, even if this will
be used only by developers and not a documented feature. Also, for the
Component, I was thinking of marking it as Performance but it didn't sound
right. So, for now, I have left it as Unknown.
Mamta
On 1/19/06, Jeffrey Lichtman <sw...@rcn.com> wrote:
>
>
> >I am curious about your statement "This tracing feature is intended
> >for developers who are debugging optimizer problems. I don't believe
> >it should ever be exposed as a supported feature....".
>
> As soon as you expose a feature you have to support it, which means
> documentation, compatibility issues, user handholding, etc. Since the
> optimizer trace output doesn't mean anything to someone who doesn't
> know the guts of the optimizer, I would say we're better off just
> letting developers know it exists.
>
>
> - Jeff Lichtman
> swazoo@rcn.com
> Check out Swazoo Koolak's Web Jukebox at
> http://swazoo.com/
>
>
Re: Enabling optimizer trace
Posted by Jeffrey Lichtman <sw...@rcn.com>.
>I am curious about your statement "This tracing feature is intended
>for developers who are debugging optimizer problems. I don't believe
>it should ever be exposed as a supported feature....".
As soon as you expose a feature you have to support it, which means
documentation, compatibility issues, user handholding, etc. Since the
optimizer trace output doesn't mean anything to someone who doesn't
know the guts of the optimizer, I would say we're better off just
letting developers know it exists.
- Jeff Lichtman
swazoo@rcn.com
Check out Swazoo Koolak's Web Jukebox at
http://swazoo.com/
Re: Enabling optimizer trace
Posted by Mamta Satoor <ms...@gmail.com>.
Hi Jeff,
I realize that we are still discussing what is the correct way of presenting
the optimizer trace. But I am curious about your statement "This tracing
feature is intended for developers who are debugging optimizer problems. I
don't believe it should ever be exposed as a supported feature....". It is
likely that the optimizer trace might seem like Greek to an end user who
doesn't know the internals of Derby or query optimization in general but to
a more aware user, the optimizer trace might be handy to do some little
research of their own before trying the derby developer list.
thanks,
Mamta
On 1/19/06, Jeffrey Lichtman <sw...@rcn.com> wrote:
>
>
> >Would be great if we put some thought into this, rather than enabling a
> >somewhat messy output. It would be great if we came up with an XML
> >format that would allow easier compatible changes in the future.
> >
> >Dan.
>
> This tracing feature is intended for developers who are debugging
> optimizer problems. I don't believe it should ever be exposed as a
> supported feature or used in the Derby tests. There may still be
> reasons for creating an XML format, but I would prefer not to put
> hurdles in the way of creating simple development tools.
>
>
> - Jeff Lichtman
> swazoo@rcn.com
> Check out Swazoo Koolak's Web Jukebox at
> http://swazoo.com/
>
>
Re: Enabling optimizer trace
Posted by Jeffrey Lichtman <sw...@rcn.com>.
>Would be great if we put some thought into this, rather than enabling a
>somewhat messy output. It would be great if we came up with an XML
>format that would allow easier compatible changes in the future.
>
>Dan.
This tracing feature is intended for developers who are debugging
optimizer problems. I don't believe it should ever be exposed as a
supported feature or used in the Derby tests. There may still be
reasons for creating an XML format, but I would prefer not to put
hurdles in the way of creating simple development tools.
- Jeff Lichtman
swazoo@rcn.com
Check out Swazoo Koolak's Web Jukebox at
http://swazoo.com/
Re: Enabling optimizer trace
Posted by Daniel John Debrunner <dj...@apache.org>.
Satheesh Bandaram wrote:
> Hi Mamta,
>
> Thanks for trying to get optimizer trace working... I think this is a
> useful tool, though it might generate lots of information. Unwanted
> information can be filtered out easily, so I would enable this as is.
> Any other improvements can be taken up later.
Would be great if we put some thought into this, rather than enabling a
somewhat messy output. It would be great if we came up with an XML
format that would allow easier compatible changes in the future.
Dan.
Re: Enabling optimizer trace
Posted by Daniel John Debrunner <dj...@apache.org>.
Satheesh Bandaram wrote:
> Rick Hillegas wrote:
>
>
>>For all of our diagnostic output, there's probably a big payoff in
>>moving to some more structured format like xml. That will help
>>client-side (and even off-the-shelf) tools format the output and
>>filter out unwanted information.
>
>
> I agree... Current diagnostic tools could be improved. Generating in XML
> does help processing and visualizing the info. DB2, for example, has a
> good way to display query plans and costs and I think this can be saved
> in XML format. Current output of DumpOptimizedTree is huge and hard to
> follow for even mid sized query.
>
> But since I have immediate need for optimizer trace output , I was
> suggesting enabling it in current form before attempting to make it
> better. Also would give us a chance to use and understand what it is
> before making it better.
If you have an immediate need for it and it's a developers' tool then
it's already available, hack the code to enable it.
Creating a property to enable it instantly makes it a feature of the
product, this is open source, the code is open, users can see the
functionality available. And if there is a property to enable it then
it's just a matter of time before someone asks a customer of theirs to
run it at their site to diagnose a customer's problem.
Dan.
Re: Enabling optimizer trace
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Rick Hillegas wrote:
>
> For all of our diagnostic output, there's probably a big payoff in
> moving to some more structured format like xml. That will help
> client-side (and even off-the-shelf) tools format the output and
> filter out unwanted information.
I agree... Current diagnostic tools could be improved. Generating in XML
does help processing and visualizing the info. DB2, for example, has a
good way to display query plans and costs and I think this can be saved
in XML format. Current output of DumpOptimizedTree is huge and hard to
follow for even mid sized query.
But since I have immediate need for optimizer trace output , I was
suggesting enabling it in current form before attempting to make it
better. Also would give us a chance to use and understand what it is
before making it better.
Satheesh
Re: Enabling optimizer trace
Posted by Rick Hillegas <Ri...@Sun.COM>.
Satheesh Bandaram wrote:
> Hi Mamta,
>
> Thanks for trying to get optimizer trace working... I think this is a
> useful tool, though it might generate lots of information. Unwanted
> information can be filtered out easily, so I would enable this as is.
> Any other improvements can be taken up later.
>
> Current diagnostic properties like DumpOptimizedTree also print huge
> amount of information. It would be nice to be able to control level of
> detail being generated.
For all of our diagnostic output, there's probably a big payoff in
moving to some more structured format like xml. That will help
client-side (and even off-the-shelf) tools format the output and filter
out unwanted information.