1
Oracle里的优化器.pdf
免费下载
Oracle 里的优化器
优化器（optimizer）是 oracle 数据库内置的一个核心子系统。优化器的目的是按照一定的判断原则来得到它认为的目标 SQL 在当前的情形下的最
高效的执行路径，也就是为了得到目标 SQL 的最佳执行计划。依据所选择执行计划时所用的判断原则，oracle 数据库里的优化器又分为 RBO（基
于原则的优化器）和 CBO（基于成本的优化器，SQL 的成本根据统计信息算出）两种。
一、RBO
Oracle 会在代码里事先为各种类型的执行路径定一个等级，一共 15 个等级，从等级 1 到等级 15，oracle 认为等级 1 的执行路径是效率最高的，
等级 15 是执行效率最差的。对于等级相同的执行计划，oracle 根据目标对象的在数据字典中缓存的顺序判断选择哪一种执行计划。RBO 是一种适
合于 OLTP 类型 SQL 语句的优化器。相对于 CBO 而言，RBO 有着先天的缺陷，一旦 SQL 语句的执行计划出现问题，将很难调整。那么 RBO 执行
计划出现问题，怎么调整目标 SQL 的执行计划呢？一般有如下方法：等价改写目标 SQL，比如在 where 条件对 number 和 date 类型的列添加 0
（deptno+0>100），varchar2 或 char 类型的列可以添加一个“空字符”，例如“||”。对于多表连接的 SQL，可以改变 from 表的连接顺序（RBO
会按照从右往左的顺序决定谁是驱动表，谁是被驱动表。）来达到改变目标 SQL 执行计划的目的。我们也可以改变相关对象在数据字典中缓存的
顺序（创建顺序），来改变执行计划。RBO 最大的缺点是以 oracle 内置代码的规则作为判断标准，而并没有考虑到实际目标表的数据量以及数据
分布情况。
二、CBO
CBO 选择执行计划时，以目标 SQL 成本为判断原则，CBO 会选择一条执行成本最小的执行计划作为 SQL 的执行计划，各条执行路径的成本通过
目标 SQL 语句所涉及的表、索引、列等的统计信息算出。这里的成本是 oracle 通过相关对象的统计信息计算出来的一个值，它实际上代表目标 SQL
对应执行步骤所消耗的 IO、CPU、网络资源（针对于 dblink 下的分布式数据库系统而言）的消耗量，oracle 会把网络资源的消耗量计算在 IO 成
本内，实际上你看到的成本为 IO、CPU 资源，另外需要注意的是，oracle 在未引入系统统计信息之前，CBO 所计算的成本值实际全是基于 IO 计
算的。
1、集的势（cardinality）
Cardinality 是 CBO 特有的概念，指集合所包含的记录数，即结果集行数。Cardinality 实际上表示对目标 SQL 某个具体执行步骤的执行结果所包
含的记录数的估算，当然，如果针对整个目标 SQL，那么此时的 cardinality 就表示对该 SQL 最终执行结果所包含的记录数的估算。Cardinality
和成本值得估算息息相关，因为 oracle 得到的制定结果集所需要消耗的 IO 资源可以近似的看成随着结果集所包含的记录数递增而递增。所以，SQL
编写的一个原则就是“尽早的过滤更多的数据”。
2、可选择率（Selectivity）
Selectivity 也是 CBO 特有的概念，它是指“施加指定谓语条件后返回的结果集的记录数占未施加任何谓语条件的原始结果集的记录数的比率”，
取值范围为 0~1，其值越小，代表可选择性越好。Selectivity 也可成本值得估算息息相关，可选择率越大，意味着所返回的结果集的 cardinality
越大，所以估算的成本就越大。实际上 CBO 就是利用 selectivity 来计算对应结果集的 cardinality 的，即：
Computed cardinality=original*selectivity
Cardinility 和 selectivity 的值会直接影响 CBO 对于相关执行步骤成本的估算，进而影响 CBO 对于目标 SQL 的执行计划的选择。
3、可传递性
可传递性也是 CBO 的特有属性，它是查询转换中所做的第一件事情，其含义是 CBO 会对目标 SQL 做等价改写，进而提供更多的执行路径给目标
CBO，增加得到最佳执行计划的可能性。RBO 不会对目标 SQL 做等价改写。Oracle 里可传递性分为以下 3 种情况：
1）简单谓语传递
比如原目标 SQL 中的谓语条件是“t1.c1=t2.c1 and t1.c1=10”,则 CBO 可能会给谓语条件额外加上“t2.c1=10”。
2）连接谓语传递
比如原目标 SQL 中的谓语条件是“t1.c1=t2.c1 and t2.c1=t3.c1”,则 CBO 可能会给谓语条件额外加上“t1.c1=t3.c1”。
3）外链接谓语传递
比如原目标 SQL 中的谓语条件是“t1.c1=t2.c1(+) and t1.c1=10”,则 CBO 可能会给谓语条件额外加上“t2.c1(+)=10”。
4、CBO 的局限性
1）CBO 会默认目标 SQL 语句 where 条件中出现的各个列之间出现是独立的，没有任何关联。并且 CBO 会根据这个前提条件来计算 selectivity
和 cardinality，进而估算成本并选择执行计划。但是这种假设并不全是正确的，生产中列与列之间存在关联的现象并不罕见。目前可以用来缓解上
述负面影响的方法是使用动态采样和多列统计信息。但动态采样的准确性取决于采样数据的质量以及数量，而多列统计信息并不适合用于多表之间
有关联的情形，所以这两种方法只能算是缓解，并不算是完美的解决方案。
2）CBO 会假设所有的目标 SQL 都是独立运行的，并且互不干扰，但实际情况却不完全是这样。
3）CBO 对直方图统计信息有多方限制。主要体现在如下 2 个方面：
（1）在 oracle 12c 之前，frequency 类型的直方图所对应的 bucket 的数量不能超过 254，这样如果列的 distinct 数量超过 254，oracle 就会使
用 height balanced 类型的直方图。对于 height balanced 类型的直方图而言，oracle 不会记录所有的 nonpopular value 的值，所以此种情况
下 CBO 选错执行计划的概率会比 frequency 类型的情形要高。
（2）在 oracle 数据库里，如果针对文本类型的字段手机直方图统计信息，则 oracle 只会将文本的前 32 个字符（实际只取前 15 个）取出来并将
其转换为浮点数，然后将浮点数作为上述文本字段的直方图统计信息记录在数据字典里。
4）CBO 在解析多表关联的目标 SQL 时，可能会漏选正确的执行计划。在 oracle 11gR2 中，CBO 在解析这种多表关联的目标 SQL 时，所考虑的
各个表的连接顺序的总和受隐含参数_OPTIMIZER_MAX_PERMUTATIONS 的限制。这意味着目标 SQL 不管有多少种连接顺序，CBO 最多只考虑
其中根据_OPTIMIZER_MAX_PERMUTATIONS 计算出来的有限种可能性。
三、优化器基础知识
1、优化器的模式
优化器模式用于决定 oracle 在解析目标 SQL 时所选择的优化器类型，以及选择使用 CBO 时计算成本的侧重点。在 oracle 数据库中，优化器模式
由参数 OPTIMIZER_MODE 的值决定，通常 OPTIMIZER_MODE 的值为 RULE,CHOOSE,FIRST_ROWS_n(N=1、10、100、1000),FIRST_ROWS
或 ALL_ROWS。OPTIMIZER_MODE 的值得各个含义如下：
1）RULE
RULE 表示优化器使用 RBO 来解析目标 SQL，此时目标 SQL 所涉及的各个对象的统计信息对于 RBO 来说将毫无意义。
2）CHOOSE
CHOOSE 是 oracle 9i 中 OPTIMIZER_MODE 的默认值，他表示 oracle 在解析目标 SQL 时到底使用 CBO 还是 RBO 取决于目标 SQL 所涉及对象
是否有统计信息。具体来说：只要目标 SQL 对象含有统计信息，即使用 CBO，反之，使用 RBO 来解析目标 SQL。
3）FIRST_ROWS_n(N=1、10、100、1000)
FIRST_ROWS_n(N=1、10、100、1000)可以是 FIRST_ROWS_1、FIRST_ROWS_10、FIRST_ROWS_100、FIRST_ROWS_1000 中的任意一个值，
他表示 oracle 在解析目标 SQL 时，oracle 会使用 CBO 来解析目标 SQL，且此时 CBO 在计算各条执行路径的成本时的侧重点在于以最快相应速度
返回前 n 条数据。
4）FIRST_ROWS
FIRST_ROWS 是一个在 oracle 9i 中就过时的一个参数，他表示 oracle 在解析目标 SQL 时会联合使用 CBO 和 RBO。在大部分情况下，oracle 还
是会选用 CBO 作为解析目标 SQL，此时 oracle 的侧重点是以最快的相应速度返回前 n 行。在一些特俗情况下，oracle 会选用 RBO 来解析目标 SQL
而不考虑成本。比如当 OPTIMIZER_MODE 为 FIRST_ROWS 时有一个内置的规则，就是 oracle 如果发现能用相关索引来避免排序，则 oracle 就
会选择该索引所对应的路径而不考虑成本值。
5）ALL_ROWS
of 8
免费下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论