[Oracle Performance Tuning] - SQL Plan Management (Overview)

SQL Plan Management (SMP)

A preventive mechanism, that enables the optimizer to automatically manage execution plans and ensure that the database only uses known and verified plans.

Benefits

• Prevents Performance regressions caused by execution plan changes.
• Gracefully adapt to execution plan changes by verifying and accepting only plan changes that improve performance.

SQL Management Base (SMB) (See the image below)

• SQL Management Base (SMB) resides in SYSAUX tablespace.
• SQL History Contains all SQL Plans (Accepted and Not Accepted)
• SQL Baseline contains Accepted and fixed plans.
• Oracle uses execution plan from SQL Baseline.

Plan Evolution

Optimizer verifies new plan and adds them to an Existing SQL Plan Baseline, if it consistently performs better than better than the existing execution plan in the baseline.

* DBAs can also manually evolve an execution plan for any SQL and add it to the SQL Baseline.

Watch Video Format: https://www.youtube.com/shorts/eBNlsd3aU50