Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jul 28, 2003

Oracle Optimizer: Moving to and working with CBO - Page 4

By Amar Kumar Padhi

3.2.2 Available CBO Modes

CBO has two available modes in which to run, ALL_ROWS and FIRST_ROWS.

FIRST_ROWS aims at returning the first row(s) of the statement as soon as possible. This mode tells optimizer to give response time prime importance. It prefers nested-loop joins. FIRST_ROWS uses cost as well as some thumb rules to process the first set of rows. Examples of thumb rules - Plans using indexes are preferred over plans having full table scans as access path, ORDER BY clause can induce index access, etc.

As of release 9i, the number of rows to be returned in the first hit can also be mentioned in the parameter, FIRST_ROWS_n (n could be 1, 10, 100 or 1000). This could be set as per the application requirements.

ALL_ROWS processes all rows for a given query before returning the output. It forces optimizer to consider minimal use of resources and best throughput. ALL_ROWS prefers sort-merge joins.

For an OLTP system, FIRST_ROWS would be the ideal option for fast response time. ALL_ROWS is meant for batch processing applications. Note, a plan that produces the first n rows with the fastest response time might not be an optimal plan if requirement is to obtain the entire result, so decide as per the need of the application.

CBO is dynamic and tunes its execution plans as the database grows in size. So do not be taken aback if the same query that works perfectly in one database setup is behaving badly in some other database of the same application. This would happen if the setup and statistics differ between the two databases. To prevent such behavior, you may consider using optimizer plan stability, which is covered later in this series.

3.2.3 Basic CBO Terms

The following terms will be used quite often when analyzing statements in CBO.

The COST computed in CBO is a unit of expense involved with each operation. The logic as to how the cost is actually derived is not documented or made external. Moreover, this may change across releases.

The number of rows in the table or number of distinct row links in the index. The cardinality of a query is the number of rows that is expected to be returned by it.

The number of distinct values. The distinct values of a column being indexed are known as its selectivity. For example, if a table has 10000 rows and an index is created on a column having 4000 distinct values, then the selectivity of the index is (4000/10000) * 100 = 40%. Unique index on not null columns have a selectivity of 100%.

It is a process of generating additional predicates for a query by CBO. This enables optimizer to consider additional execution paths. For example if predicates are provided in query of the type A=B and B=C, the optimizer may add an additional predicate that indicates A=C.

Much required information gathered for various data holding objects. This information is vital for the CBO to decide on execution plans.

Join Methods
Oracle uses joins like Hash, sort-merge and nested loops. A query may run faster using one type of join as compared to other methods. This should be evaluated for individual queries.

FTS or Full Table Scan relates to a query sequentially scanning a table from the first block to the last allocated block. This could be very expensive for big tables and should be avoided.

Index scan
Relates to random access of a table by use of one or more indexes on the table.

3.2.4 Minimum requirement

To start using CBO the minimum requirement is to set the optimizer mode to FIRST_ROWS or ALL_ROWS (or CHOOSE) and generate statistics for the objects. However, this will not ensure that your system is working at its best. Please refer to part 2 (Initialization parameters) for information regarding related initialization parameters.

Irrespective of the Optimizer mode settings, CBO is automatically invoked if one of the following is satisfied:

  • If hints are used.
  • If table is partitioned.
  • If tables are set for parallel.

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM