Tuesday, 20 August 2013

Oracle Switching Plan of Execution to Wrong Index

Oracle Switching Plan of Execution to Wrong Index

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.

No comments:

Post a Comment