What is cost-based optimization?
In the early days of databases, computers followed a rigid set of rules to find data. But as data grew from megabytes to petabytes, those rules became obsolete. Enter Cost-Based Optimization (CBO): the sophisticated "brain" that makes Oracle one of the most powerful databases in the world.
If you’ve ever wondered why a query runs in one second today but takes a minute tomorrow, the answer usually lies within the CBO.
Cost-Based Optimization is a method used by the Oracle Database to determine the most efficient way to execute a SQL statement. Instead of following a fixed list of priorities, the CBO evaluates multiple "execution plans" and assigns a Cost to each one.
The Goal: Find the plan with the lowest cost.
The Definition of "Cost": In Oracle terms, cost is a numeric value that represents the estimated resource usage (specifically I/O, CPU, and Memory) required to perform the operation.
The CBO doesn't just guess; it acts like a high-speed simulator. It uses three primary inputs to calculate the cost of a plan:
This is the most important input. Oracle looks at metadata stored in the Data Dictionary, such as:
Cardinality: How many rows are in the table?
Selectivity: How "picky" is your filter? (e.g., Searching for GENDER='M' returns 50% of the table, while SSN='123' returns 0.0001%).
Block Counts: How many physical "pages" of data must be read from the disk?
The CBO compares the cost of different ways to get the data:
Is it cheaper to scan the Entire Table (High I/O, low CPU)?
Is it cheaper to use an Index (Low I/O, but potentially more "jumps" between index and table)?
The CBO even considers the hardware it's running on. It looks at the speed of your CPUs and the "seek time" of your disks to determine if a specific operation will be "expensive" in real-world time.
Think of the CBO as Google Maps:
The RBO (Rule-Based) was like a paper map that said, "Always take the highway."
The CBO is the GPS that says, "Usually the highway is faster, but there is a 5-mile traffic jam (high cost), so I'll divert you through the side streets (low cost)."
If the "side street" (Full Table Scan) is actually faster than the "highway" (Index) because of the specific data you are looking for, the CBO will choose it.
The CBO is a genius, but it can be "fooled" by bad information. This is known as the "Garbage In, Garbage Out" problem.
Stale Statistics: If your statistics say a table has 100 rows, but it actually has 100 million, the CBO will choose a plan meant for a tiny table. This is the #1 cause of performance issues.
Complex Expressions: If you use complex math or functions in your WHERE clause, the CBO might struggle to estimate how many rows will be returned.
| Feature | Cost-Based (CBO) | Rule-Based (RBO) |
| Philosophy | "What is the cheapest way?" | "What is the highest-ranked rule?" |
| Data Awareness | High (Uses Statistics). | None (Blind to data size). |
| Adaptability | Changes as data grows. | Stays the same forever. |
| Modern Status | The Industry Standard. | Obsolete (Deprecated). |
As a developer or DBA, your job is to give the CBO the best possible information:
Gather Statistics: Use DBMS_STATS.GATHER_TABLE_STATS regularly.
Use Constraints: Primary keys and Foreign keys help the CBO understand the relationships between tables.
Avoid Hints (Usually): Don't try to "outsmart" the CBO with hints unless you have exhausted all other tuning options.
The Cost-Based Optimizer is the reason Oracle can handle massive workloads without manual tuning for every single query. By understanding the "cost" of I/O and CPU, it ensures that your application stays fast as your data grows.