1Z0-997-25 Question 90
Single answerYou are managing an Oracle Database on an OCI VM DB System. After deploying a new index to optimize reporting queries, you notice that a frequently executed transactional query has slowed down significantly. An AWR report shows that the query� execution plan has changed, indicating a plan regression. What is the best first step to resolve the performance issue while keeping the new index and minimizing downtime?
- A
Drop the newly created index to force the old execution plan back into use.
- B
Use SQL Plan Management (SPM) to create or evolve a baseline plan that avoids the regressed plan.
- C
Disable the optimizer to prevent it from using the new index until further testing is completed.
- D
Manually gather statistics again to remove the new index from the optimizer's consideration.
Show answer and explanation
Correct answer: B
Explanation
When a query shows a performance regression due to a plan change, especially after creating a new index, the recommended approach is to use Oracle Database features like SQL Plan Management (SPM). SPM allows you to capture the known good plan as a baseline and instruct the optimizer to use or evolve that plan. This preserves the new index for other queries and minimizes the impact on production. Refer to the Oracle Database Performance Tuning Guide for details on implementing and maintaining SQL Plan Baselines.
- A. Incorrect.
Dropping the index may resolve the performance regression temporarily, but it removes a potentially beneficial optimization for other queries. It is also more disruptive than necessary.
- B. Correct.
Using SQL Plan Management (SPM) to establish or evolve a baseline plan is the recommended practice. This enables you to control which execution plan the optimizer uses, avoiding the regressed plan while preserving the new index for queries that benefit from it.
- C. Incorrect.
Disabling the optimizer is extremely disruptive and would negatively impact all queries in the database, not just the one experiencing regression.
- D. Incorrect.
Regathering statistics alone does not guarantee the optimizer will exclude the new index. It might still choose a suboptimal plan. Proper plan management via SPM is more targeted and effective.