Improved SQL Plan Management APIs in Oracle 26ai

Database performance is one of the most important aspects of any application. Even a small change in a SQL execution plan can significantly impact performance. To address this, Oracle AI Database 26ai introduces improvements to SQL Plan Management (SPM) APIs, making it easier for DBAs and developers to manage and stabilize SQL execution plans.

What is SQL Plan Management?

SQL Plan Management (SPM) is a feature in Oracle that helps maintain stable SQL execution performance by controlling which execution plans are used by the database.

Instead of allowing the optimizer to automatically change plans, SPM ensures that only verified and accepted plans are used.

Key benefits include:

• Prevents unexpected performance issues
• Maintains consistent query execution
• Improves application stability
• Allows controlled plan evolution

What’s New in Oracle 26ai?

Oracle 26ai improves SQL Plan Management by enhancing the APIs available in the DBMS_SPM package. These enhancements help simplify plan capture, evolution, and performance management.

The improvements focus on:

• Better automation of SQL plan baselines
• Improved plan evolution capabilities
• Easier management of execution plans
• Enhanced performance monitoring

Understanding SQL Plan Baselines

A SQL Plan Baseline is a set of accepted execution plans for a SQL statement. The optimizer will only use plans that exist in the baseline.

If a new plan appears, Oracle can test it before allowing it to replace the existing plan.

Capturing SQL Plans Automatically

Oracle 26ai improves how SQL plans are captured and stored. You can capture SQL plans directly from the cursor cache.

DECLARE
  l_plans_loaded NUMBER;
BEGIN
  l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => 'your_sql_id'
  );
END;
/

This allows DBAs to quickly create baselines for important queries.

Evolving SQL Plans

Sometimes a new execution plan may perform better than the current one. Oracle allows safe evaluation of new plans before accepting them.

DECLARE
  l_report CLOB;
BEGIN
  l_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
    sql_handle => 'your_sql_handle'
  );
END;
/

This ensures that only improved plans become part of the baseline.

Why These Improvements Matter

Modern applications generate complex workloads, especially in cloud and AI-driven environments. With Oracle 26ai focusing on AI workloads and high-performance analytics, stable SQL performance is more important than ever.

Improved SQL Plan Management APIs help organizations:

• Maintain predictable performance
• Reduce troubleshooting time
• Optimize critical queries
• Improve database reliability

Best Practices for Using SQL Plan Management

• Capture baselines for critical queries
• Regularly monitor SQL performance
• Allow controlled plan evolution
• Test new execution plans before enabling them
• Use automation provided in Oracle 26ai

Final Thoughts

The improvements to SQL Plan Management APIs in Oracle 26ai make it easier for DBAs and developers to manage execution plans efficiently. By ensuring stable query performance, these enhancements help organizations build reliable and scalable database applications.

If you are running performance-critical workloads, using SQL Plan Management in Oracle 26ai can significantly improve query stability and optimization.