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.
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.
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).
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.
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;
/
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.
No. Automatic SPM is distinct from auto capture. You should use real-time SPM with optimizer_capture_sql_plan_baselines set to FALSE.
Real-time SPM significantly reduces the incidence of performance regressions, but it won't eliminate SQL performance regressions entirely.
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.
Nigel is the product manager for the Oracle Optimizer. He's worked with Oracle technology since 1988 and joined Oracle in 1996. He's been involved in a wide variety of roles including development, benchmarking, consulting and pre-sales.