Invoking the Cost-Based Optimizer
Oracle Tips by Burleson Consulting
Before retrieving any rows, the cost-based
optimizer must create an execution plan that
determines the access method to access the desired table and
indexes. Just like the RBO, the cost-based optimizer works by
weighing the relative “costs” for different access paths to the
data, and choosing the path with the smallest relative cost. Once
the statistics have been collected, there are three ways to invoke
the cost-based optimizer:
Setting the init.ora parameter optimizer_mode =
all_rows, first_rows or choose
alter session set optimizer_goal=all_rows or
Cost based hints /*+ all_rows */ or --+
These “costs” for a query are determined with the aid
of table and index statistics that are computed with the analyze
table and analyze index commands in Oracle.
Gathering statistics for the CBO
There is a debate raging in the Oracle
community about how frequently to re-analyze statistics. Some Oracle
Remote DBAs feel that it is important that the statistics are refreshed
periodically, especially when the distribution of data changes
frequently, while other feel that there is only one optimal way to
service a query and the execution plan should never change.
Tip: The Oracle Remote DBA needs to choose
their SQL tuning philosophy. If they feel that they want their
execution plans to change, then they should re-analyze statistics
frequently. If they feel that there is only one optimal execution
plan for any query, then they will tune the SQL and use optimizer
plan stability and stop refreshing statistics.
There are two approaches for re-analyzing
statistics, custom scripts and the dbms_stats utility. Many Remote DBAs
write a quick SQL*Plus script to gather optimizer statistics. The
following script will generate the proper SQL syntax.
# First, we must set the environment . . . .
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2
set pages 999
set heading off
set echo off
set feedback off
'analyze table ‘||owner||’.'||table_name||' estimate statistics
sample 5000 rows;'
owner not in (‘SYS’,’SYSTEM’,’PERFSTAT’);
'analyze index ‘||owner||’.'||index_name||' compute statistics;'
owner not in (‘SYS’,’SYSTEM’,’PERFSTAT');
set echo on
set feedback on
Most shops schedule a script like this to run weekly,
or whenever there have been significant changes to the table data.
However, it is not necessary to re-analyze tables and indexes that
remain relatively constant. For example, a database where the tables
have a constant number of rows and indexes where the distribution of
values remain constant will not benefit from frequent analysis.
Another option is to use the dbms_stats
utility. This utility has the following options:
gather_database_stats – This gathers statistics for
the entire instance
gather_schema_stats – This gathers statistics for a
schema within an instance.
gather_index_stats – This gathers statistics for
indexes in a schema
gather_table_stats – This gathers statistics for
tables in a schema
The only real advantage of this package is
that you can also use parallelism to analyze statistics. Let’s take
a quick look at how the dbms_stats package is invoked. The following
query will analyze table statistics for a huge table, using 35
parallel query slaves.
Next, let’s examine the rules for determining the default optimizer
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.