Oracle Optimizer: Moving to and working with CBO - Page 4
July 28, 2003
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.
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: