Oracle optimizer has always been a major performance test area during migrations. Release to release Oracle changes the code but these changes may have some unexpected outcomes.
Usually the problems are locked at the inefficient queries, but be careful inefficient queries are not the reasons, usually they are the outcomes. The reasons are usually wrong or missing statistics or database parameters.
I prepared a small demonstration based on one of Mr.Jonathan Lewis‘s presentation to show how optimizer is affected from some basic characteristics of your queries like data distribution and database parameters;
As a conclusion if you have a performance problem inefficient queries are easy to leave the guilt, but if you are a scientific person you must go some steps further, why are these queries running with inefficient execution plans?
The reason might be related to;
– missing selective indexes ,
– missing or wrong gathered statistics ,
– data distribution or row migration on the table ,
– wrong or default left optimizer database parameters
Understanding the optimizer may take some years and after all still with a new release everything you know must be retested usually :) But if you want to start this long journey here are my advices ;
Watch and study this presentation
Read and study these papers
Follow this blog
Read and study this book
Testing Information : the scripts mentioned are tested on Oracle Database 9i Enterprise Edition Release 22.214.171.124.0
Refences Used :