Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

Fast query

So far so good, now I have added a hint into the query to use the bad index, and the explain plan is:

Slow query

Now, this is extremely slow. But why?

share|improve this question
1  
@JackDouglas No, you are right. I should have said that just adding an index doesn't hurt queries. But different execution plans (which may be affected by new indexes) might. About the restart solving the issue, no idea really. I leave this to Oracle experts. –  ypercube Jun 4 '13 at 16:00
1  
@ypercube if the OP was on 10g I'd blame bind peeking as restarting would clear out the cached execution plans. I think this is less likely with 11.2, but you can test it –  Jack Douglas Jun 4 '13 at 16:24
4  
To help answer your question, please post the explain plans for the good and bad plans; ideally using the gather_plan_statistics hint. –  Chris Saxon Jun 4 '13 at 20:45
1  
"Regardless whether the index is available or not, the optimizer uses the 3-field-composite-unique-index making the query extremely fast" in other words, the 'problem' was inaccurate stats rather than anything intrinsic about the index? Please stop calling it a "bad index"! When you hint the index, you force the CBO to use a sub-optimal plan. You could spend a long time trying to understand why the plan you are forcing is sub-optimal, but is it worth the effort now you have fixed the problem? –  Jack Douglas Jun 5 '13 at 15:42
2  
If you want to go down that path, the much more costly scan of ST_TD_VORGANGSDATEN jumps out at me from the slow plan. I also notice that you have not expanded all the joins so we can see what is beneath them. One last tip, there is a setting in SQL Developer to show the estimated cardinalities in the explain plan, these are very useful to see alongside the cost estimates. –  Jack Douglas Jun 5 '13 at 15:42

1 Answer 1

up vote 6 down vote accepted

The CBO will consider lots of different ways to execute your query, taking into account all the indexes and statistics on both tables and indexes.

If you disable an index, obviously the CBO then cuts out all the plans it might have considered that involved that index.

The question really boils down to: "why is the CBO choosing a poor plan", and the first thing to consider is whether your stats are accurately reflecting reality in your database. Is this the case? How do you gather stats, and how often on these tables and indexes?

If and only if the stats are good and the CBO is still choosing a bad plan, you'll need to start considering "why?", then you'll want to have a look at the plan, especially the estimated cardinalities for each step, and see which are badly off. In some cases this is because of correlations that standard stats cannot help with, but cross that bridge when you get to it :)

share|improve this answer
    
there is no recurring gathering of stats. How will that influence the execution plan? I am missing the link between the optimizer and the poor index-caused query. –  Michael-O Jun 4 '13 at 20:44
    
are you assuming that the index is the root cause of the problem? The CBO needs good stats to make good planning decisions: if it had good stats perhaps it would not chose the poor plan involving that index? –  Jack Douglas Jun 4 '13 at 21:34
    
Plan has been generated. Please see the update on the question. –  Michael-O Jun 5 '13 at 12:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.