There is a short answer and a very long answer to this. I will try and keep the answer somewhere in the middle of short and long.
The first assumption we must accept and from my observations over the past several years, most Oracle developers don’t understand the Cost Based Optimizer, yet they use it everyday.
Way back in the dark ages, full table scans were evil and we used hints to get rid of this dreaded access path. As you can see from the bellow query, we got a full table scan of t1
So we solve this by adding a hint.
That’s better, we think but lets look at a few things. Did the hint really help? The first query had 70,308 consistent gets, 3,884 physical reads and cost was 1,065. The second query where we thought we were being cleaver had 1,057,564 consistent gets, 5868 physical reads and the cost was 990K.
Well, maybe I was not so cleaver by forcing Oracle to use an index, maybe the CBO is smart enough to put together the correct plan and the full table scan is not evil after all.
A few years ago I was at ODTUG and heard Maria Colgan speaking about the CBO and I was hooked. Maria made me realize I need to understand what the CBO was doing. Yes sometimes the CBO gets it wrong, but there are ways to correct that.
I’m now putting together a series of presentations on understanding the CBO using the scientific approach. Why not just publish how the CBO works? Why not publish ROT (Rule Of Thumb) for the CBO. Well the answer to that is quite simple. Every environment is different and it’s much better to understand your data and how the CBO works with your data and queries. There will be a presentations that will go through how to read an explain plan, however the other presentations will teach you how to establish what the CBO is doing in your environment and you will be able to explain it. That makes you smarter and more valuable.
Stay tuned, I will be posting blurbs and hints on the CBO.