How Oracle optimizer may decide between an index or table scan access path

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;

Code listing 72 : optimizer decision between index or full table access demo

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

Here Oracle trace files are your friends, use them, they will show you all information you need during problem investigation;
10046 Event explaination
10053 Event explaination

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

Refences Used :

Leave a Comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s