Oracle执行计划管理 - SPM

https://blog.51cto.com/lhrbest/3246884

目录

Oracle优化器辅助手段的发展

SPM需求背景

SPM重要构成

SQL计划基准捕获

如何创建SQL计划基准

如何查看SQL计划基准

SQL计划基准选择

执行计划的三个属性

如何选择SQL计划

SQL计划基准发展

SQL计划基准发展的三种选择

如何发展SQL计划基准

使用和管理SQL管理库

初始化参数

管理SQL管理库的空间使用

Oracle优化器辅助手段的发展
Oracle 8：hint
Oracle 8i&9：stored outline - 存储提纲，将调好的执行计划存入outline(使用Hints生成的执行计划)，在不修改原SQL语句的情况下，可指定某条语句使用outline。效果同Hints，区别是不修改原SQL语句。
Oracle 10：sql profile - 在不修改原SQL语句的情况下，为特定的SQL指定优化器的一些信息，从而导致优化器生成更为合理的SQL执行计划，达到不修改SQL文本就可以改变并执行计划的目的。
Oracle 11：sql plan management (SPM)
SPM需求背景
任何数据库应用程序的性能在很大程度上都依赖于查询执行，尽管Oracle优化器无需用户干预就可以评估最佳计划，但是SQL语句的执行计划仍可能由于一下多种原因发生意外更改：版本升级、重新收集优化器统计信息、改变优化器参数或模式/元数据定义。由于无法保证计划始终向更好的情况改变，如果在环境变化时能够维持当前执行计划不变，或者只向更好的情况改变，将是最理想的解决方案。

SPM重要构成
SQL 计划管理 (SPM) 确保运行时性能永远不会因为执行计划的更改而降低。为了确保这点，只使用已接受（受信任）的执行计划；跟踪任何计划发展并随后对其进行评估，如果新计划未造成运行时性能改变或运行时性能得到提高，则接受新计划为经验证的计划。

SQL计划管理由三部分组成：SQL计划基准捕获，SQL计划基准选择，SQL计划基准发展

SQL计划基准捕获
创建SQL计划基准，包含所有SQL语句的受信任的执行计划（ACCETPED=YES）。

如何创建SQL计划基准
包含两种方式

自动捕获执行计划，逻辑见图1-1

 
批量加载执行计划，包含四种技术

针对给定的SQL调优集（STS）加载执行计划
从存储大纲加载执行计划
使用游标缓存中目前的执行计划
从临时表解压缩现有的SQL计划基准
如何查看SQL计划基准
SQL> select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselines;
 
PLAN_NAME                      SQL_HANDLE                     ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
MODULE
------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
SQL_PLAN_191pkfncj4zuddbd90e8e SYS_SQL_1486b27519127f4d       YES YES NO
sqlplus@e2c90ae100ab (TNS V1-V3)
select * from t1 where idcard > 500
 
SQL_PLAN_d9zsu6rdznqvgdbd90e8e SYS_SQL_d4ff1a35dbfa5b6f       YES YES NO
sqlplus@e2c90ae100ab (TNS V1-V3)
select * from t1 where user_id > 100
SQL计划基准选择
执行计划的三个属性
ENABLED - 表示计划已启用，可供优化器使用，若计划未设置未ENABLED，则优化器不考虑此计划。
ACCEPTED - 表示该计划已被验证为有效计划，若计划未验证为ACCEPTED，则优化器不考虑此计划。
FIXED - 固定计划，表示优化器只会考虑标记为FIXED的执行计划，且从中选择cost最低的固定计划。
如何选择SQL计划
逻辑见图1-1

**注意这里每次重新计算cost的代价不大，因为执行计划是已知的，优化器不必遍历所有的可能，只需根据算法计算出已知计划的cost便可。

SQL计划基准发展
SQL计划基准发展的三种选择
仅当其性能比现有SQL计划基准更佳时才接受新计划，逻辑如下图
不进行性能验证就接受新计划
运行性能比较并生成报告，但不发展新计划
如何发展SQL计划基准
逻辑见图1-2

使用和管理SQL管理库
初始化参数
optimizer_capture_sql_plan_baselines - 控制可重复SQL语句的新SQL计划基准的自动捕获，默认设置为FALSE
optimizer_use_sql_plan_baselines - 控制SQL计划基准的使用，启用后，优化器在SQL计划基准中寻找正在编译的SQL语句的计划，如果找到，优化器会计算各个计划的性能开销，然后选择开销最低的计划。默认设置为TRUE
管理SQL管理库的空间使用
管理库包括语句日志、计划历史记录和SQL计划基准。

存储：SQL管理库是数据库字典的组成部分，存储在SYSAUX表空间中。SQL管理库默认占用不超过SYSAUX表空间的10%，可设置为1%-50%。每周检测，若超过该限制，则会告警。

清理：每周定期清理执行计划，默认超过53周未使用的计划都会被清理，可设置为5-523周。
————————————————
版权声明：本文为CSDN博主「angeling2009」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/angeling2009/article/details/115211535