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…

Question

Options

• A1, 3, 4, 5
• B1, 6, 3, 4, 5
• C1, 2, 3, 5
• D1, 2, 3, 4
• E1, 6, 3
• F1 and 2

• A
  2% (1)
• B
  7% (4)
• C
  3% (2)
• D
  15% (9)
• E
  2% (1)
• F
  72% (43)

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 * (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. 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 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

No community discussion yet for this question.