How Oracle 19c Protects SQL Performance Using SQL Plan Management (SPM)
What is SQL Plan Management (SPM)?
SQL Plan Management ensures that only known and verified execution plans are used for a SQL statement.
If a new plan appears (due to statistics changes, new index, or upgrade), Oracle tests it first before using it.
Step 1: Enable SQL Plan Baselines (19c)
ALTER SYSTEM SET optimizer_use_sql_plan_baselines = TRUE;
SHOW PARAMETER optimizer_use_sql_plan_baselines;
Step 2: Create Test Table
CREATE TABLE sales (
sale_id NUMBER,
customer_id NUMBER,
sale_date DATE,
amount NUMBER
);
Insert sample data:
Step 3: Run Query (Without Index)
Check execution plan:
Step 4: Capture SQL Plan Baseline
Load current plan into SPM:
Get sql_id
Step 5: Create an Index (Potential Plan Change)
Step 6: Check SQL Plan Baselines
In your example, Oracle captured 8 execution plans for the same SELECT * FROM sales WHERE customer_id = 500 query, but only one plan is accepted as stable.
This demonstrates that SPM is working by allowing only the accepted plan to be used for execution, while the remaining plans are kept for testing and will be used only if they prove to perform better.
Step 7: Evolve the New Plan (Optional)
Allow Oracle to test and accept the new plan:
Conclusion
SQL Plan Management in Oracle 19c provides a robust safety net for SQL performance.
By capturing and evolving execution plans, Oracle ensures that only stable and verified plans are used.
For DBAs and developers, SPM is an essential tool to prevent unexpected performance regressions.
