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
Correct Answer: F
Explanation/Reference:
* (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_BASELINEis not used to evolve new plans.DBMS_SPM.EVOLVE_SQL_PLAN_BASELINEshould 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
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
