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