What is Real-time SQL plan Management?

What does real-time SPM do?

Real-time SPM quickly detects and repairs SQL performance problems caused by execution plan changes.

If a SQL statement has been running well, but a plan change causes it to perform poorly, real-time SPM detects this immediately. If it establishes that a previous plan will perform better, it will reinstate it using a SQL plan baselines.

This automates what some DBAs do already: they create SQL plan baselines to target individual SQL statements with intermittent performance issues and enforce a plan that is known to be good.

Automatic SQL plan management

Oracle Database 19c introduced automatic SQL plan management. In this release, a background task looks for alternative execution plans if a SQL performance regression is found. Inside the background task, SQL test execution is used to establish which plan is best, and a SQL plan baseline is accepted to enforce it.

Oracle Database 23ai introduces a new 'flavor' of SPM automation called real-time SPM. It's licensed for Oracle Database 23ai Enterprise Edition and is also available in Oracle Autonomous Database 19c.

How does real-time SPM work?

Over time, the database captures SQL statement performance and execution plan data for the SQL used by the application. New SQL is captured periodically and stored in the automatic SQL tuning set (ASTS).

When a SQL statement is hard parsed, the optimizer uses the ASTS to detect when a brand new plan has been chosen (i.e., the SQL statement is known, but the plan has not been used before). If this is the case, the optimizer compares the performance of the new plan with a previous (reference) plan found in the ASTS. Both plans are captured (and visible in DBA_SQL_PLAN_BASELINES), and the best-performing plan becomes an accepted SQL plan baseline.

A new plan must be executed at least once, allowing the database to gather SQL performance data before real-time SPM compares new plan performance with the reference plan. The foreground process implements these steps: the session parsing and executing the new plan. This is why DBA_SQL_PLAN_BASELINES refers to foreground verification (see below).

How can I tell real-time SPM is working?

The DBA_SQL_PLAN_BASELINES view has the information you need.

When real-time kicks in, the FORGROUND_LAST_VERIFIED column is populated. The column ORIGIN will show the value FOREGROUND-CAPTURE for SQL plan baselines created by real-time SPM. The NOTES column contains XML-based information on what plans were compared and the performance statistics seen.

How is real-time SPM enabled and disabled?

Check your current setting (in Autonomous Database you will see AUTO or ON if you have not changed the default):

SELECT parameter_value spm_status
FROM   dba_sql_management_config
WHERE  parameter_name = 'AUTO_SPM_EVOLVE_TASK';

You enable real-time SPM using the automatic SPM task parameter:

BEGIN
  DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK','AUTO');
END;
/

Disable with:

BEGIN
   DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK','OFF');
END;
/

Can I use it with pre-existing SQL plan baselines, SQL patches or SQL profiles?

Yes. Automatic SPM works even if you have pre-existing plan management objects. Manually created (or auto-captured) SQL plan baselines will control plans as expected, and SQL patches and SQL profiles will interact with execution plans and SQL plan baselines as they have previously.

Should I use it with SPM auto capture?

No. Automatic SPM is distinct from auto capture. You should use real-time SPM with optimizer_capture_sql_plan_baselines set to FALSE.

Does real-time SPM eliminate all SQL performance regressions?

Real-time SPM significantly reduces the incidence of performance regressions, but it won't eliminate SQL performance regressions entirely.

1. Like SPM in general, real-time SPM requires the application to use repeatable SQL statements using bind variables. Ad-hoc, highly dynamic SQL is not in scope.
2. Real-time SPM requires that a SQL statement had a SQL execution plan that performed well in the past and was captured in the ASTS. The ASTS task captures SQL from the cursor cache periodically. It is enabled automatically when real-time SPM is enabled.
3. A  poorly performing plan will be executed at least once so that the optimizer can gather SQL performance data.
4. In rare cases, real-time SPM may accept a previous plan that ultimately doesn't perform well (even though there are checks to avoid this as much as possible). To resolve this condition, a 'reverse verification' check is performed under certain circumstances. The steps are:
   • During hard parse, if the optimizer believes a previously rejected ‘new’ plan is best (based on cost), reverse verification kicks in.
   • The accepted plan is nevertheless executed (as expected). Once complete, reverse verification double-checks the performance of this plan against the previously rejected new plan. If the rejected plan’s statistics are better, it is reinstated.
   • A hard parse may not happen immediately, so there may be a time lag before reverse verification resolves the issue.
5. Non-reproducible plans cannot be used. For example, dropping an index may render an old plan non-reproducible and unusable (if the plan relies on the index). Ultimately, changing the application schema can induce performance regressions that cannot be repaired using plans captured in the past.
6. Real-time SPM doesn’t always intervene in cases where plans need to change due to data skew. Instead, adaptive cursor sharing (ACS) will kick in. For this reason, performance regressions may be experienced for some skew-sensitive SQL statements until ACS resolves the correct plan for each set of bind values.
7. Before reinstating an old plan, real-time SPM will parse it to confirm that the cost has not changed significantly. If it has, the plan will not be used. This avoids reinstating a plan that was (for example) executed on completely different data. The optimizer avoids reinstating a plan if something has changed significantly since the SQL performance data was captured in ASTS. It also ensures that the 'old' plan is still reproducible.
8. Real-time SPM only resolves performance issues accociated with execution plan changes.

If you’re interested in testing real-time SPM, you will find it hard to trick it into action by manipulating optimizer statistics or changing bind values on skewed data. The checks mentioned in bullet point seven will see to that (because changing statistics will change cost estimates). As per bullet point six, ACS is given room to breathe when resolving plans to deal with skewed data.

To get you started, I have created a real-time SPM demo for you to check out on GitHub. It relies on adjusting the parameter optimizer_index_cost_adj to induce poorly performing executing plans.