When you first begin your journey into Oracle performance tuning, you quickly discover that you’re not just working with databases, you’re deciphering a complex system of rules, statistics, and estimations. I was reminded by Connor McDonald in a post he made on LinkedIn about one of the most influential turning points in my career as a database professional came when I encountered Wolfgang Breitling’s 2003 paper – A Look Under the Hood of the CBO: the 10053 Event. At a time when I was focused on tuning queries for speed, Breitling’s work redefined how I thought about cost, and more importantly, how Oracle thinks about cost.
Click through for more information. Although SQL Server’s cost estimation process is different, the underlying reasoning and many of the mechanisms involved are the same.
Has anyone done a TRUE CBO comparison?
Is the CBO the only reason product X is faster at Y?
Of course not.
Its more about whose algorithm politics floated/charged to the top of past hiring decisions by developers, mathematician’s and product managers at these two in the 1990s, no?
Not really, because of the DeWitt Clause: https://en.wikipedia.org/wiki/David_DeWitt#:~:text=In%20essence%2C%20a%20DeWitt%20Clause,the%20behest%20of%20Larry%20Ellison.
Basically, Oracle licensing prohibits you from publishing benchmarks other than ones they officially approve of.
Databricks eliminated that clause: https://www.databricks.com/blog/2021/11/08/eliminating-the-dewitt-clause-for-database-benchmarking.html
But it’s not just Oracle that includes this clause: https://www.brentozar.com/archive/2018/05/the-dewitt-clause-why-you-rarely-see-database-benchmarks/
Now, if you’re talking about different trade-offs within a given cost-based optimizer, then yes, there are teams that do these things. I haven’t met any of the people at Oracle who work on these adjustments, but have met some of the Microsoft people. The challenge that anybody in that situation runs into is that there are very few universal rules, so any adjustment you make will have negative consequences for some subset of operations. This, for example, is why Microsoft still has cost threshold for parallelism set to 5, a number I personally consider too low and automatically set to something more like 25-50. There are enough circumstances (according to the people who build the tests) in which having a lower threshold for parallelism improves overall query performance, and so they keep that default.