You perform the following activities during the database upgrade from Oracle Database 10g
to Oracle Database 11g:
Capture plans for a SQL workload into a SQL Tuning Set (STS) before upgrade.
Load these plans from the STS into the SQL plan baseline immediately after the upgrade.
What is the reason for performing these activities?
A.
to minimize plan regression due to the use of a new optimizer version
B.
to completely avoid the use of new plans generated by a new optimizer version
C.
to prevent plan capturing when the SQL statement is executed after the database
upgrade
D.
to keep the plan in the plan history so that it can be used when the older version of the
optimizer is used
Explanation:
15.1.1 Purpose of SQL Plan Baselines (Link)
Common scenarios where SQL plan management can improve or preserve SQL
performance include:
A database upgrade that installs a new optimizer version usually results in plan changes for
a small percentage of SQL statements. Most of these plan changes result in either no
performance change or improvement. However, some plan changes may cause
performance regressions. SQL plan baselines significantly minimize potential regressions
resulting from an upgrade.
Ongoing system and data changes can impact plans for some SQL statements, potentially
causing performance regressions. SQL plan baselines help minimize performance
regressions and stabilize SQL performance.
Deployment of new application modules means introducing new SQL statements into the
database. The application software may use appropriate SQL execution plans developed in
a standard test configuration for the new statements. If the system configuration is
significantly different from the test configuration, then the database can evolve SQL plan
baselines over time to produce better performance.