oracle11g中SQL优化新特性之SQL Plan Management(SPM)

发布网友发布时间：2天前

共1个回答

热心网友时间：18小时前

Oracle Database11gR1引入的SQL Plan Management（SPM）是一项新特性，允许DBA捕获并保持任意SQL语句执行计划的最优状态，以限制刷新优化器统计数据、应用变化和数据库版本升级带来的影响。本文旨在帮助读者理解SPM的基本原理，并简要说明其在性能优化中的能力。

SPM通过一个简单而优雅的方法实现了解决SQL计划意外恶化的策略。一旦用户会话开启了自动SQL Plan Baseline捕获，CBO（Cost-Based Optimizer）就会在SQL Management Base（SMB）内记录会话内执行的任何SQL语句，包括SQL文本、概要（Outline）、绑定变量及其编译环境，并将其存储为一个SQL Plan Baseline。当SQL语句第二次执行时，CBO会比较新计划与SMB中的计划，评估新计划是否更高效。如果新计划能提升性能，SPM会将其标记为该语句的最优计划。然而，如果新计划降低了性能，SPM会在SMB中选择成本最低的计划，并将其存储，以备将来使用。

SPM捕获SQL Plan Baselines的过程相对简单。首先，通过调整参数`OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES`为TRUE，开启自动捕获功能。该参数默认为FALSE，表示不自动捕获。通过DBMS_SPM包，可以从数据库缓存、SQL Tuning Set或AWR快照中，或导入存储表中，预先捕获和导入大量SQL语句的计划。

捕获的SQL Plan Baselines包含SPM和CBO用于控制计划的元数据。当新计划进入SMB时，它会自动被标记为ENABLED，但只有在被验证为良好计划后，才会被标记为ACCEPTED。查询这些元数据最简单的方法是通过`DBA_SQL_PLAN_BASELINES`字典视图。查看已存在的SQL Plan Baselines以及对一条SQL语句执行有潜在影响的方法，可以通过DBMS_XPLAN包中的`DISPLAY_SQL_PLAN_BASELINE`过程实现。

自动捕获SQL Plan Baselines的实现涉及调整参数和执行相同的SQL语句两次。第一次记录SQL语句，第二次捕获计划并标记为该语句的ACCEPTED SQL Plan Baseline。如果后续执行产生不同的新计划，该计划也会被自动捕获，但不会被标记为ACCEPTED。SPM只会将第一个计划标记为ENABLED和ACCEPTED。

SQL Plan Baselines的演化过程包括手动和自动方法。手动方法包括调用`dbms_spm.evolve_sql_plan_baseline()`函数或使用SQL Tuning Advisor工具包来执行演化任务。自动方法则包括定期调度`evolve_sql_plan_baseline()`函数，或配置SQL TUNING ADVISOR以在自动任务窗口中自动运行。

具体操作包括：开启自动捕获和使用SPM，通过`ALTER SESSION`语句调整参数；查看SPM元数据和使用`DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE`显示包含特定文本的SQL Plan Baselines。