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.
Cost
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.
Cardinality
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.
Selectivity
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%.
Transitivity
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.
Statistics
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
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.
» 
Comment and Contribute
