db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Execution Plan Changes Dramatically between Executions
Date Sat, 03 Jul 2010 15:55:17 GMT
Do any of the tables in the select have very few rows?  If so
a few updates/inserts could change the statistics a lot.

I would suggest running an offline compress on all the tables
in the query and see if the issue still happens.  This will update
all the statistics that the optimizer uses when estimating, including
optimizing the indexes on all the tables.

In the worst case, as a workaround you could use the optimizer hints
to force the use of indexes to match the optimized plan if this does
not get rid of the problem.

Simon wrote:
> Hello all,
> 
> I have an application which executes the following query:
> 
> select tp.ID, tp.NAME, tp.DESCRIPTION,
> (select count(*) from test_plan rtp, test_plan_instance rtpi, script rs
>   where tp.id = rtp.id and rs.TEST_PLAN_INSTANCE_ID = rtpi.ID
>   and rtpi.TEST_PLAN_ID = rtp.ID and rs.STATUS = 'Running'
> ) as running_count,
> (select count(*) from test_plan rtp, test_plan_instance rtpi, script rs
>    where tp.id = rtp.id and rs.TEST_PLAN_INSTANCE_ID = rtpi.ID
>    and rtpi.TEST_PLAN_ID = rtp.ID and rs.STATUS = 'Pending'
>   ) as pending_count
>   from test_plan tp
> 
> Most of the time it works great, indexes get used and it is fast
> enough (it is a report so it is not necessary to be instant).
> However sometimes it runs really slowly.  It goes from taking 140ms to
> taking 147 seconds!    I tried logging the query plans and I see that
> on the times when it is running slowly it is using a completely
> different execution plan.   I don't fully understand the plans but it
> is very clear that certain indexes just stop getting used and it does
> scans instead, eg:
> 
> scan information:
> 	Bit set of columns fetched={0, 1}
> 	Number of columns fetched=2
> 	Number of pages visited=726
> 	Number of rows qualified=0
> 	Number of rows visited=102870
> 	Scan type=heap
> 
> Since the data set does not change in any significant way between
> executions (maybe a few updates, an insert or two) and the query is
> static I'm really curious why it would change its query plan,
> especially to something that is so dramatically worse.
> 
> Any hints on how I can avoid this?
> 
> NB:  Using Derby 10.5.3.   You can see the full execution plans:
> 
> http://badboysoftware.biz/test/fast.log
> http://badboysoftware.biz/test/slow.log
> 
> Thanks for any help!
> 
> Simon
> 


Mime
View raw message