BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation










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.



# First, we must set the environment . . . .
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
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;

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

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

spool off;

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 mode.

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



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter