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










The Optimizer Modes

Oracle Tips by Burleson Consulting

Within these two optimizers, Oracle provides several optimizer modes. An optimizer mode may be set for the entire database, or it may be set for a specific Oracle session. The optimizer modes include rule, first_rows, all_rows and choose. Let’s take a closer look at each of these goals.

Rule mode

The rule hint will ignore the CBO and the statistics and generate an execution plan based solely of the basic data dictionary information. It is always a good idea to try tuning a specific SQL statement by using the RBO by using a rule hint or by issuing the alter session set optimizer_goal=rule. However, always remember that the RBO may choose a less than ideal index to service a query.


The choose optimizer mode allows Oracle to choose the most appropriate optimizer goal. This is the default optimizer mode within Oracle, and it generally uses the presence of statistics to determine which optimizer to invoke. If no statistics exist, Oracle will use the rule goal. If statistics exist, Oracle will choose first_rows or all_rows, depending on the presence of indexes and the setting for parallelism and db_file_multiblock_read_count.

Warning: Having incomplete statistics with the choose optimizer mode can cause huge slowdowns. If Oracle detects a single table in a query with statistics, it will use the cost-based optimizer, and compute statistics for the other tables at run-time. This can cause huge slowdown of SQL execution. This problem is very common with Oracle Remote DBA beginners because the default mode is choose, and because beginner Remote DBAs sometimes selectively issue the analyze command, not realizing that they are effecting SQL execution times.

first_rows mode

This is a cost-based optimizer mode that will return rows as soon as possible, even if the overall query runs longer or consumes more resources. The first_rows optimizer mode usually involves choosing a full-index scan over a parallel full-table scan. Because the first_rows mode favors index scans over full-table scans, the first_rows mode is most appropriate for online systems where the end userwants to see some results as quickly as possible.

all_rows mode

This is a cost-based optimizer mode that ensures that the overall query time is minimized, even if it takes longer to receive the first row. This usually involves choosing a parallel full-table scan over a full-index scan. Because the all_rows mode favors full-table scans, the all_rows mode is best suited for batch-oriented queries where intermediate rows are not required for viewing.

Which is “better”? Fast execution vs. minimal resource usage.

To understand Oracle’s philosophy regarding fast execution and minimal resource consumption we need to look at a simple example. To illustrate the difference between all_rows and first_rows with an over-simplistic example, consider the following query:

order by

This query can be serviced in two ways:

  •  Full-table scan -  Here we perform a full-table scan in parallel and sort the rows in the TEMP tablespace. For the sake of illustration, let’s assume that this execution plan produces the fastest overall execution time and minimal use of resources (Figure 2).

Figure 2: Perform a full-table scan and a sort (all_rows)

  • Full Index scan -  Here we retrieve the rows in last_name order by using a last_name_index. This technique results in more physical reads, but begins to return sorted rows almost immediately (Figure 3).

Figure 3: Perform an index retrieval (first_rows)

Again, assuming that we accept the full-table scan and sort is less I/O intensive than the full-index scan, then we can clearly see the difference in the optimizer goals. The full-index scan will begin to return rows almost immediately at the expense of extra I/O, while the full-table scan will require less resources, but the result set will not be available until the end of the query.

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

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



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