We are recently experiencing a tremendous query slowdown with spilled over temp tablespace. A specific query causes this problem.
The queried table (
table3) has an indexed PK, three FK with indexes and a compound unique constraint on the three FKs. The offensive query looks like this:
SELECT ... FROM table1 t1, table2 t2, table3 t3 WHERE t1.abs_id = ? AND t3.vgs_id = t1.vgs_id AND t3.ai_id > ? AND t2.id = t1.t2_id AND t2.status = 2 AND t2.felddimension = 0 ...
Only instance restart solved the issue. Even killing connections did no help.
After futher investigation on the FKs and the indexes, it turned out that the index on the
t3.ai_id column causes the severe drop in performance. After disabling this one the unique constaint served the query extremely fast.
The problematic part is
AND t3.ai_id > ? (range scan). Unique scan does not cause any trouble.
Now the question is, how can an index cause such a slowdown and moreover, how can I investigate the cause? It simply doesn't add up for me.
Competitive times: normal 10 s, slowdown > 2 min or never returning.
EDIT 1 (2013-06-05): Upon Jack Douglas' and Chris Saxon's advises, I have ran stats and then performed explain plan, I have made a giant leap forward.
I have calcucated schema stats with and without the index. Regardless whether the index is available or not, the optimizer uses the 3-field-composite-unique-index making the query extremely fast.
Here's the explain plan from SQL Developer:
So far so good, now I have added a hint into the query to use the bad index, and the explain plan is:
Now, this is extremely slow. But why?