Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Cost-Based Optimization and SQL Tuning
Oracle Tips by Burleson

Unless you have a brand-new system that was developed on Oracle8i, chances are that you've already had some experience with running both cost-based optimization and rule-based optimization together inside the same database. As the cost-based optimizer continues to improve, more and more SQL statements can be moved from rule-based optimization into cost-based optimization, and this is especially true in Oracle8i. However, it is not immediately obvious which of your SQL statements will benefit from a cost-based optimization approach, and there is no substitute for manually tuning and evaluating the elapsed speed of each query to find the fastest execution plan.

Prior to Oracle8i, most Oracle systems used the default optimizer_mode=rule and tuned individual SQL statements with cost-based hints to take advantage of the new Oracle8 features such as function-based and bitmap indexes. However, now that Oracle8i’s cost-based optimizer has a reach the point where it is compatible with rule-based optimization, many SQL tuning professionals are experimenting with using a cost-based default optimizer_mode and then individually tuning queries to achieve the fastest execution plans.

As I noted in our review of Oracle hints in Chapter 12, you have a huge number of options available to use to tune when using the cost-based optimizer. Some of the new features that we can take advantage of with cost-based optimization include bitmap indexes, function-based indexes, star query joins, materialized views, and a host of other new hints that are all designed to improve the response time of our Oracle SQL statements.

As I noted in Chapter 7, the first thing most SQL tuning professionals do when they encounter a suspect SQL program is to add a rule hint and see if the execution plan becomes more efficient. Even under cost-based optimization, this is still a very good approach to take because the rule-based optimizer in general will provide very good execution plans except when cost-based indexes are present. The RBO does not understand function-based or bitmap indexes and ignores them when developing an execution plan.

When running your database with a cost-based default, there are particular subsets of SQL queries that are the most important for your SQL tuning effort. Simple queries that select from a single table, or DML statements such as inserts and updates, are not as important as complex SQL queries. By complex, I mean those kinds of queries that join multiple tables together, including subqueries and queries that have very complicated Boolean predicates in the SQL where clause. These are the types of SQL statements that will benefit most from tuning.

While we have already covered the basic methods for tuning SQL statements in earlier chapters, it is helpful to categorize the cost-based SQL tuning options according to the type of access that is being performed. We generally see three classes of queries:

  • Cost-based multiple table join techniques (see also Chapter 16)
  • Cost-based subquery optimization techniques (see also Chapter 19)
  • Queries with complex Boolean predicates in the where clause

Within each of these areas, there are sets of rules that direct us to possible hints that can be applied to these classes of queries in order to improve the overall execution speed. As I noted over and over again in earlier chapters, the only way to tell for sure if a hint is going to improve the execution speed of the query to actually execute the query. Even with Oracle8i, costing estimates from the cost-based optimizer do not provide as accurate a measure of the real execution speed of the query as does actually running the query and timing the query elapsed time with the SQL*Plus set timing on command.

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 DBA and developer to tune the execution plan. The cost-based optimizer is constantly being improved, but there are still many cases where the rule-based optimizer will result in faster Oracle queries. One of the first things a seasoned Oracle DBA does when tuning a 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.

Before retrieving any rows, the cost-based optimizer must create an execution plan that tells Oracle the order in which to access the desired tables and indexes. The cost-based optimizer works by weighing the relative “costs” for the different execution paths to the data, and it chooses 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.

When we begin to tune a SQL statement with a cost-based optimizer default, the first step is to take a look at the suspect query and categorize the query according to the type of table access method. Once we've developed a pattern for each SQL statement, it becomes very easy to go to the select set of hints that may be able to improve the execution time for the query. To illustrate, the following sections will examine each one of the basic SQL models and explore the types of hints that can be used under cost-based optimization to improve the execution plan for the query.

Let’s take a look at each of these three categories of queries: table joins, subqueries, and complex Boolean expressions.

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


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



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

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter