How to Correct Wrong Execution Path

  • by

SQL statements including queries and DML sometimes execute with the different index because of the following reasons:

Missing index

The target index might be dropped accidentally by someone else. Therefore, the optimizer have no choice but to use the wrong index instead, which may not match your expectations.

You should check the existence of desired index, then create an appropriate index for your statements if necessary. If you can’t remember the members of the missing index, you can run SQL tuning or SQL access adviser to find out.

Low sampling percentage

Low sampling rate does not always mean inaccurate in terms of statistics. If the table is pretty uniform from head to toe, low sampling rate, say 5%, may be enough to make the optimizer choose the right plan. But mostly, 5 to 10 percent may not be good enough to give the optimizer confidence to use right index.

In such case, you should analyze the segment with a higher sampling rate or fully compute (100%) the segment directly with cascade option. But the question is how high estimate percentage is high enough?

Inaccurate statistics

Sometimes, even compute (100%) cannot save the execution path. You may rebuild the target index then analyze it again. The optimizer will prefer the newly reorganized index.

If the above actions fail to correct the path, you may add hint in the statement to force the query to execute the right index. Of course, perform a SQL Tuning will be helpful.

New indexes

One situation may sound weird, a new index come into play to affect the optimizer. Generally, it should perform better than usual if the optimizer choose it. For example, an new index offers “Index range scan” access path is better than used “Index skip scan”. Right? You can make the judge.

More access paths can be found at official documentation: Optimizer Access Paths.

Leave a Reply

Your email address will not be published. Required fields are marked *