oracle基于成本的优化器(cost based optimizer, CBO) 最令人沮丧的一点就是它看上去可能会不定期的改变SQL语句的执行计划的趋势.通常很难来确定执行计划为什么改变.
理解计划的不稳定性
oracle优化器(CBO), 它的工作本质上就是计算出获取SQL语句指定的特定数据集的最快方法, 一般来说, 这个必须使用预先计算好的关于所含对象(表, 索引及分区等)的统计信息来在非常短的时间内完成. 优化器通常没有时间来验证这些信息. 之所以有这么紧的时间约束是因为解析是一个串行运算. 因此, 数据库需要尽可能少做, 并尽可能快的完成. 否则, 解析可能会成为可扩展行的一个严重的瓶颈. 在这里我需要说明的一点是此处我所做的评论主要是针对OLTP环境的, 那么你的执行计划为什么会改变呢?
CBO的主要输入有3个.
- 统计信息 - 与SQL语句所引用的对象相关
- 环境 - 例如与优化器相关的参数设置
- SQL - 语句本身
因此, 除非上面这3点中的某一个改变了, 否则执行计划是不应该变化的. 我相信计划不稳定性导致的挫败感主要是由于我们认为”任何事情都没有变化”. 而实际上某些事情的确发生了变化.
1) 统计信息的变化
在大多数系统中都频繁收集对象级统计信息. 默认情况下, 10g 和 11g 都有一个在晚上运行的后台作业来计算新的统计信息. 如果这些后台作业也在你的系统中运行, 那也就意味着每天你都可能会有得到一个新的执行计划的机会.
2) 环境的改变
有很多参数可能会影响优化器的计算. 当前对一个语句进行解析时起作用的设置可以通过启用 10053 追踪来获得.
3) SQL 语句的改变
这里指的是你写的SQL语句没变, 但是, 优化器将它转换的时候发生了变化. 例如:
如果一个语句引用了视图并且底层的视图发生了变化, 语句也就改变了
如果一个语句使用绑定变量而从变量所传过来的值发生了变化, 语句也就改变了. 但是, 我们经过多年的培训被教育说, 使用绑定变量会避免硬解析, 但是 oracle 9i 引入了一个特性, 准许优化器在确定执行计划的解析过程中”窥视”绑定变量的值. 这就是你可能已经听说的声名不佳的绑定变量窥视. 它也是引起计划稳定性问题的主要原因之一.
绑定变量窥视
当 oracle 8i 中引入直方图信息的时候, 他们就提供了一种机制使得优化器可以识别出某一列的值的不是均匀分布的, 经典的例子就是100条记录只有两个非重复值, 其中值”Y”出现了99次而值 “N”只出现1次. 如果没有直方图信息, 优化器就会一直假设不管你需要的是Y, 还是N的数据, 都将会得到一半的记录 100/2=50, 因此, 你总是需要进行全表扫描而不是使用该列上的索引. 而直方图信息, 假设它是准确的, 能够让优化器知道数据分布是不均匀的. 其中几乎整张表的值都是 Y, 只有1%为N. 这就使得不管在where子句中指定了哪个值, 优化器都可以得出恰当的执行计划. 那么让我们来考虑一下这样所带来的影响, 这样可以改善对 Y 值进行查询的响应时间吗? 答案是否定的,(仍然需要全表扫描, 99%的数据当然需要全表扫描) 另外, 为了让优化器能够使用直方图信息, 你不得不在SQL语句中使用常量, 因此你必须向下面这样来书写SQL语句:
SELECT XYZ FROM TABLE1 WHERE COLUMN1 = ‘Y’;
SELECT XYZ FROM TABLE1 WHERE COLUMN1 = ‘N’;
现在进入主题: 绑定变量窥视, 它准许优化器窥视绑定变量值, 然后使用直方图信息来得出如同使用常量时那样恰当的执行计划. 新特性的问题在于它只看一次变量, 就是在对语句进行解析的时候, 现在让我们假设一种更切合实际的情况, 你有一张10 000 000行数据的表, 其中99%的值为Y, 只有1%的值为N, 在这个例子中,如果第一次执行语句时传递的值是Y, 那么就将会锁定全表扫描执行计划, 直到语句被迫进行重新解析, 即使在语句下一次执行过程中传递的值是N 也一样. 让我们来考虑一下这样所带来的影响, 在你得到全表扫描执行计划(因为你第一次传递的值是 Y )之后, 不管再传递什么值, 语句的行为都将是一样的. oracle将一直进行全表扫描, 总是做一样多的工作, 通常运行时间也基本相同, 从用户的观点来看, 这看上去似乎是合理的, 性能是一致的(顺便说一句, 没有直方图信息时的情况就是这样的), 另一方面, 如果在第一次执行的时候传递的值是N, 而选用了索引执行计划, 在值为N的时候执行将会比以前快, 但对于值为 Y 的执行却会令人难以置信的慢. 这绝对不是用户想要的.
解决以上问题, 在11g 中通过所谓自适应游标共享所实现.
综上, 绑定变量也不是所有情况都可以使用的.
注: 自适应游标共享 介绍 参数(cursor_sharing 如果设置为 force, 那么oracle 将会把所有的常量转换为绑定变量)
适当使用常量
给优化器一些提示(HINT) , 优化器可以按照自己的意愿接受或忽略. 当生命了无效的提示时不会返回任何错误或警告信息.
v$SQL_HINT 提供一个有效提示的列表
提示可以通过以一个加号(+)开头嵌入到注释中来应用到单独的SQL 语句中. 任何紧跟在 select, update, insert 或 delete 关键字之后以 (+)开头的注释都会被优化器评估. 注释中可以包含多个提示.
绑定变量窥视可能是最广泛也是最难对付的原因, 尽管大多数用户并不十分情愿关闭绑定变量窥视”特性”(_optim_peek_user_binds), 这一点是可以理解的, 但将它完全关闭实际上是可行的选择. 有很多生产系统都采用了这种方法. 除了将绑定变量完全关闭以外, 在需要直方图信息处理偏态数据分布的列上恰当使用常量, 实际上是在处理计划稳定性问题的同时, 还能使得优化器具有选择绝对最优执行计划能力的唯一有效途径.
oracle 数据库11g提供了一种新的处理计划不稳定的方法. SQL执行计划基线. 通过使用基线, 不准许一条语句切换到一个比已经执行过的显著慢的多的执行计划上. 这个心的机制以来于看上去与SQL概要文件非常相似的基线. 事实上, 它们也是以同样的结构保存在数据字典中. 基线的特征如下:
- 如果存在的话, 11g默认会使用基线, 参数 optimizer_use_sql_plan_baseline , true
- 默认不会创建基线, 要想创建基线, 你必须的做点什么
- 基线不存在分类的概念
- 与大纲和概要不同, 每个SQL语句可以有多个基线
- 基线的关键特征之一是它是第一个非常熟悉用来创建它们的执行计划的基于提示的机制.
- DBA_SQL_PLAN_BASELINE 显示已经创建的基线
- 与大纲和SQL概要文件同样的, 基线也应用到RAM环境中的所有实例上, 而并不是局部应用到某个特定的实例上.