Reducing Oracle CPU Usage with SQL Plan Baselines and Adaptive Query Optimization

High CPU usage is one of the most common — and often misunderstood — performance issues in Oracle Database environments.
When SQL execution plans fluctuate or the optimizer misjudges statistics, the CPU load can spike, slowing down even well-tuned systems.

The solution?
Leverage SQL Plan Baselines and Adaptive Query Optimization — two Oracle features designed to stabilize SQL performance and reduce CPU consumption over time.

---

Why Oracle CPU Usage Spikes

Oracle CPU utilization typically increases due to:

1. Bad execution plans – optimizer chooses suboptimal joins or full scans.
2. Outdated statistics – causing wrong cardinality estimates.
3. Plan instability – same SQL generates multiple execution plans.
4. Inefficient queries – missing indexes, bad joins, or redundant logic.
5. High parallelism – too many worker threads competing for CPU.

---

Step 1: Identify High CPU SQLs

The first step is identifying which SQL statements are causing high CPU usage.

Use AWR or v$ views:

SELECT sql_id, elapsed_time/1000000 AS elapsed_s,
       cpu_time/1000000 AS cpu_s,
       executions, sql_text
FROM v$sql
WHERE cpu_time > 1000000
ORDER BY cpu_time DESC FETCH FIRST 10 ROWS ONLY;

Or generate an AWR report and review the “SQL ordered by CPU Time” section.

You’ll often find that a few SQLs consume 70–80% of CPU time.

---

Step 2: Enable and Use SQL Plan Baselines

A SQL Plan Baseline stores the “known good” execution plan for a query.
If the optimizer later tries to pick a worse plan, Oracle automatically forces the baseline plan — keeping performance consistent.

---

✅ Enable Baseline Capture

You can capture baselines automatically:

ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE;

Or manually load from the cursor cache:

EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '7t9f6w2jq1xk9');

---

✅ Verify Baselines

Check which plans are stored:

SELECT sql_handle, plan_name, enabled, accepted 
FROM dba_sql_plan_baselines;

---

✅ Force a Good Plan

If you have a preferred execution plan:

EXEC DBMS_SPM.ALTER_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_1234abcd', plan_name=>'SQL_PLAN_9xk8y1', fixed=>'YES');

Now Oracle will reuse that plan — avoiding future regressions that can cause CPU spikes.

---

Step 3: Implement Adaptive Query Optimization

Introduced in Oracle 12c and enhanced in 19c, Adaptive Query Optimization (AQO) allows the optimizer to adjust execution plans during runtime based on actual data.

---

Enable Adaptive Features

Check parameters:

SHOW PARAMETER optimizer_adaptive_features;
SHOW PARAMETER optimizer_adaptive_statistics;

If disabled, enable adaptivity:

ALTER SYSTEM SET optimizer_adaptive_statistics = TRUE;
ALTER SYSTEM SET optimizer_adaptive_plans = TRUE;

---

How It Helps Reduce CPU Usage

• Adaptive joins: Switches between nested loops and hash joins dynamically.
• Adaptive statistics: Uses real execution feedback to fine-tune estimates.
• Reduced full scans: Automatically changes plan mid-query if cost estimates are off.

💡 This means Oracle learns from workload patterns — improving performance without manual tuning.

---

Step 4: Monitor Plan Changes

To ensure stability, monitor the number of distinct plans per SQL:

SELECT sql_id, COUNT(DISTINCT plan_hash_value) AS plan_count
FROM v$sql
GROUP BY sql_id
HAVING COUNT(DISTINCT plan_hash_value) > 1;

If a SQL has multiple plans, it’s a candidate for SQL Plan Baseline enforcement.

---

Step 5: Combine Baselines + Adaptive Optimization

The best approach is a hybrid strategy:

• Use Adaptive Optimization to let Oracle learn and improve automatically.
• Use SQL Plan Baselines to lock in known good plans.

This ensures both stability and intelligence — Oracle can adapt to real-world workloads while avoiding regressions.

---

Real-World Example

A retail OLTP system running Oracle 19c saw a 50% CPU spike after statistics refresh.
AWR showed multiple execution plans for the same high-frequency SQL.

Solution:

1.Loaded the best-performing plan into the baseline:

EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'d9g7a9m2q3r8t');

2. Enabled adaptive plans:

ALTER SYSTEM SET optimizer_adaptive_plans=TRUE;

3. Fixed the known good plan:

EXEC DBMS_SPM.ALTER_SQL_PLAN_BASELINE(fixed=>'YES');

✅ Result: CPU usage dropped by 40%, and query response time improved by 3x.

---

Best Practices for Stable SQL Performance

1. Capture baselines for all critical production SQLs.
2. Monitor new plans after patching or statistics refresh.
3. Use AWR to detect plan instability early.
4. Enable adaptive features for dynamic tuning.
5. Combine baselines + adaptive plans for best long-term results.
6. Pin important plans before major upgrades or migrations.
7. Use SQL Plan Management reports to audit changes.

---

Related Articles

• How to Install Oracle 19c Database on Red Hat Enterprise Linux 9
• How to Set LOCAL_LISTENER Parameter in Oracle Databases (Step-by-Step Guide)
• Upgrade Oracle Database 11.2.0.3 to 11.2.0.4 Using DBUA – Step-by-Step with Screenshots

---

Final Thoughts

High CPU usage in Oracle is usually a symptom of bad SQL execution plans — not bad hardware.
By combining SQL Plan Baselines and Adaptive Query Optimization, you can create a system that is both self-tuning and stable — reducing CPU spikes while improving query reliability.

Performance isn’t about luck — it’s about control, feedback, and consistency.