How to analyze the performance impact of a database environment change using what-if analysis?

To properly evaluate the impact of a set of SQL statements during a database environment change, it's crucial to have a thorough understanding of how SQL query performance can be impacted. There are two primary types of performance changes that can occur in SQL queries. The first type, which I refer to as "progressive change," is typically caused by changes in statistics, such as fluctuations in data volume in relevant tables or index pages. If the statistics change is not significant enough to trigger a new query plan, the query plan will remain the same, and the performance of the SQL query will not be significantly altered compared to the original statistics.

The second type of performance change, which I refer to as "quantum change" occurs when a new query plan is introduced due to a significant change in statistics or schema. This type of change can have a major impact on performance, occasionally resulting in performance disasters.

When making changes to the database environment, it is essential to closely monitor the performance of SQL queries and take appropriate measures to optimize the affected statements. To track critical SQL statements before and after environment changes, there are some general steps you can follow:

1.     Extract the SQL statements along with their query plan and performance statistics from SGA or AWR.

2.     Apply the environment changes to the database, such as creating new indexes, gathering statistics, upgrading the database, or forecasting the performance of a software deployment in the production database.

3.     Obtain the query plan from the changed database environment.

4.     Compare the query plan for each SQL statement to identify any differences.

5.     Look for potential issues such as unused indexes, high-cost SQL changes, etc.

6.     Benchmark the query plan for the modified SQL statements to detect any degraded performance.

If you only have a small number of SQL statements to track in terms of performance before and after environment changes, the above steps can be done manually. However, it can be challenging if you have hundreds of SQL statements to monitor without a tool. Tosska DB Ace for Oracle is equipped with a robust tool that can assist you in tracking the performance differences of SQL statements between two databases.

DBAO SQL Performance Tracker - YouTube

Tosska DB Ace Enterprise for Oracle - Tosska Technologies Limited