SQL Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 213

The higher the cost, the longer the expected execution time will be. 2 Once the cost of each plan is determined,
the optimizer will select the plan that has the lowest cost. This plan is then used during execution.
The various plans generated by the optimizer can be obtained using the 10053 event trace discussed in Chapter 6.

Optimizer Goals
Optimization of any task should have a goal and purpose. Normally computer-based optimization goals are targeted
to provide high throughput or good response times. Oracle's optimizer is no exception; optimization is either for an
OLTP workload where the quick response time is expected or a DSS (decision support system) workload where data is
inserted or queried in larger volumes and where high throughput is expected. In certain cases in which the database
has small organizations, the optimizer uses the same database to support both types of workload or a mixed workload.
By default the optimizer uses “throughput” as its optimizer goal; and by using different conditions, the goal could
be changed. The factors that affect the optimizer goal are

• Database initialization parameter

OPTIMIZER_MODE
The manually forced Optimizer plan modification used in the query to change the execution •

path of the optimizer

• Optimizer statistics

OPTIMIZER_MODE
The OPTIMIZER_MODE initialization parameter establishes the default optimizer behavior of the instance when
generating execution plans. The parameter accepts one of the three values:

ALL_ROWS : This is the default optimizer mode. When the database is created using DBCA
(database configuration assistant), the OPTIMIZER_MODE defaults to ALL_ROWS . It attempts
to optimize the query to get the very last row as fast as possible. Regardless of whether
statistics are present, the statement is optimized for the best throughput, that is, to use the
minimum amount of all available resources to complete the operation. For example, we
take the following query:

SELECT n_name,
r_name,
p_name,
s_name,
SUM(ps_supplycost)
FROM rapdwh.region,
rapdwh.nation,
rapdwh.supplier,
rapdwh.partsupp,
rapdwh.part
WHERE ps_partkey = p_partkey
AND ps_suppkey = s_suppkey
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey

2 This does not apply to parallel operations where resource use is not directly related to the elapsed time.