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
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