oracle11g执行计划管理-(讲解如何固定sql的执行计划)
5星 · 超过95%的资源 需积分: 50 152 浏览量
2012-06-21
18:57:08
上传
评论 3
收藏 190KB PDF 举报 
Oracle 11g 执行计划管理
摘要：本文描述了 11g 的新特性之一：执行计划管理，介绍了引入该新特性的原因，以及该新特性的相关特点、工作原理等。最后通
过引入一个测试案例来介绍如何使用该新特性。
1 执行计划管理的工作原理
我们知道，SQL 语句的性能很大程度上依赖于 SQL 语句的执行计划。如果 SQL 语句的执行计划发生改变，则 SQL 的性能很有可能发
生大的变化。而 SQL 执行计划发生变化的原因有很多种，包括优化器版本的变化、统计信息的变化、优化器相关的各种参数的变化、添加
或删除了索引、添加或删除了物化视图、修改了系统设置、以及是否应用了 10g 引入的 SQL profile 等。
在 11g 之前，我们可以使用存储大纲（stored outline）和 SQL Profile 来帮助我们固定某条 SQL 语句的执行计划，防止由于
执行计划发生变化而导致的性能下降。不过这些技术需要 DBA 人为的处理，比如存储大纲，需要 DBA 手工创建，而 SQL Profile 来说，
则要 DBA 手工应用才能生效。
从 11g 开始，oracle 引入了 SQL 执行计划管理（SQL Plan Management）这个新特性，从而可以让系统自动的来控制 SQL 语句
执行计划的稳定性，进而防止由于执行计划发生变化而导致的性能下降。
通过启用该特性，某条语句如果产生了一个新的执行计划，只有在它的性能比原来的执行计划好的情况下，才会被使用。
为了实现执行计划管理，优化器会为所有执行次数超过一次的 SQL 语句维护该 SQL 语句的每个执行计划的历史列表（plan
history）。优化器通过维护一个语句执行的日志条目（statement log）来识别该 SQL 语句是否为第二次执行。一旦优化器认出某条
SQL 语句为第二次执行，则优化器将该语句所生成的所有不同的执行计划插入到 plan history 的相关表里，而 plan history 里包含
了优化器能够用于重新生成执行计划的所有信息，这些信息包括 SQL 文本、存储大纲、绑定变量以及解析环境（比如 optimizer_mode
之类影响优化器解析 SQL 语句的参数）等。
当然，11g 也支持手工维护 SQL 语句的 plan history，作为对自动维护 plan history 的功能补充。
但是并不是说 plan history 里任何的执行计划都可以拿来使用。这里需要先介绍一下所谓的执行计划基准线（plan baseline）
这个概念。plan baseline 是 plan history 的一个子集，plan baseline 里面的执行计划是用来比较性能好坏的一个依据。也就是
说，凭什么来判断是否可以使用一个新产生的执行计划呢？就是把该新的执行计划与 plan baseline 里的计划进行比较来判断。某个
SQL 语句的执行计划可以属于 plan history，但是不一定属于 plan baseline。
注意：每个 SQL 语句都会有它自己的执行计划历史以及执行计划基准线。
那么某个 SQL 语句的执行计划是如何进入执行计划历史，乃至执行计划基准线的呢？
有两种方法可以将 SQL 语句的执行计划纳入到执行计划管理体系中去：
1) 将初始化参数：OPTIMIZER_CAPTURE_PLAN_BASELINES 设置为 true，则会自动的捕获 SQL 的执行计划。该参数缺省为
false。设置为 true 以后，当某条 SQL 语句第一次执行时，该 SQL 语句的 plan history 是空的，显然该 SQL 语句的 plan
baseline 也是空的。那么当该 SQL 第二次再次执行时，优化器会自动将该 SQL 语句以及相关的执行计划放入 plan history，同时也
会放入到 plan baseline 里。这就构成了最初的 plan baseline，也就是说最初进入 plan history 的执行计划会直接自动进入
plan baseline 里。那么当你做了某些修改，比如添加了一个索引等，然后第三次执行该同样的 SQL 语句，则会产生另外一个不同的执
行计划，这时新的执行计划会自动进入 plan history，但是不会自动进入 plan baseline。是否使用该新的执行计划，则要把该新的
执行计划与 plan baseline 里现存的第二次执行 SQL 时的执行计划进行比较，如果新的执行计划成本更低，则会使用新的执行计划，否
则使用 plan baseline 里的执行计划。
2) 使用 dbms_spm 包手工处理，这可以让你手工管理 SQL plan baseline。使用该包，你可以直接将 SQL 的执行计划从 shared
pool 里加载到 plan baseline 里，也可以使用 dbms_spm 包将已经存在的 SQL Tuning Set 加载到 plan baseline 里。同时，
dbms_spm 可以让你把 plan history 里的执行计划加入到 plan baseline 里。反之，你也可以使用该包将 plan baseline 里的执
行计划移出去。
注意，某条 SQL 语句的 plan baseline 里的第一个执行计划可以像上面说的通过设置初始化参数来自动加入，但是如果你希望在
- 1
- 2
- 3
- 4
前往页