Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

March 21, 2007

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

Filed under: Oracle How To,Oracle Performance — H.Tonguç Yılmaz @ 5:56 pm

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 9.2.0.7.0

Refences Used :
n/a

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: