The Value of Cost: Oracle’s Optimizer

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.

The Misunderstanding of “Cost”

Like many Oracle professionals early in their tuning journey, I used to equate cost with response time. If a query had a high cost, I assumed it would be slower. If I could reduce the cost, I assumed the performance would improve. But this oversimplification, while occasionally helpful, often led me down rabbit holes of wasted time.

That’s when I found Breitling’s paper, “A Look Under the Hood of CBO: The 10053 Event” and you can still find the paper and slide decks from this valuable talk today if you search.  For me, it was a revelation.  When I met Wolfgang years later, as well as subsequent group events as part of Oak table and the Oracle community, I don’t think he understood the impact his work had on my own career, but that happens to many of us.

Understanding the Math Behind the Optimizer

Breitling’s work breaks down what the Oracle optimizer actually does when it calculates the “cost” of a query. He guides readers through the internals of how Oracle uses object statistics, histograms, selectivity, cardinality, join order, and access paths to determine the most efficient plan.  It’s not in terms of time, but in terms of resource usage estimates.

He introduced me to the use of the 10053 trace event, which outputs the internal decision-making process of the optimizer.  Its essentially a peek behind the curtain, where this trace shows how different plans are evaluated and how costs are computed using CPU, I/O, and memory estimates.

For me, it was like switching from watching shadows on the wall to stepping outside and seeing the mechanics of the real world.

From Tuning Just for Time to Tuning for Understanding

Armed with the insights from Breitling’s paper, I began to see performance tuning in a new light. Instead of blindly rewriting SQL or creating indexes and checking if the elapsed time improved, I started to ask:

• What does the optimizer think the cardinality will be?

• Are the statistics stale or misleading?

• Why did it choose a nested loop over a hash join?

• Is this high cost actually a problem, or just misunderstood?

I learned to trace the query, read the plan, and compare estimated vs. actual rows. And yes, I still cared about response time-  that’s what the user feels and if you’re not tuning for time, you’re wasting time, but it’s essential that we understand the cost was a measure of estimated work, not elapsed time.

Cost-Based Doesn’t Mean Always Right

Breitling also pointed out that the optimizer is only as good as the information it’s given. If statistics are stale or skewed, if histograms are missing, if cardinality estimates are wildly off, or we’ve confused the optimizer with nested views and confusing joins, then the CBO’s decision-making is compromised.

This emphasized the importance of accurate and representative statistics, monitoring query performance over time, and understanding how features like bind variable peeking or adaptive plans affect real-world behavior. 

Even with newer Oracle versions (we’re well beyond 10g now), the core principles described in that 2003 paper still hold true. Oracle has introduced many enhancements, such as adaptive query optimization, automatic statistics gathering, and SQL Plan Management.  The truth is, at its core, the CBO is still a cost-driven engine that must be understood on its own terms.

Breitling’s paper didn’t just give me tools, it gave me a mindset. It helped me move from reactive tuning to proactive performance architecture. It taught me how to think like the optimizer.

In Summary

If you’re tuning queries and feel like you’re “wasting time” trying to reduce cost without understanding why it’s high or what it even represents, I highly recommend reading Wolfgang Breitling’s 2003 paper. It might be dated in version, but it’s timeless in wisdom.

The Oracle Cost-Based Optimizer is a powerful tool, but only if you know how to read it. For me, that education started with one deeply insightful document that turned confusion into clarity.