Observability Insights: Guard Oracle Database 23ai upgrade against SQL performance regressions

February 7, 2025 | 3 minute read
Kathryn Jennings
Senior Principal Product Manager
Anusha Vojjola
Senior Product Manager
Text Size 100%:

Join us March 11, 2025 at 9 am PT for an upcoming Observability Insights session on SQL Performance Watch - Guard your Oracle Database 23ai upgrade against SQL performance regressions.

This informative session will demonstrate SQL Performance Watch as the valuable tool in the Database Management cloud service that helps protect Oracle Database 23ai upgrades from potential SQL performance regressions. Here's a breakdown of its key features and benefits:

SQL Performance Watch is an essential tool for anyone planning an Oracle Database 23ai upgrade

Key Features:

1. Change Impact Assessment: SQL Performance Watch can assess the impact of various system changes, such as database upgrades, patch sets, migrations, minor application upgrades, add/drop index, or routine refresh of optimizer statistics.

2. Real Production Context: The tool runs SQL statements before and after the change with real production context to generate an accurate report on the net impact on the workload.

3. Comprehensive Report: The report outlines any regressed SQL statements and provides detailed execution plan insights.

4. Proactive Recommendations: For regressed SQL statements, SQL Performance Watch offers proactive recommendations to remediate them.

 

Benefits:

1. Predicts and Prevents Performance Issues: By identifying potential performance issues before they occur, you can take proactive measures to prevent them.

2. Saves Time and Resources: Automated change impact assessment saves time and resources compared to manual testing and analysis.

3. Ensures Smooth Upgrades: With SQL Performance Watch, you can ensure that your Oracle Database 23ai upgrade goes smoothly without any unexpected performance regressions.

Overall, the SQL Performance Watch is an essential tool for anyone planning an Oracle Database 23ai upgrade or making other system changes that could potentially impact performance.

SQL Watch
Figure 1:  SQL Performance Watch overview

 

How it works:

SQL Performance Watch works by analyzing your database's workload before and after a change is made. It uses this information to identify any potential performance issues that may arise from the change.

Here are the steps involved in using SQL Performance Watch:

1. Set up your database environment: Before using SQL Performance Watch, you need to set up your database environment by creating a baseline capture of your workload.

2. Make changes: Once you have set up your baseline capture, make any necessary changes to your database environment (e.g., upgrade Oracle Database).

3. Run analysis: After making changes to your database environment, run an analysis using SQL Performance Watch.

4. Review results: Review the results of the analysis to identify any potential performance issues that may have arisen from the change.

 

SQL Performance Watch best practices for protecting against potential regression during Oracle Database 23ai upgrade

To get the most out of using SQL Performance Watch for protecting against potential regression during Oracle Database 23ai upgrade follow these best practices:

  1. Review SQL Performance Analyzer (SPA) restrictions – distributed queries, complex datatypes
    • Note, test SQL within the PL/SQL not the PLSQL call.
    • DML – select part of the query (access path) and also DML is rollbacked by default at the end of the SQL execution.
    • SPA does not change the state of the database.
  2. Use hybrid mode – explain plan trials to reduce workload to a small subset.
  3. Any SQL execution plan changes – improved or regressed performance, test additionally since faster HW or other such factors may mask underlying issues.
  4. Use multiple comparison metrics, start with Elapsed Time.
  5. Split large SQL Tuning sets (STS), a maximum of 5K statements in one STS.
  6. Check for #executions, #rows processed, hash value (if checking for correctness) in addition to execution plan for individual SQL statements.
  7. Use CONVERT_STS trial option as a last resort, pay attention to #executions in each trial, it is better to compare multi-executions to multi-executions trial.
  8. Restore the database to the point in time the STS was captured to address issues with #rows processed.
     

Register today for this session.

Resources:

Kathryn Jennings

Senior Principal Product Manager

Anusha Vojjola

Senior Product Manager

Anusha is a Senior Product Manager working in Observability and Manageability team. Her focus of work is mainly with Performance of the database. Anusha has recently completed her Master's program in Engineering Management at Duke University in May'22.


Previous Post

Observability Insights session – Take advantage of APM data analytics to unlock usage, performance trends, end user behavior patterns, and more

Kathryn Jennings | 4 min read

Next Post


Application Monitoring with OpenTelemetry

Zyaad Khader | 8 min read
Oracle Chatbot
Disconnected