Why does the optimizer not use the second plan?

A user session executes a query and the plan for the query is shown in the Exhibit as Plan-1. After
verifying with the SQL Access advisor, an index is created on the table in the JOB_ID column and
the query is executed again. A new plan is generated against the second query shown in the
Exhibit as Plan-2. When explaining the plan for the second query, you observe that the optimizer
uses the first plan instead of the second. The following parameters are set for the user session:
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE
OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE
SQLTUNE_CATEGORY=DEFAULT
Why does the optimizer not use the second plan?

A user session executes a query and the plan for the query is shown in the Exhibit as Plan-1. After
verifying with the SQL Access advisor, an index is created on the table in the JOB_ID column and
the query is executed again. A new plan is generated against the second query shown in the
Exhibit as Plan-2. When explaining the plan for the second query, you observe that the optimizer
uses the first plan instead of the second. The following parameters are set for the user session:
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE
OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE
SQLTUNE_CATEGORY=DEFAULT
Why does the optimizer not use the second plan?

A.
because the second plan is not verified

B.
because the second plan is not part of the SQL plan baseline

C.
because the SQLTUNE_CATEGORY parameter is set to DEFAULT

D.
because the explain plan option in the user session does not allow the new plan because
SQLTUNE_CATEGORY parameter is set to DEFAULT



Leave a Reply 1

Your email address will not be published. Required fields are marked *


L. Zhu

L. Zhu

A newly generated plan has to be verified before it can be used.

So A is correct