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.