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 first_rows

• Cost based hints   /*+ all_rows */ or --+ all_rows

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.

analyze.ksh

#!/bin/ksh

# First, we must set the environment . . . .
ORACLE_SID=xxx
export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH

$ORACLE_HOME/bin/sqlplus /<<!

set pages 999
set heading off
set echo off
set feedback off

connect internal;

spool /export/home/oracle/analyze.sql;

select
'analyze table ‘||owner||’.'||table_name||' estimate statistics sample 5000 rows;'
from
   Remote DBA_tables
where
   owner not in (‘SYS’,’SYSTEM’,’PERFSTAT’);

select
'analyze index ‘||owner||’.'||index_name||' compute statistics;'
from
   Remote DBA_indexes
where
   owner not in (‘SYS’,’SYSTEM’,’PERFSTAT');

spool off;

set echo on
set feedback on

@/export/home/oracle/analyze
exit
!

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.

BEGIN
   dbms_stats.gather_table_stats
(
      ownname=SCOTT,
      tabname=huge_table,
      estimate_rows=5000,
      degree=35
);

Next, let’s examine the rules for determining the default optimizer mode.
 

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.