053 Question No. 246

246.The optimizer_use_plan_baselines parameter is set to TRUE. The optimizer generates a
Plan for a SQL statement but does not find a matching plan in the SQL plan baseline.
Which operations is performed by the optimizer in this scenario? (Choose.)
A. The optimizer adds the new plan to the plan.
B. The optimizer selects the new plan for the execution of the SQL statement.
C. The optimizer adds the new plan to the SQL plan baseline as an accepted plan.
D. The optimizer adds the new plan to the SQL plan baseline.
E. The optimizer costs each of the accepted plans in the SQL plan baseline and picks the one with the
Lowest cost.
Answer: AE

The plan history was the set of plans, both accepted and not accepted, which the optimizer generates for a SQL stat Ement over time. Because only accepted plans is in the SQL plan baseline, the plans in the baseline form a susbset of the plan history. For example, after the optimizer generates the first acceptable plan for a SQL plan baseline, subsequent plans is part of The plan history is not part of the plan baseline.

The process of adding plans to a SQL plan baseline is plan Evolution. To being eligible to be evolved, a plan must is enabled for use by the optimizer.

During The SQL plan baseline selection phase, Oracle Database detects plan changes based on the stored plan history, and S Elects plans to avoid potential performance regressions for a set of SQL statements.

Each time the database compiles a SQL statement, the optimizer does the following:

2. Uses a cost-based search method to build a best-cost plan

4. Tries to find a matching plan in the SQL plan baseline

5. Does either of the following depending on whether a match is found:

   • if found, then the optimizer proceeds using the matched plan

   • If not found, then the optimizer evaluates the cost of each accepted plan in the SQL plan baseline and selects the plan with the lowest cost

the Best-cost plan found by the optimizer, does not match any plans in the Plan history F Or the SQL statement represents a new plan. The database adds this plan as a nonaccepted plan to the plan. The database does not use the new plan until it was verified to not cause a performance regression. However, if a change in the system (such as a dropped index) causes all accepted plans to become non-reproducible, then th E Optimizer selects the Best-cost plan. Thus, the presence of a SQL plan baseline causes the optimizer to use conservative plan selection strategy for the SQL STA Tement.

To enable the use of SQL plan baselines, set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to TRUE (default).

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

053 Question No. 246