Why isn't the optimizer doing the right thing!?!?

The purpose of the Oracle Cost Based Optimizer is to quantify the different possible ways to execute a query and compare those options in order to figure out what the best way is to run your query. For even a small query, the options are vast...its a very difficult job and IMHO, the CBO does a great job when its supplied with enough accurate information about the problem.  That being said, there are times when its decisions are perplexing, even to people who feel they understand the CBO.

When you go to the optometrist and take a vision test, you run through multiple questions..."Which is clearer, left or right?"  After all these test your vision's clarity can be quantified to represent how clearly you see.  Even if your vision is perfect at "20/20", there are people who can see even more clearly than that, so vision is always relative.  This is how I think most technical knowledge is.  If somebody asks you, “Do you understand the CBO?”, you may think the answer is yes, and you may grasp the concepts very well...but *very* few people have 20/20 clarity on the topic. In vision like in complex topics like the CBO, understanding is relative.

To gain better vision into the CBO, the definitive reference is Jonathan Lewis. If you get a chance to hear him speak or read his books, do it. Wolfgang Breitling is another of my heroes.  I had the opportunity to meet him at Hotsos a few years back...he's brilliant, and like Jonathan Lewis, he approaches being a DBA from the perspective of a mathematician.

There have been many blogs and books by brilliant authors (like Jonathan Lewis, see link to the right for his CBO book) to answer the question, “Why isn't Oracle choosing the right plan?”.  I just want to point you to another source.  After spending years reading several books and tons of blogs...I gained new levels of clarity on the topic when I read Wolfgang Breitling's white paper:

A Look Under the Hood of CBO The 10053 Event

The copy I got a few years ago was very old at that time...but the vast majority of it-if not the formulas, than the concepts-still apply today.  In modern Oracle RDBMS versions with system stats, the rough time estimate cost in the CBO has switched from counting I/O's, to the amount of time needed to read a single block, so its a little more tightly coupled with the concept of time.

The idea behind the CBO is to quantify and compare multiple potential plans...so if its quantified in time or IO, it doesn't really matter...the result should be the same. Keep in mind, it does make it difficult to compare plan costs between older DBMS versions though.

When I get a chance, I'm planning to revisit the concepts of his 10053 analysis a bit on the Exadata platform to see what's changed...I'll post anything interesting I see.