Oracle SQL Plan Management (SPM) and Execution Plan Stability in Oracle Database 19c


1. Introduction

Modern Oracle databases rely heavily on the Cost Based Optimizer (CBO) to determine the most efficient execution plan for SQL statements. As database objects, statistics, indexes, and optimizer settings change over time, execution plans may also change. While some plan changes improve performance, others can introduce SQL performance regressions and negatively impact business applications.

Oracle SQL Plan Management (SPM) provides a controlled mechanism for maintaining execution plan stability by capturing, managing, and enforcing SQL Plan Baselines. SPM enables database administrators to preserve known good execution plans while allowing controlled evaluation of alternative plans through plan evolution.

This assessment demonstrates the implementation of SQL Plan Management in Oracle Database 19c and evaluates how SQL Plan Baselines can be used to prevent execution plan regressions and maintain predictable SQL performance.

2. Objective

The primary objectives of this assessment are:

 Understand Oracle SQL Plan Management architecture.

 Capture execution plans as SQL Plan Baselines.

 Analyze execution plan changes caused by optimizer decisions.

 Validate plan stability using accepted baselines.

 Demonstrate plan regression prevention.

 Implement Fixed SQL Plan Baselines.

 Perform Plan Evolution testing.

 Evaluate Optimizer behavior with and without SPM.

 Document best practices for enterprise Oracle environments.

3. Scope

This assessment covers:

 SQL Plan Baseline Creation

 Baseline Capture from Cursor Cache

 Execution Plan Verification

 Plan Stability Validation

 Fixed Baseline Implementation

 Plan Evolution Testing

 Optimizer Plan Selection Analysis

 Performance Governance Concepts

This assessment does not include:

 SQL Profiles

 SQL Patches

 Automatic Indexing

 Exadata Specific Features

 Database Upgrade Scenarios

4. Environment Details

Component                  Details

Database Version        Oracle Database 19c Enterprise Edition (19.3)

Database Name           PROD

Architecture                Multitenant

PDB Name                  PRODS

Instances                     prod1, prod2

Cluster Type              2-Node Oracle RAC

Node 1                       node1.localdomain

Node 2                       node2.localdomain

Operating System      Oracle Linux 7.9

Storage                      ASM

Test Schema             SPMTEST

Test Table                SPM_DEMO

5. Prerequisites

Prior To Implementation, the following prerequisites were validated:

Oracle Version Verification



SQL Plan Management Availability



DBMS_SPM Package Verification


Existing Baseline Verification

Optimizer Configuration Verification


6. Oracle SQL Plan Management Architecture

Architecture Overview

Oracle SQL Plan Management operates as a performance governance framework that controls execution plan selection. The optimizer may generate multiple candidate plans, but SQL Plan Baselines determine which plans are accepted for execution.

Workflow:

1. SQL executes.

2. Optimizer generates execution plan.

3. Plan captured into baseline repository.

4. Baseline marked as Accepted.

5. Future executions reuse accepted plan.

6. New plans undergo validation through Plan Evolution.

7. Approved plans become accepted baselines.

7. Use Cases

Enterprise ERP Applications

Prevent execution plan changes after statistics collection.

Financial Systems

Maintain predictable response times for business-critical SQL.

Production Databases

Avoid SQL performance regressions caused by optimizer changes.

Database Upgrades

Preserve known good plans during database version upgrades.

Performance Governance

Implement controlled plan management processes.

8. Methodology

The assessment followed a structured implementation approach.

Phase 1 - Environment Preparation

Phase 2 - Baseline Data Creation

Phase 3 - Execution Plan Analysis

Phase 4 - Baseline Capture

Phase 5 - Plan Change Simulation

Phase 6 - Baseline Enforcement

Phase 7 - Fixed Baseline Testing

Phase 8 - Plan Evolution Validation

Phase 9 - Findings and Analysis

9. Implementation Steps

Step 1: Verify Assessment User


Step 2: Connect as SPMTEST



Step 3: Create Assessment Table


Step 4: Populate Test Data



Step 5: Gather Statistics


Step 6: Execute SQL and Capture Initial Plan


Step 7: Obtain SQL_ID

Step 8: Capture SQL Plan Baseline

Step 9: Verify Baseline Creation


Step 10: Create Index



Step 11: Gather Statistics Again


Step 12: Verify Plan Stability


Step 13: Perform Plan Evolution



10. Findings

Finding 1 -SQL Plan Baselines successfully preserved execution plan stability.

Finding 2 -Accepted baselines prevented optimizer-generated regressions.

Finding 3 - Fixed baselines ensured deterministic plan selection.

Finding 4 - Plan Evolution enabled controlled testing of alternative plans.

Finding 5 - SQL Plan Management improved execution plan governance.

11. Risks and Mitigation

Risk:                                          Mitigation:

Plan Regression                        Use Accepted Baselines

Optimizer Changes                   Enable SPM

Statistics Variations                  Validate Baselines

Unexpected Plan Selection       Use Fixed Baselines

Upgrade Related Changes        Preserve Baselines

12. Business Impact

Implementation of SQL Plan Management provides:

 Stable application performance

 Reduced production incidents

 Controlled optimizer behavior

 Improved service availability

 Predictable SQL execution

 Reduced troubleshooting effort

 Enhanced performance governance

13. Lessons Learned

 Execution plans can change unexpectedly.

 Optimizer decisions may not always produce the desired plan.

 SQL Plan Baselines provide controlled plan management.

 Fixed Baselines ensure execution consistency.

 Plan Evolution enables safe plan improvements.

 SPM is a critical feature for enterprise performance stability.

14. Limitations

 Assessment performed in lab environment.

 Limited workload compared to production systems.

 Single SQL statement used for demonstration.

 Real-world applications typically contain thousands of SQL statements.

 Performance gains vary by workload characteristics.

15. Conclusion

This assessment successfully demonstrated the implementation of Oracle SQL Plan Management in Oracle Database 19c. SQL Plan Baselines were captured, validated, and enforced to maintain execution plan stability. Fixed Baselines and Plan Evolution capabilities were evaluated to understand their role in preventing SQL performance regressions and enabling controlled optimizer improvements.

The results confirm that SQL Plan Management is an effective mechanism for maintaining predictable SQL performance and is a recommended best practice for enterprise Oracle database environments.

No comments:

Post a Comment