How to Verify if the SQL Baselines are Applied in Oracle

YouTube video

Introduction

In this article, we will discuss how to verify if the SQL baselines are applied in Oracle. SQL baselines are a feature that was introduced in Oracle 11g and is also available in Oracle 12c. They help in stabilizing the performance of the database by ensuring that a particular execution plan is used consistently for a specific SQL query. By using SQL baselines, you can avoid performance degradation and regression.

A Useful Resource for Oracle DBAs

Before we dive into the main topic, let me introduce you to a valuable website for Oracle DBAs and Oracle E-Business Suite Apps DBAs. The website, abcbworkshop.blogspot.com, provides helpful information and resources for anyone working with Oracle databases and applications.

Loading a Plan into SQL Plan Baselines

Loading a plan into SQL plan baselines is a process that involves selecting a plan from the shared pool and loading it into the SQL plan baselines in the SYSAUX tablespace. This ensures that the selected plan is used consistently in the future, stabilizing the performance of the database.

Step 1: Identify the SQL Query

To begin, we need to identify the SQL query for which we want to load a plan into the baselines. Let’s assume we have a user named ‘Scott’ and we want to load a plan for a particular query executed by this user.

Step 2: Fetch the SQL ID and Plan Hash Value

Next, we need to fetch the SQL ID and Plan Hash Value for the query execution we want to use as a baseline. We can do this by checking the shared pool. The SQL ID and Plan Hash Value uniquely identify a specific execution plan.

Step 3: Run a PL/SQL Code

Once we have the SQL ID and Plan Hash Value, we can run a PL/SQL code to load the plan into the baselines. This code retrieves the plan from the shared pool and associates it with the SQL ID and Plan Hash Value.

Step 4: Verify the Plan is Loaded

After loading the plan into the baselines, we need to verify that it is successfully loaded. We can do this by running a script that queries the baselines using the handle associated with the SQL statement. If the plan is present in the baselines, it means it has been successfully loaded.

Benefits of SQL Plan Baselines

SQL plan baselines offer several benefits for Oracle database performance:

  1. Stable Performance: By loading and using a specific plan as a baseline, you can ensure stable performance of your database. This eliminates the risk of performance degradation caused by changing and altering plans.

  2. Future Use of Baselines: Once a plan is loaded into the baselines, it will be used for future executions of the SQL query. Any changes made to the database, such as parameter modifications or schema changes, will not affect the chosen plan.

  3. Performance Regression Avoidance: SQL baselines help in preventing performance regression, which is a situation where a new execution plan causes a decrease in performance compared to the previous plan. By using baselines, you can avoid such issues and maintain optimal performance.

  4. Manual and Auto Evolution Tasks: In Oracle 12c, there is an auto evolution feature that automatically verifies and integrates new plans into the baselines. However, in Oracle 11g, manual intervention is required to perform the evolution process. This allows for greater control and ensures that only optimized plans are used.

Conclusion

Verifying if SQL baselines are applied in Oracle is an essential step in maintaining a stable and high-performing database. By following the steps mentioned in this article, you can ensure that the desired plans are loaded into the baselines and used consistently. SQL plan baselines provide a reliable way to avoid performance regression and degradation, making them a valuable feature for Oracle DBAs.

Remember to regularly verify and update your baselines to adapt to any changes in your database environment. With SQL plan baselines, you can confidently optimize the performance of your Oracle database and deliver excellent results for your business.

Thank you for reading and have a great day!