Advanced SQL Plan Management: Baselines for SQLs Running on Oracle Active Standby Databases.

Recently, I was assigned the task of developing a method to create SQL Plan Baselines for workloads that run exclusively on Oracle Active Standby Databases. My first step was to open an Oracle Service Request (SR) to check whether any official documentation existed on how to handle this scenario.

To my surprise, Oracle Support confirmed that it wasn’t possible, and other DBAs I consulted said the same (including ChatGPT). The challenge stems from the fact that Active Standby Databases are read-only, meaning they don’t allow write operations (except on temporary tables).

However, I wanted to find a way around this limitation. I didn’t want to involve the application team or have them execute anything on the primary database on every occasion I needed to do this. Instead, my goal was to design a practical and repeatable method that other DBAs could use independently to create these baselines.

I’ve created not only one method, but two.

Note: I won’t cover the differences between SQL Profiles and SQL Plan Baselines in this article. It’s worth mentioning, however, that SQL Plan Baselines can be exported from one database and imported into another. In this article, I focus on a different approach.

---

1. Test Environment

For this test, I used an environment composed of a two-node Oracle RAC primary database and a two-node Active Data Guard (standby) database, both running Oracle Database 19c.The CURSOR_SHARING parameter was set to FORCE on both sides.

To simulate a realistic workload, I used the SwingBench tool, directing its connections to the standby database, as shown below.

And when we look on the standby side, we can see the following:

When analyzing the standby side, I noticed something interesting: two SQL_IDs (29qp10usqkqh0 and f28p5ptx7602x) had nearly identical SQL text but different FORCE_MATCHING_SIGNATURE values. Keep them in mind, they’re key for Method 1.

2. The methods

In this experiment, I tested and validated two different methods for creating SQL Plan Baselines on an Active Standby Database.

• Method 1: Uses the FORCE_MATCHING_SIGNATURE to associate SQL statements with their execution plans.

• Method 2: Leverages the UMF topology configuration, allowing SQL execution plan data to be transferred and managed across database environments.

2.1 The FORCE_MATCHING_SIGNATURE method

Let’s start with a practical example. Imagine that this SQL has more than on execution plan. The first step is to “export” this execution plan from the standby database using the script coe_xfr_sql_profile.sql. This script requires only two parameters: the SQL_ID and the PLAN_HASH_VALUE.

As a result, the script generates another file, in my case, it was named: coe_xfr_sql_profile_29qp10usqkqh0_375625260.sql.

Before executing it, open the file and change the parameter force_match = FALSE → TRUE.

This ensures the SQL Profile (and later, the SQL Plan Baseline) can be reused by any SQL sharing the same SQL text and FORCE_MATCHING_SIGNATURE.

Next, transfer the generated script to the primary database and execute it there.

Here’s where it gets tricky, when CURSOR_SHARING is set to FORCE, Oracle replaces literal values with system-generated variables. On the primary database side, you need to execute the SQL and it must have the same FORCE_MATCHING_SIGNATURE value 15667842054921087251. At this stage it is very important that you remember the SQL_ID f28p5ptx7602x. It has the FORCE_MATCHING_SIGNATURE value 6106929378163046652. That happened because I’ve executed the SQL using this method.

When you do this, because we have the CURSOR_SHARING parameter set to FORCE the database replaces literal values by system generated variables. And we ended up with a different FORCE_MATCHING_SIGNATURE. The result is that the SQL Profile is not going to be used.

In some cases, I’ve seen system-generated binds like :1, :2, and :3. Therefore, I’m using this to keep variable names consistent:

After executing the SQL above on the primary database side, I see this:

With an executed version of this SQL stored in the library cache the only thing left is to create the SQL Plan Baseline and disable or drop the SQL Profile.

The result? The SQL Plan Baseline becomes visible and usable, even for SQL executed only on the standby side.

2.2 The UMF method

Starting with Oracle 12.2, DBAs can generate AWR snapshots on Active Data Guard Standby Databases (see Doc ID 2409808.1 – “How to Generate AWRs in Active Data Guard Standby Databases”). I’m not going to describe how to make the initial configuration. Here is what I have.

Remember that you can always do this to make sure your SQL_ID is going to be captured by an AWR snapshot.

With the environment configured, the only remaining step is to run my custom script awr_sql_baseline.sql, which automates the baseline creation process. It’s self-explanatory and leverages the AWR infrastructure to generate the baseline seamlessly.

The result can then be verified from the standby database.

3. Final Thoughts

   
   

Creating SQL Plan Baselines for SQL executed only on Active Standby Databases isn’t straightforward, but it’s absolutely possible.

Both methods have their place:

• The FORCE_MATCHING_SIGNATURE method is quick and script-based, ideal for specific SQLs.

• The UMF-based approach is more integrated and scalable, ideal for environments already using AWR collection across Data Guard. However, it currently appears not to function correctly on Oracle Database 19.27, and Oracle Support has yet to provide a fix or workaround. 

I hope this helps other DBAs who face similar challenges. Give it a try, refine it for your environment, and share your experience.

#OracleDatabase #OracleDBA #Oracle19c #OracleDataGuard #SQLPlanManagement #OraclePerformance #SQLTuning #DatabaseAdministration

Written by Italo Santos, Sr. DataOps Engineer at Bravo LT.