You need to upgrade you Oracle Database 10g to 11g

You need to upgrade you Oracle Database 10g to 11g. You want to ensure that the same SQL
plans that are currently in use in the 10g database are used in the upgraded database initially, but
new, better plans are allowed subsequently.
Steps to accomplish the task:
1. Set the OPTIMIZER_USE_SQL_BASELINE and
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE to TRUE.
2. Bulk load the SQL Management Base as part of an upgrade using an STS containing the plans
captured in Oracle Database 10g.
3. Evolve the plan baseline using the DBMS_SPM.EVOLVE_PLAN_BASELINE procedure.
4. Fix the plan baseline – using the DBMS_SPM.ALTER_SQL_PLANBASELINE procedure.
5. Accept new, better plans using the DBMS_SPM.ALTER_SQL_PLAN_BASELINE procedure
and manually load them to the existing baseline.
6. Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to FALSE.
Identify the required steps.

A.
1, 3, 4, 5

B.
1, 6, 3, 4, 5

C.
1, 2, 3, 5

D.
1, 2, 3, 4

E.
1, 6, 3

F.
1 and 2

Explanation:
* (1) OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
In Oracle Database 11g a new feature called SQL Plan Management (SPM) has been introduced
to guarantees any plan changes that do occur lead to better performance. When
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is set to TRUE (default FALSE) Oracle will
automatically capture a SQL plan baseline for every repeatable SQL statement on the system.
The execution plan found at parse time will be added to the SQL plan baseline as an accepted
plan.
* (2) Once you have completed the software upgrade, but before you restart the applications and
allow users back on the system, you should populate SQL Plan Management (SPM) with the 10g
execution plans you captured before the upgrade. Seeding SPM with the 10g execution plans
ensures that the application will continue to use the same execution plans you had before the
upgrade. Any new execution plans found in Oracle Database 11g will be recorded in the plan
history for that statement but they will not be used. When you are ready you can evolve or verify
the new plans and only implement those that perform better than the 10g plan.
Incorrect:
Not (3): DBMS_SPM.EVOLVE_PLAN_BASELINE is not used to evolve new plans.
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE should be used:
It is possible to evolve a SQL statement’s execution plan using Oracle Enterprise Manager or by
running the command-line function DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE. U
Note:
* SQL plan management (SPM) ensures that runtime performance will never degrade due to the
change of an execution plan. To guarantee this, only accepted (trusted) execution plans will be
used; any plan will be tracked and evaluated at a later point in time and only accepted as verified
if the new plan performs better than an accepted plan. SQL Plan Management has three main
components:
1. SQL plan baseline capture:
Create SQL plan baselines that represents accepted execution plans for all relevant SQL
statements. The SQL plan baselines are stored in a plan history inside the SQL
Management Base in the SYSAUX tablespace.
2. SQL plan baseline selection
Ensure that only accepted execution plans are used for statements with a SQL plan
baseline and track all new execution plans in the history for a statement as unaccepted
plan. The plan history consists of accepted and unaccepted plans. An unaccepted plan
can be unverified (newly found but not verified) or rejected (verified but not found to

performant).
3. SQL plan baseline evolution
Evaluate all unverified execution plans for a given statement in the plan history to
become either accepted or rejected