Oracle SQL Plan Instability: Why SQL Profiles Are Not Enough and SQL Plan Baselines Are the Reliable Solution

In performance-critical environments, having consistent SQL execution plans is essential. A common challenge arises when Oracle’s Cost-Based Optimizer switches execution plans, resulting in unpredictable performance.

We recently encountered this scenario where a query ran with excellent performance using one plan, but occasionally degraded due to the optimizer choosing an alternate, inefficient plan — even with a SQL Profile enabled. This blog documents the issue, our attempted solutions, and why SQL Plan Baselines ultimately proved to be the right fix.

Business Use Case

A financial reporting query on a table FIN_STAGE.account_snapshot_journal was used to retrieve account-level data for each branch based on latest timestamps and filtering on valid date ranges. The SQL used timestamp range filtering, complex CASE logic, and joined aggregated data back to the base set.

Example Query

SELECT c.max_time, c.branch_code, c.acct_no,
       b.limit_amt, b.avail_amt, b.status_flag, b.latest_flag
FROM (
    SELECT a.txn_time, a.limit_amt, a.avail_amt, a.branch_code,
           CASE
               WHEN EXTRACT(YEAR FROM a.valid_date) = EXTRACT(YEAR FROM SYSDATE) THEN 'ACTIVE'
               WHEN a.valid_date <= SYSDATE THEN 'ACTIVE'
               WHEN EXTRACT(YEAR FROM a.valid_date) = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE, -12)) THEN 'FUTURE'
               WHEN a.valid_date > ADD_MONTHS(SYSDATE, -24) THEN 'FUTURE'
               ELSE NULL
           END AS status_flag,
           'Y' AS latest_flag
    FROM FIN_STAGE.account_snapshot_journal a
    WHERE txn_time BETWEEN :1 AND :2
) b
JOIN (
    SELECT MAX(txn_time) AS max_time, branch_code, acct_no
    FROM FIN_STAGE.account_snapshot_journal
    WHERE txn_time BETWEEN :3 AND :4
    GROUP BY branch_code, acct_no
) c ON b.branch_code = c.branch_code AND b.txn_time = c.max_time;

The Problem

The query intermittently switched between two execution plans:

• Plan Hash 1311461192: Optimal plan — executed in ~70ms
• Plan Hash 492203874: Suboptimal plan — took over 300 seconds with high logical I/O

Even after applying a SQL Profile, the optimizer occasionally reverted to the bad plan, leading to application delays.

SQL Profile: First Attempt

We generated a SQL Profile using Oracle SQLT or coe_xfr_sql_profile.sql to push the optimizer toward the good plan.

SELECT name, status
FROM dba_sql_profiles
WHERE name = 'coe_c47h6yfcvbw6k_1311461192';

The profile was successfully created and enabled. For a couple of weeks, it ensured consistent performance.

However, this was short-lived

Issue Returns: SQL Profile Not Enough

Despite having the SQL Profile in place, AWR and v$sql showed the optimizer had again chosen the alternate plan 492203874. Analysis confirmed:

• Same SQL ID and SQL text
• Same bind values
• SQL Profile was still enabled

Root Cause:
SQL Profiles provide guidance to the optimizer — not enforcement. They can be ignored if the optimizer determines that another plan is more “appropriate” based on internal costing or runtime metadata.

SQL Patch: Enforcing the Plan Manually

As an intermediate step before using SQL Plan Baseline, we created a SQL Patch with specific outline hints to lock the optimizer behavior.

SQL Patch Code

BEGIN
  DBMS_SQLDIAG.create_sql_patch(
    sql_id     => 'c47h6yfcvbw6k',
    hint_text  => q'[
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$55EFA6E7")
      MERGE(@"SEL$F5BB74E1" >"SEL$4")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$F5BB74E1")
      MERGE(@"SEL$2" >"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      NO_ACCESS(@"SEL$55EFA6E7" "C"@"SEL$1")
      INDEX(@"SEL$55EFA6E7" "A"@"SEL$2" ("ACCOUNT_SNAPSHOT_JOURNAL"."TXN_TIME"))
      LEADING(@"SEL$55EFA6E7" "C"@"SEL$1" "A"@"SEL$2")
      USE_NL(@"SEL$55EFA6E7" "A"@"SEL$2")
      NLJ_BATCHING(@"SEL$55EFA6E7" "A"@"SEL$2")
      INDEX_RS_ASC(@"SEL$3" "ACCOUNT_SNAPSHOT_JOURNAL"@"SEL$3" ("ACCOUNT_SNAPSHOT_JOURNAL"."TXN_TIME"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$3" "ACCOUNT_SNAPSHOT_JOURNAL"@"SEL$3")
      USE_HASH_AGGREGATION(@"SEL$3")
    ]',
    name       => 'patch_c47h6yfcvbw6k'
  );
END;

This forced the optimizer to follow the hint path and restored the expected performance — however, it’s not a long-term enterprise-level solution.

---

Final Resolution: SQL Plan Baseline

To achieve complete stability, we loaded the good plan from cursor cache into the SQL Plan Baseline repository.

EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
  sql_id => 'c47h6yfcvbw6k',
  plan_hash_value => 1311461192,
  fixed => 'YES',
  enabled => 'YES');

Then we dropped the SQL Profile:

EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_c47h6yfcvbw6k_1311461192');

From that point on, the optimizer consistently picked the fixed baseline plan, and performance never degraded again.

Summary Table: Options Comparison

Method	Enforces Plan	Recommended For	Drawbacks
SQL Profile	No	Minor instability cases	May be ignored
SQL Patch	Yes	Immediate fix for plan injection	Not scalable for all workloads
SQL Plan Baseline	Yes	Critical queries needing control	Requires capture and validation

Final Takeaway

If you are experiencing plan instability:

• SQL Profiles may temporarily help, but they are not reliable for long-term enforcement.
• SQL Patches offer tactical control but are harder to manage across environments.
• SQL Plan Baselines (SPM) are the most dependable method to guarantee consistent execution plan behavior for critical workloads.

Recommendation:
Always prefer SQL Plan Baseline over SQL Profile when plan stability is a must.