db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Simon <simonzm...@gmail.com>
Subject Execution Plan Changes Dramatically between Executions
Date Sat, 03 Jul 2010 04:04:43 GMT
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