有人要求我比较一下SQL plan management auto capture(SPM自动捕获）与automatic SQL plan management（自动SPM），好吧，走起。。。
SPM自动捕获通过设置数据库参数optimizer_capture_sql_plan_baselines为TRUE来启用（详情可参考这个链接https://www.modb.pro/db/29991)。默认情况下，所有在数据库中执行超过1次的SQL的执行计划，都会被捕获下来。从Oracle Database 12c Release 2起你可以指定过滤条件来限定哪些SQL语句被捕获，这个可以通过使用DBMS_SPM.CONFIGURE API来操作。一般来说，你可以捕获特定应用的SCHEMA或SCHEMA的集合下的所有SQL。
自动SPM 的工作方式则不同。你会在AWR和标识高资源开销SQL的自动SQL调优集（ASTS)中看到它。它从游标缓存、历史AWR和自动SQL调优集中查找可供选择的SQL执行计划。这些执行计划均在后台，使用SPM演进顾问任务（相关文档请点此）来测试执行。如果一个特定的执行计划，被发现比当前的执行计划性能更好，那么一个SQL plan baseline将被创建并强制使用更好的计划。通过这种方式，自动SPM寻找性能退化的单个SQL并修复它们。
因此…
- SPM自动捕获主动预防SQL语句出现性能退化，并试图广泛或完全覆盖应用所使用的SQL。
- 自动SPM寻找性能退化的单个SQL并使用一组 SQL plan baselines来修复它们
一般来说，不需要同时使用自动SPM和SPM自动捕获，因为后者的目的就是为了不必用到前者。
原文链接：https://blogs.oracle.com/optimizer/post/whats-the-difference-between-spm-auto-capture-and-auto-spm
What’s the Difference Between SPM Auto Capture and Auto SPM?
August 19, 2020 | 1 minute read
Nigel Bayliss
Product Manager
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.
