Oracle Switching Plan of Execution to Wrong Index

1
votes

We had a performance issue in our production environment.

We identified that Oracle was executing queries using a Index which is not correct.

The queries have in their WHERE CLAUSE all the columns of the Primary Key (and nothing else).

After rebuilding of Index and Gather Statistics, Oracle started using the PK_INDEX. And the plan of execution indicated Index Unique Scan.

It worked fine for a while and then Oracle started using the Wrong Index again. The index that it uses now comprise of 2 Columns of which only 1 appears in the WHERE CLAUSE of the query. Now the plan of execution indicates INDEX RANGE SCAN and the system is very slow.

Please let me know how we could get to the root of this issue.

1
oracleoracle10gdatabase-performancequery-performance
Try gathering stats again. If you get the "right" execution plan then you need to set up a periodic stats gathering job. If you still get the "wrong" execution plan you'll have to post some more info. – haki
@haki Thanks for your comment. I gathered statistics again and the plan of execution seems to be using the right index at the moment. What I am not sure is how long will this last. Just within a gap of 11 hours the execution plan changed. Within this 11 hour there was nothing else done related to Indexes and the Database default Gather Stat JOB is also disabled. There was not a whole lot of record insertions/Updates etc either. Please guide. – Guddu

1 Answers

4
votes

Try gathering stats again. If you get the expected execution plan then it means that the changes made to the table since the last stats gathering made oracle think the least favorite execution plan is better.

so, You'r question here is really "How can I maintain plan stability ?"

You have several options

  1. Use hints in your query to indicate the exact access path.
  2. Use outlines

I personally don't like these two approaches because if your data will change in the future in such a manner that the execution plan should change, you'll get lousy performance.

So the third option (and my personal favorite) is

  1. enable periodic statistics gathering. Oracle knows to spot the changes and incrementally update relevant stats.