Oracle Database 12c: Performance Management and Tuning — Question 12
You plan to upgrade your production database from Oracle Database 11g to 12c. As part of the upgrade, you want to introduce new indexes and materialized views. You have already created a test system with Oracle Database 12c, having the same structure and data as the production database, along with new schema objects to be added to the production database.
You want to identify regressed SQL statements, if any, which may have been caused by schema changes and the change in the optimizer version.
Which two methods would you use to achieve this? (Choose two.)
Answer options
- A. Create an SQL Tuning Set (STS) for the SQL statements on the production database and submit as input to the SQL Tuning Advisor on the test database.
- B. Create an STS for the SQL statements on the production database and submit as input to the SQL Performance Analyzer with the parameter first set to 11.2.0.1, and then to 12.1.0.1 on the test database. OPTIMIZER_FEATURES_ENABLE
- C. Generate an Automatic Workload Repository (AWR) compare periods report with snapshots taken before and after schema changes on the test database.
- D. Capture the production database workload, replay it on the test system by using Database Replay, and analyze by using the workload replay compare period report.
- E. Create an STS for the SQL statements on the production database and submit as input to the SQL Access Advisor on the test database.
- F. Create an STS for the SQL statements on the production database before and after changes and submit as input to the SQL Performance Analyzer on the test
Correct answer: A, D
Explanation
Option A is correct because creating an SQL Tuning Set and using the SQL Tuning Advisor can help identify potential performance issues with the SQL statements. Option D is also correct as capturing and replaying the workload allows for a direct comparison of performance before and after the changes. The other options either do not specifically target regressed SQL statements or involve tools that are less suitable for this analysis.