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 Fri, 20 Aug 2010 03:04:32 GMT
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!


View raw message