SQL Plan Management (SPM) ensures stable query performance by preserving known good execution plans and allowing only verified plans to be used. 1. SPM captures and stores execution plans in SQL plan baselines. 2. New plans are checked against the baseline and not used unless proven better or safe. 3. It prevents performance regressions by defaulting to accepted plans. 4. New plans must be manually or automatically evolved before acceptance. 5. You can replace underperforming plans through baseline evolution. 6. SPM requires manual loading of plans or automatic capture configuration. 7. Tools like DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE help load existing good plans. 8. Automatic evolution jobs should be scheduled for periodic evaluation. 9. Baselines should be reviewed regularly using views like DBA_SQL_PLAN_BASELINES. 10. Specific SQL IDs should be associated with baselines to avoid confusion in dynamic SQL environments. SPM enables predictable performance while still supporting optimization improvements but requires active management for optimal results.

SQL Plan Management (SPM) is Oracle’s way of making sure your database queries run efficiently without sudden performance drops, even when the optimizer decides to try a new execution plan. It works by capturing and preserving known good execution plans so that the optimizer can't pick worse ones accidentally.

Here’s how it works in practice:

What Exactly Is SPM?

SPM is a feature introduced in Oracle 11g that helps maintain stable query performance. It does this by maintaining a plan history for SQL statements and allowing only verified good plans to be used.

• When a SQL statement runs, its execution plan is stored in a structure called a SQL plan baseline.
• If a new plan shows up (like after a stats gathering or schema change), Oracle checks if it's part of the accepted baselines.
• If not, the optimizer evaluates it but won’t use it unless it proves to be better or safe enough.

This means you get predictable performance while still allowing for optimization improvements.

How Does SPM Help With Plan Stability?

Plan stability is all about avoiding performance regressions caused by unexpected changes in execution plans. Here’s how SPM ensures that:

• Only known good plans are used by default – The optimizer picks from the accepted plans in the baseline.
• New plans are vetted before being used – They’re added to the baseline as "not accepted" and must be verified manually or via automatic evolution.
• You can fix bad plans – If a known plan starts performing poorly, you can evolve the baseline by replacing it with a better one.

For example, imagine you have a nightly batch job that suddenly takes longer because the optimizer chose a different plan. With SPM enabled, the optimizer would fall back to using a previously accepted plan, keeping your job on schedule.

How to Use SPM Effectively

Using SPM isn’t just about turning it on — there are some practical steps and best practices to follow:

• Load your important SQL into baselines early, especially during application deployment or major upgrades.
• Use tools like DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE to capture existing good plans.
• Set up automatic evolution jobs to evaluate new plans periodically.
• Regularly review SQL plan baselines using views like DBA_SQL_PLAN_BASELINES.
• Consider associating baselines with specific SQL IDs to avoid confusion, especially in applications that use bind variables or generate dynamic SQL.

One thing to note: SPM doesn’t work out of the box automatically for all SQL. You need to either load plans manually or configure automatic capture through settings like optimizer_capture_sql_plan_baselines.

Final Thoughts

SPM gives you control over which execution plans are allowed to run, helping you avoid surprises without blocking optimization entirely. It’s not magic, though — it requires monitoring and occasional manual intervention to keep things running smoothly.

If you're dealing with an environment where performance consistency matters more than chasing every possible optimization, SPM is definitely worth setting up. Just remember: it works best when you actively manage the baselines and understand what each plan is doing.

The above is the detailed content of What is SQL Plan Management (SPM), and how can it ensure plan stability?. For more information, please follow other related articles on the PHP Chinese website!