db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Simon <simonzm...@gmail.com>
Subject Re: Execution Plan Changes Dramatically between Executions
Date Sat, 21 Aug 2010 04:36:17 GMT
Thanks Bryan!

This was so obvious in retrospect - I was totally befuddled by the
"heisenbug" nature of it (problem goes away when you try to examine

I've made a rather crude workaround:  I calculate a random number N then add

WHERE <N> = <N>

to the end of the query - it prevents the statement from being cached
but doesn't seem to slow down the query at all.  Now I get good
performance all the time!

I'll see if I can wrap this up into a test case.   It does seem like
"correct" behavior in a technical sense, but the outcome is certainly
weird / undesirable so if there's something that can be done to make
the optimizer avoid plans that have such a poor worst case performance
(even if they are the "best" plan for the specific data at that
moment) then that would definitely be a plus.



On Sat, Aug 21, 2010 at 11:41 AM, Bryan Pendleton
<bpendleton.derby@gmail.com> wrote:
>> So the question is, why does the optimizer suddenly get much smarter
>> just because I restarted the database?  Is it caching the execution
>> plan perhaps?  Can I make it be this smart all the time somehow?
> Derby definitely caches the compiled form of the statement, and will re-use
> it.
> There is a way to completely disable that cache, I believe, check
> this bug report from some details:
> https://issues.apache.org/jira/browse/DERBY-4280
> Given the dramatic behavior changes in your situation, it would be
> awfully useful if you could package up whatever information you can
> about this and log it in the Derby Jira for future users benefit.
> Ideally, if you could contribute a test program which demonstrates
> the behavior that you describe, that would be great! But even things
> like query plans of the good and bad behavior, descriptions of the
> schemas, etc., can be of help.
> thanks,
> bryan

View raw message