Insights into Statistics, Query Optimization and the Oracle Optimizer
-
19c
August 19, 2020
What's the Difference Between SPM Auto Capture and Auto SPM?
I was asked to compare SQL plan management auto capture with automatic SQL plan management, so here goes...
SQL plan management (SPM) auto capture is enabled by setting the database parameter optimizer_capture_sql_plan_baselines to TRUE (covered here). Then, by default, all plans for all SQL statements executed in the database more than once are captured. From Oracle Database 12c Release 2 you can specify filters to limit which SQL statements are chosen. This is done using the DBMS_SPM.CONFIGURE API. Generally speaking, you will capture all SQL statements for a particular application schema or set of schemas.
Automatic SPM works differently. It looks in AWR and the automatic SQL tuning set (ASTS) to identify SQL statements that are high resource consumers. It then looks for alternative SQL execution plans in the cursor cache, the AWR history and in ASTS. These alternative plans are test executed in the background using the SPM evolve advisor task (documented here). If a particular plan is found to perform better than the current plan, then a SQL plan baseline will be created to enforce the better plan. In this way, automatic SPM looks for individual query performance regressions and repairs them.
Therefore...
- SPM auto capture proactively protects SQL statements from performance regression and is intended to have wide or complete coverage of the SQL statements used by the application.
- Automatic SPM looks for individual SQL statement performance regressions and repairs them using a targeted set of SQL plan baselines.
There is generally no need to use automatic SPM and auto capture together since the latter is intended to prevent the need for the former.