OCP 1Z0 053 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 two operations are performed by the optimizer in this scenario? (Choose two.) 
A. The optimizer adds the new plan to the plan history. 
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 but not in the ENABLED state. 
E. The optimizer costs each of the accepted plans in the SQL plan baseline and picks the one with the 
lowest cost. 
Answer: AE 

优化器捕获的plan不会直接进入baseline，而是进入plan history，只有当比较后cost最低的才会进入baseline


http://docs.oracle.com/cd/E11882_01/server.112/e41573/optplanmgmt.htm#PFGRF95115

15.2.2 Selecting SQL Plan Baselines

During the SQL plan baseline selection phase, Oracle Database detects plan changes based on the stored plan history, and selects 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:

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

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

3. 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 that does not match any plans in the plan history for the SQL statement represents a new plan. The database adds this plan as a nonaccepted plan to the plan history. The database does not use the new plan until it is 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 the 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 statement.

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

15.2.3 Evolving SQL Plan Baselines

During the SQL plan baseline evolution phase, the database evaluates the performance of new plans and integrates plans with better performance into SQL plan baselines.

When the optimizer finds a new plan for a SQL statement, the database adds the plan to the plan history as a nonaccepted plan. The database can verify the plan for performance relative to the SQL plan baseline performance. A successful verification of a nonaccepted plan consists of comparing its performance to that of a plan selected from the SQL plan baseline and ensuring that it delivers better performance. When the database verifies that a nonaccepted plan will not cause a performance regression, the database changes it to an accepted plan and integrates it into the baseline.