System changes—such as a upgrading a database or adding an index—may cause changes to execution plans of SQL statements, resulting in a significant impact on SQL performance. In some cases, the system changes may cause SQL statements to regress, resulting in performance degradation. In other cases, the system changes may improve SQL performance. Being able to accurately forecast the potential impact of system changes on SQL performance enables you to tune the system beforehand, in cases where the SQL statements regress, or to validate and measure the performance gain in cases where the performance of the SQL statements improves.
SQL Performance Analyzer automates the process of assessing the overall effect of a change on the full SQL workload by identifying performance divergence for each SQL statement. A report that shows the net impact on the workload performance due to the change is provided. For regressed SQL statements, SQL Performance Analyzer also provides appropriate execution plan details along with tuning recommendations. As a result, you can remedy any negative outcome before the end users are affected. Furthermore, you can validate—with significant time and cost savings—that the system change to the production environment will result in net improvement.
You can use the SQL Performance Analyzer to analyze the impact on SQL performance of any type of system changes, including:
Database upgrade
Database consolidation testing for pluggable databases (PDBs) and manual schema consolidation
Configuration changes to the operating system or hardware
Schema changes
Changes to database initialization parameters
Refreshing optimizer statistics
Validating SQL tuning actions
SQL Performance Analyzer for information about using SQL Performance Analyzer