View the Exhibit1 to examine the series of SQL commands. View the Exhibit2 to examine
the plans available in the SQL plan baseline. The baseline in the first row of the Exhibit is
created when OPTIMIZER_MODE was set to FIRST_ROWS. Which statement is true if the
SQL query in exhibit1 is executed again when the value of OPTIMIZER_MODE is set to FIRST_ROWS?
parameter-optimizer (exhibit):
A.
The optimizer uses a new plan because none of the plans in the exhibit2 are fixed plans.
B.
The optimizer uses the plan in the second row of the exhibit2 because it is an accepted
plan.
C.
The optimizer uses the plan in the first row of the exhibit2 because it is the latest
generated plan.
D.
The optimizer uses the plan in the first row of the exhibit2 because OPTIMIZER_MODE
was set to FIRST_ROW during its creation.
Explanation:
Setting the OPTIMIZER_MODE Initialization Parameter(Link)
The OPTIMIZER_MODE initialization parameter establishes the default behavior for
choosing an optimization approach for the instance.
OPTIMIZER_MODE Initialization Parameter Values
ALL_ROWS, The optimizer uses a cost-based approach for all SQL statements in the
session regardless of the presence of statistics and optimizes with a goal of best throughput
(minimum resource use to complete the entire statement). This is the default value.
FIRST_ROWS_n, The optimizer uses a cost-based approach, regardless of the presence of
statistics, and optimizes with a goal of best response time to return the first n number of
rows, where n equals 1, 10, 100, or 1000.
FIRST_ROWS, The optimizer uses a mix of cost and heuristics to find a best plan for fast
delivery of the first few rows.
Note that using heuristics sometimes leads the optimizer to generate a plan with a cost that
is significantly larger than the cost of a plan without applying the heuristic. FIRST_ROWS is
available for backward compatibility and plan stability; use FIRST_ROWS_n instead.
I would like to note that the order of these exhibits has been switched according to the text.
The explanation given is not very relevent here.
The leason here (I think) is that only “accepted baseline plans” can be used regardless.