Performance Tuning   «Prev 

Reviewing the Oracle Cost Based Optimizer

Why calculate cost?

The entire basis of a cost-based optimizer is the simple fact that different access paths can require dramatically different (I/O) input/output costs.
To understand this better, assume that you have a simple query that joins two tables together
  1. BIGTABLE with 1,000 rows and
  2. LITTLETABLE with 10 rows.
For the purpose of this illustration, we can assume that there is only one row in BIGTABLE that can be joined to each row in LITTLETABLE.
If you access BIGTABLE first, the retrieval will first read a row in BIGTABLE and then read the associated row in LITTLETABLE. To clarify, that is one access for the BIGTABLE row and one access to retrieve the value of the join column in BIGTABLE with the value of the same column in LITTLE TABLE.

Oracle will perform this action 1,000 times, resulting in 2,000 accesses overall.
If you access LITTLETABLE first, the retrieval will read a row from LITTLETABLE and then the associated row in BIGTABLE, for a total of 20 accesses overall, or 100 times fewer rows than starting the query with BIGTABLE. Although the caching mechanism in Oracle will help diminish the effect of this dramatic difference, it is easy to see that the cost of starting the query access with BIGTABLE is much greater than the cost of using LITTLETABLE first.

Diagram that illustrates different input/output costs for different sized tables
Diagram that illustrates different input/output costs for different sized tables
To a rule-based optimizer, an index is an index is an index. The Oracle rule-based optimizer will choose an index based on a somewhat arbitrary rule, such as which index is mentioned first. The inflexible nature of rule-based optimization means that the way a query is written could result in significantly different performance characteristics. Or, even worse, that the same query could deliver significantly different performance characteristics as the actual composition of the database changes.