Oracle keeps changing execution plan
Posted on 2011-09-08
I have a piece of code in Oracle that since 2 days ago is now taking approx x10 longer to run i.e. 2mins -> 20 mins.
It appears, looking at the explain plan, that Oracle is forcing the code down the wrong path. The columns exist within the code for the optimizer to use an index but it refuses. Instead it does a 'bitmap conversion to rowids' or 4 partitions worth of data approx 8mill rows.
I have added the index hint to no avail. The weird thing is that every now and again when I check the plan it looks absolutely fine. I rerun it and it goes back to bit map conversion. Recent stats have been gathered on the index, I have changed the code around to try and force the optimizer to use the index but still no joy.
Has anyone experience of or know why Oracle is now all of a sudden choosing to take the wrong road as it were?
All help is greatly appreciated, this is having a very large, very negative impact.
/* Explain Plan
1 Some of the entries in the bitmap index Partitions determined by Key Values were accessed.
2 The bitmaps returned from steps 1 were converted to rowids.
3 Rows from table Partitions determined by Key Values were accessed using rowid got from a local (single-partition) index.
4 A range of partitions of steps 3 were accessed..
5 One or more rows were retrieved using index Partitions determined by Key Values. The index was scanned in ascending order..
6 A range of partitions of steps 5 were accessed..
7 For each row retrieved by step 4, the operation in step 6 was performed to find a matching row.
8 Rows from table TABLE1 were accessed using rowid got from a local (single-partition) index.
9 WINDOW SORT PUSHED RANK
10 A view definition was processed, either from a stored view SCHEMA or as defined by steps 9.
11 Rows were returned by the SELECT statement.