Cost-based optimizer (CBO) & Rule-based optimizer(RBO)
-------------------------------------------------------

PURPOSE
--------

Prior to O7.3 the CBO still behaves somewhat erratically by making poor
execution plan choices and actually slowing down the query compared to the
RBO implementation. The use of SQL HINTS is strongly encouraged to help
the CBO make "more informed" decisions about the choice of execution
plans. The general reaction of the audience was that CBO "has a long way
to go" before becoming a mature, reliable product. I would recommend that
you suggest to your customer that they approach the CBO carefully by
setting up various tests to compare RBO and CBO performance. You should
encourage the customer to begin experimenting with CBO and tell them that
by the time they upgrade to O7.3 the product will have matured into a
dependable tool that will produce quality execution plans. In my opinion,
some of the negative feedback from customers using CBO occurred because
they did not take the time to learn how it works and how best to use it.


Explanation & Example
----------------------

1.) The RBO will be supported in Oracle8, but will not be enhanced and
will eventually be desupported.

2.) PL/SQL V1 (which is the PL/SQL engine linked with most tools) is
basically an Oracle 6 product and does not contain all the logic to
support CBO. Only PL/SQL V2 understands the CBO concept of HINTs.

3.) The CBO does not understand the characteristics associated with an
application, nor can it fully understand the impact of complex
relationships between joined tables (the cost of the analysis would far
out-weigh the benefit). Therefore, hints may be required to ensure a
specific behavior ofthe CBO.

4.) The CBO is still relatively immature and (under some conditions)
improperly estimates the cost of some query components.

5.) The CBO assume evenly distributed values and therefore does not always
estimate selectivity correctly. In the case of bind variables,
selectivity is assumed to be some fixed value (maybe 25%?). Oracle 7.3
will address some of this by utilizing column histograms.

6.) Use of the LIKE predicate sometimes results in unexpected execution
plans. In particular, the CBO does not propagate LIKE-predicate
transitivity.

7.) Application queries should be tuned and monitored for performance
degradation, regardless of the optimizer type being used.

8.) All things being equal, the CBO will select a driving table working
from left-to-right in the FROM list. This is just the opposite of the
RBO behavior.

9.) By default, the CBO optimizes for throughput (overall resource
consumption), NOT response time. Response time should not be confused
with throughput.

10.) Generally speaking, interactive/Forms-based applications with canned
queries should be tuned for response time (OPTIMIZER GOAL=FIRST_ROWS).

11.) Observation of CBO behavior suggests that SORT-MERGE Joins are often
favored over NESTED-LOOP Joins. This has a negative impact on the
response time for the first row(s).

12.) Accurate statistics for all tables & indexes are a MUST for proper
behavior of the CBO. (The CBO will make hard-coded assumptions for
statistics that are not provided).

13.) If OPTIMIZER_MODE is set to CHOOSE, the CBO will be used if ANY of
the tables in the query has statistics. The CBO will optimize for
throughput, NOT response time.

14.) If OPTIMIZER_MODE is set to RULE, the RBO will be used regardless of
the presence of statistics.

15.) If OPTIMIZER_MODE is set to FIRST_ROWS or ALL_ROWS (or if the session
OPTIMIZER_GOAL is set to FIRST_ROWS or ALL_ROWS), then the CBO will be
used regardless of the presence of statistics. FIRST_ROWS is optimized
for response time, and ALL_ROWS has a goal of optimizing throughput.

16.) Whenever possible statistics should be updated by using the
ANALYZE...COMPUTE command (the ESTIMATE option had some problems in
earlier releases of Oracle7).

17.) Performing an ANALYZE operation on a table automatically cascades to
all related indexes.

18.) If tables are too large to allow a full ANALYZE... COMPUTE,
statistics should be updated with ANALYZE... ESTIMATE using at least 20-
25% of the table. A full ANALYZE... COMPUTE should be performed on the
INDEXES following the ANALYZE... ESTIMATE operation on the table.

19.) The ANALYZE command should be performed regularly on dynamic tables.
The frequency of ANALYZE operations should be commensurate with the
dynamics of the table.

20.) When formatting trace files using TKPROF, it is important to
understand that optionally selected EXCUTION PLANs are derived using the
current optimizer environment (the plans are NOT stored in the trace
file). Settings such as OPTIMIZER_MODE should be consistent with the
settings at the time the trace file was created.

21.) For complicated multi-table joins, the number of execution plan
permutations can be very large. Therefore, though Oracles documentation
might suggest otherwise, the CBO uses heuristics to estimate certain
costs and to prune execution plan options. (The order of the tables in
the FROM clause can therefore have an impact on the final execution plan
selected).

22.) PL/SQL blocks executed from SQLPLUS and PL/SQL stored procedures use
an optimizer goal of CHOOSE when the session has been altered to a goal of
FIRST_ROWS. I assume this is a bug. This prevents optimization for time
unless you provide a specific HINT.

23.) There are some known bugs associated with the optimizer goal of
FIRST_ROWS and queries that have ORDER BY -- in some cases the optimizer
may not use an available index in lieu of a SORT operation. (see bugs
211500, 247561)

24.) Since Oracle Forms uses the PL/SQL v1 engine, HINTs are stripped
from the SQL statement. There is a block property to supply a HINT.
Additionally, the session may be altered to optimize for response time
(FIRST_ROWS goal for all queries) by invoking the FORMS_DLL built-in:
FORMS_DLL(alter session set optimizer_goal=FIRST_ROWS).

25.) Optimizer Goal can be set at an instance level in both Oracle 7.0.16
and 7.1.4. Though the feature is not documented in 7.0.16, the paramter
OPTIMIZER_MODE=FIRST_ROWS can be used (but not officially supported).

26.) There is a bug associated with the ANALYZE ... ESTIMATE option that
sometimes results in division by zero errors. It appears that this
problem is most likely encountered when estimating statistics on smaller
tables.

27.) There is a bug in pre-7.1 releases of Oracle7 that can cause the CBO
to return no rows, but the RBO will correctly return n rows. The bug
number is 187592 and is a duplicate of 184630. Basically, queries with
outer joins involving more than 3 tables may return incorrect number of
rows when using the cost based optimizer. Use the ORDERED hint as a
possible work-around.

28.) Most of these issues relate to Oracle 7.0.16 and 7.1.4. My
understanding is that some of the CBO problems and BUGs are being fixed
in Oracle 7.2.

29.) Any suspected problems should be documented in an Oracle TAR. WWS
should open a BUG for any newly discovered problems. Problems that don't
get BUG'ed don't get fixed.

One other thing. If hints are used and are specified incorrectly (ie.
improper syntax) or the hint makes no sense (improper semantics) then the
hint is simply ignored without any error msgs or other feedback to the
user.


Reference Ducumment
---------------------