Tuning with Cost-Based Optimization (CBO)
Oracle Tips by Burleson Consulting
The cost-based optimizer uses statistics that
are collected from the table using the analyze table and
analyze index commands. Oracle uses these metrics about the
tables in order to intelligently determine the most efficient way of
servicing the SQL query. It is important to recognize that in many
cases the cost-based optimizer may not always make the proper
decision in terms of the speed of the query, and Oracle has provided
numerous hints to allow the Remote DBA and developer to tune the execution
The cost-based optimizer is constantly being
improved, but there are still many cases the rule-based optimizer
will result in faster Oracle queries. As we stated before, one of
the first things a seasoned Oracle Remote DBA does when tuning an SQL
statement is to add a rule hint, or use the alter session
set optimizer goal = rule statement in order to change the
default optimizer mode from cost-based to rule-based optimization.
Here is a list of common hints that are used to change the execution
plan in the cost-based optimizer:
all_rows - This is the cost-based approach
designed to provide the best overall throughput and minimum resource
and_equal(table_name index_name1) - This hint
causes merge scans of 2 to 5 single column indexes.
cluster(table_name) - This hint
requests a cluster scan of the table_name.
first_rows - This is the cost-based approach
designed to provide the best response time.
full - This hint requests the bypassing
of indexes, doing a full-table scan.
hash(table_name) - This hint
causes a hash scan of table_name.
hash_aj - This hint is placed in a not in
sub-query to perform a hash anti-join.
index(table_name index_name) - This
hint requests the use of the specified index against the
table. If no index is specified, Oracle will choose the best index.
index_asc(table_name index_name) - This hint
requests to use the ascending index on a range scan operation.
index_combine(table_name index_name) - This
hint requests that the specified bitmapped index be used.
index_desc(table_name index_name) - This hint
requests to use the descending index on a range scan operation.
merge_aj - This hint is placed in a not in
sub-query to perform an anti-join
no_expand - The NO_EXPAND hint prevents the
cost-based optimizer from considering OR-expansion for queries
having OR conditions or IN-lists in the WHERE clause. Usually, the
optimizer considers using OR expansion and uses this method if it
decides the cost is lower than not using it. This OR expansion is
related to optimizer internals and does not mean that the logic
itself will be changed and return a different result set.no_merge
- This hint is used in a view to prevent it from being merged
into a parent query.
nocache - This hint causes the table cache
option to be bypassed.
noparallel - This hint turns off the parallel
ordered - This hint requests that the
tables should be joined in the order that they are specified (left
parallel(table_name degree) - For full
table scans, this hint requests that the table_name query be
executed in parallel mode with “degree” processes servicing the
push_subq - This hint causes all sub-queries in
the query block to be executed at the earliest possible time.
rowid - This hint requests a ROWID scan
of the specified table.
rule - This hint indicates that the
rule-based optimizer should be invoked (sometimes due to the absence
of table statistics).
star- This hint forces the use of a star query
plan, provided that there are at least three tables in the query and
a concatenated index exists on the fact table.
use_concat - This hint requests that a
union all be used for all or conditions
use_hash(table_name1 table_name2) - This hint
requests a hash join against the specified tables.
use_merge - This hint requests a sort
use_nl(table_name) - This hint requests
a nested loop operation with the specified table as the driving
It is beyond the scope of this chapter to go into all
of these hints, so for now, just consider hints to be the tools you
use to tune cost-based execution plans. We will look at these in
detail in later chapters.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.