Part 1 of this article discussed how Oracle begins arriving at an execution plan. In Part 2, we explore how Oracle uses the rule-based optimizer (RBO) and the cost-based optimizer (CBO) to decide on an execution plan.
Comparing the RBO and CBO
There are two optimizers within the Oracle RDBMS. Both produce execution plans. The rule-based optimizer (RBO) is the original optimizer to the Oracle database. The cost-based optimizer (CBO) originated back in Oracle7 but became more popular as databases increased in size.
- RBO follows a set of rules mostly based on indexes and types of indexes.
- CBO uses statistics and math to make an educated guess at the lowest cost. CBO processes multiple iterations of explain plans (called permutations). CBO picks the one with the overall lowest cost.
Oracle gives the CBO a SQL statement and gets cost numbers and an execution plan back. Oracle then shuffles the code, trying different combinations of tables and where clause predicates in an attempt to find the lowest overall cost. The SQL with the lowest cost is perceived to be the fastest executing SQL.
CBO: processes regular queries looking for the overall lowest cost.
CBO: partitioned queries look for the fastest elapse time.
The RBO produced an execution plan with a single pass of the SQL text. The time spent hard parsing was not the issue back in the day. The library cache was smaller and maintaining a high hit ratio of the SQL that had been hard parsed was more of the issue.
The CBO takes longer to hard parse so it is a good practice to use bind variables and other coding techniques to help Oracle find the SQL from prior executions rather than performing this hard parse task for every submitted SQL.
The CBO is a single code set that takes a SQL statement in and returns an execution plan and cost numbers.
The cost number on statement id 0 is the overall cost of the SQL being processed. It is this overall cost number that is compared to the additional cost numbers from the prior permutation. If the new overall cost is lower, the prior permutation is discarded and the current permutation becomes the ‘lowest cost’ SQL. This process is repeated until Oracle is satisfied that it has tried all combinations of tables and where clause predicates available including trying all three join types (if there are more than one table listed in the SQL).
Query transformations introduced in Oracle 9i
In addition to the code shuffling of the permutations, Oracle9i introduced query transformations or more aggressive internal rewrites of the SQL.
Oracle10g improved on these query transformations looking for the one that has the lowest overall cost.
Oracle does this as part of the permutation process searching for combinations of the SQL that will produce the lowest over all CBO cost.
The types of additional transformations that Oracle9i+ now performs include:
- Complex view merging (converts views to joins)
- Subquery unnesting (converts subqueries to inline views)
- Join predicate push down (moves WHERE clause predicates into subquery)
There are hints to enforce this behavior at each step mentioned or to inhibit the behavior altogether.
Each step of the execution plan produces a result set that is passed to the next step of the plan. These result sets are like temporary tables that are not visible. They are not indexed either.
If the next step is a join type, then it becomes the outer table.
The result set that arrives at statement id 0 is the final result of the execution of the SQL and the rows in here are then passed to the cursor area in the user’s program global area. Their application is alerted that the SQL is done executing and there are rows to be dealt with.
[…] Part 2 of this article will take a closer look at the RBO and CBO. […]