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. The default is AUTO (real-time SPM enabled). ON was the previous 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;
/
-- For non-autonomous systems only, in the relevant PDB,
-- execute the following as SYS to ensure the correct value is set for ACCEPT_PLANS
BEGIN
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' ,
      parameter => 'ACCEPT_PLANS',
      value     => 'TRUE');
END;
/

Disable with:

BEGIN
   DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK','OFF');
END;
/
-- For non-autonomous systems only, in the relevant PDB as SYS, 
-- you may want change the alternate plan source from SQL_TUNING_SET
-- to AUTO if you plan to use SPM evolution in non-auto mode
BEGIN
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
      parameter => 'ALTERNATE_PLAN_SOURCE',
      value     => 'AUTO');
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.

What does the background task do?

When using real-time SPM, you might notice that there is a background task running:

SELECT enabled 
FROM   dba_autotask_schedule_control 
WHERE  dbid = sys_context('userenv','con_dbid') 
AND    task_name = 'Auto SPM Task';

This task is no longer responsible for identifying performance regressions (like it is when using automatic SPM with background verification). Instead, it evolves plans when necessary, just like it does when you’ve used SQL plan management in the past. It has the potential to resolve performance regressions that were not fixed during real-time verification (see below).

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 Base DB 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.

Can real-time SPM deal with terminated runaway SQL statements?

At DOAG this year, I was asked whether real-time SPM dealt with terminated runaway SQL statements (for example, a SQL statement that’s been running for so long it had to be terminated). I wasn’t sure of the details, and SQL quarantine was part of the discussion. So, I have built a new demo using database resource manager (DBRM). SQL quarantine is optional. There’s a new example on GitHub designed for use with Oracle Autonomous Database and it also includes an example of how you can use DBRM to terminate a runaway SQL statement (automatically). Real-time SPM reinstates the previous good plan, and it’s all in one shot. It’s pretty cool.

If you are not using DBRM to handle runaways, and you interrupt a long-running SQL statement manually without killing the session, real-time SPM will reinstate a previous, better-performing plan (as long as such a plan is present in the automatic SQL tuning set).