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
