Re: Execution Plan Changes Dramatically between Executions

[Simon <si...@gmail.com>]

Hi all,

I'm sorry to bump this ... but it's become quite a crippling issue for
my application so I really need to try and find an answer.   I have
figured out a little more about the problem.   Although the table in
question has a lot of rows, the slow performance is triggered by an
insert of a single row with a column value matching one of the columns
in the where clauses in the query.  When it is inserted, it is the
only row in the table with this value.   The execution plan does not
actually change, but the performance of it does dramatically.

The paradox is that if I stop the application (and database, since it
is embedded) and restart it then the optimizer comes up with different
values and query plan changes and everything is fast again.

So it does this:

1.  starts off fast using execution plan A
2.  a single insert happens, query continues using execution plan A (
but it is now horrifically slow)
3.  stop DB and start again, no change in database => now uses
execution plan B  (fast)

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?

Thanks for any help!

Simon

Re: Execution Plan Changes Dramatically between Executions

[Simon <si...@gmail.com>]

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
it!).

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.

Cheers,

Simon

On Sat, Aug 21, 2010 at 11:41 AM, Bryan Pendleton
<bp...@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
>

Re: Execution Plan Changes Dramatically between Executions

[Bryan Pendleton <bp...@gmail.com>]

> 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