I have a Baseline Explain Plan for a batch procedure which should be followed by the optimizer to have the best performance for that procedure.
(The baseline was taken for the procedure when performing best.)
Today when I saw the procedure was running abnormally slow, I checked the Explain Plan being followed by the optimizer in the current scenario.
Every step of the Explain Plan matched with that of the baseline except one wherein instead of the intended normal index , some other bitmap index was being used . I dropped that bitmap index just to see if there was any difference but I found the optimizer went for some other bitmap index.
I gave a hint to use teh intended index but still it is not getting used.
I dropped and recreated teh intended index but no help.
I analyzed all the associated tables but still no help.
1. init.ora has a new parameter "star_transformation_enabled" set to true while this was false earlier.
This is the only change in init.ora
2. Hint had table alias specified
3. Indexes the optimizer is trying to look for are always bitmap and they are sole column indexes.
4. Data change is not at all significant.
The next Explain Plan being followed has abnormally high cost and it may take days to complete the execution.