基于成本的优化器 — 一般错误概念和问题
2024-07-21 02:39:28
供稿：网友
IntrodUCtion 介绍
~~~~~~~~~~~~
本短文着意于消除一些关于基于成本的优化器（CBO）的错误说法，强调一般的错误和问题。
Background 背景
~~~~~~~~~~
为了执行任何一个SQL语句，Oracle都要先导出一个“执行计划(execution plan)”。它基本上就是Oracle如何检索出符合给定SQL语句要求的数据的执行计划。
Oracle7和Oracle8 都有两种可以为SQL语句导出执行计划的优化器:
- 基于规则的优化器（RBO）
继续自Oracle6，它使用一系列严格的规则来决定每个SQL语句的执行计划。假如你知道这些规则，你可以构造一个SQL查询使其以指定的方式访问数据。表的内容对于执行计划没有影响。
这个优化器已经不再被增强了，所以不能使用很多oracle8的特性。
- 基于成本的优化器（CBO）
从Oracle7开始引入，该优化器尝试找到最低成本的访问数据的方法，为了最大的吞吐量或最快的初始响应时间。计算使用不同的执行计划的成本，并选择成本最低的一个。关于表的数据内容的统计被用于确定执行计划。
Fundamental Points 基本点
~~~~~~~~~~~~~~~~~~
对于每个SQL语句，都有很多可能的执行计划。
“最佳计划”永远是“最佳计划”，无论它如何到达。
最佳计划可以由两个意思：
1 此计划使用最小的资源来处理此语句涉及到的所有行。 [叫做ALL_ROWS]
2 此计划以最短的时间返回这个语句的第一行 。 [叫做FIRST_ROWS]
CBO不理解应用的相关特性，也不能完全理解关联表之间的复杂关系的影响。仅有有限的信息可以用来确定最佳计划。
CBO通过计算不同执行方案的估计成本来确定最佳计划，并选用最低成本的计划。因为这个关系到相关成本的假设，所选的计划不一定是真的最好的计划。这种情况经常被当作BUG报告给oracle 技术支持,因为 CBO没有为一个指定方案选择一个最佳的计划。人们通常可以证实因为给定的输入统计试有效的并且缺省的“成本”被牵扯进来。所选中的计划被计算成最佳计划，虽然它不是。无论CBO如何改进提高，总也会有所选的计划不是最优的这种情况。所以，你必须经常地预备优化语句。
RBO的功能已经不再增强。这就意味着一些执行计划只对CBO有效。然而，RBO还将在Oracle 8中继续存在。
Before you Continue 在你继续之前
~~~~~~~~~~~~~~~~~~~
不建议你在Oracle releases 7.0.X中使用CBO.
本文中的信息适用于Oracle releases 7.1 以上（包括Oracle 8.0）。
Base Statistics 基础统计
~~~~~~~~~~~~~~~
为了要给CBO最多的信息（有机会选择好的执行计划），你必须对所有将被查询的表做ANALYZE。
带有ESTIMATE选项的ANALYZE操作对于一些表能够产生不正确的结果，尤其是那些取样较小的表。这不是个BUG,而是每个统计取样方法的特性。假如所选取样不能代表整个数据集，你就不能期待产生正确的统计。
在Oracle 7.1 和7.2 中，列的值被假定为是均匀分布的。这是在这些版本中的一个重要的限制，完全和精确的统计也不能指出实际数据的分布情况。这一限制在Oracle release 7.3 以上版本被部分解决了，能够保存列值的分布信息 - 但是这些额外的信息可能对某些类型的查询没有实际的帮助，请看后面的章节中关于Bind Variables 的注重事项。
在考虑使用ANALYZE时，要考虑如下的重要问题：
- 对一个带索引的表的ANALYZE，将分析其相关索引。 （在Oracle 7.3 中可能值分析表而不分析索引。）
- 假如你对一个表进行ANALYZE ... ESTIMATE 分析，那么然后在其相关索引上做ANALYZE COMPUTE分析是很明智的。这样可以确保被索引字段的统计是准确的。
- 分析索引不用到临时表空间
- 假如分析一个索引而不分析其基表，在这一单一基础上CBO不会被选中。
- 假如你需要使用ESTIMATE- 估计（例如，由于时间的限制），建议你在几个不同的取样大小上进行 ANALYZE ... ESTIMATE， 来确定每个对象的理想的取样大小。总的目标是找到一个能在最短的时间内产生准确的统计的取样大小。较好的开始点是 10% - 15%。
- 进行超过50%的ANALYZE ... ESTIMATE 就会导致/变成ANALYZE ... COMPUTE。
- 7.1.6 以前的版本在进行ANALYZE ... ESTIMATE 时，会有ORA 600 错。
- 不要分析数据字典表（SYS表）除非你有足够充分的理由。关于这一点有和一些文档或README文件相矛盾，他们说DBMS_UTILITY.ANALYZE_SCHEMA 可以用于分析SYS表。虽然DBMS_UTILITY.ANALYZE_SCHEMA 可以分析SYS用户，但Oracle没有对这些被分析的表进行衰退测试，可能会造成死锁或效率问题。
Optimizer Goal / Mode 优化目标和模式
~~~~~~~~~~~~~~~~~~~~~
采用什么样的优化器和其操作方式是由下面的因素决定的：
Object Type 对象类型
- 某些对象类型是基于规则的优化所不知道的。
例如：索引表（IOT）RBO根本不熟悉，在牵扯IOT的查询中将自动使用CBO.
Parallel Degree > 1 on a table 表上的并行度大于1
- 假如查询中的某个表的并行度大于一，CBO都将被采用而不管提示、OPTIMIZER_MODE或OPTIMIZER_GOAL的值是否为"RULE"。适用于Oracle 7.3 以上。
- 在Oracle 8.0.5 和 Oracle 8.1.5 releases 中假如任何索引的并行度超过1，也将采用CBO。仅适用于Oracle 8.0.5和Oracle 8.1.5。
Hints 提示
- 除了RULE之外的任何提示都会导致使用CBO。HINT不能被任何参数关掉，这一点非常重要。
session level会话级 OPTIMIZER_GOAL
- 假如没有给定以上的条件，优化器的选用由会话级的参数OPTIMIZER_GOAL决定。假如上面的一个条件给定了，OPTIMIZER_GOAL就不起作用了。
假如OPTIMIZER_GOAL设为RULE，将采用RBO，而不管任何表的统计。
假如OPTIMIZER_GOAL设为CHOOSE，对于只要有一个表被分析过的查询，都将选用ALL_ROWS 。
Init.Ora OPTIMIZER_MODE 参数
- 会话级的OPTIMIZER_GOAL参数的缺省设置是init.ora文件中的OPTIMIZER_MODE的值。
PL/SQL 块（包括匿名块和存储过程）应使用显式的提示(hint)来确定实际的优化方法。没有指定提示、并行的或“CBO-only”的对象的情况下，PL/SQL 块中的SQL语句采用的优化器，见下：
INIT.ORA OPTIMIZER_MODE
Mode used in PLSQL
RULE
RULE
CHOOSE
ALL_ROWS
ALL_ROWS
ALL_ROWS
FIRST_ROWS
ALL_ROWS
Summary Optimizer Mode: 优化模式的总结
~~~~~~~~~~~~~~~~~~~~~~~
对于以上的文章使我们清楚的确定采用何种优化器的一些事情，总结如下：
Description
Table Statistics
Mode Used
Non-RBO Object(Eg:IOT)
n/a
#1
Parallelism > 1
n/a
#1
RULE hint
n/a
RULE
ALL_ROWS hint
n/a
ALL_ROWS
FIRST_ROWS hint
n/a
FIRST_ROWS
*Other Hint
n/a
#1
OPTIMIZER_GOAL=RULE
n/a
RULE
OPTIMIZER_GOAL=ALL_ROWS
n/a
ALL_ROWS
OPTIMIZER_GOAL=FIRST_ROWS
n/a
FIRST_ROWS
OPTIMIZER_GOAL=CHOOSE
NO
RULE
OPTIMIZER_GOAL=CHOOSE
YES
ALL_ROWS
#1 除非OPTIMIZER_GOAL 设置为FIRST_ROWS,都将采用ALL_ROWS 。在PLSQL中，将采用ALL_ROWS。
*Other Hint 其他提示的意思是指除了RULE, ALL_ROWS 或FIRST_ROWS之外的提示。
General Optimizer Notes 优化器的一般注重事项
~~~~~~~~~~~~~~~~~~~~~~
在看待优化器问题的时候，应考虑如下几点：
- ALL_ROWS 倾向于全表扫描（full table scans）。
- FIRST_ROWS 倾向于索引访问（ index access）。
- CBO缺省使用ALL_ROWS计算成本。
- 在Oracle 7.3之前，CBO不会为了迎合并行查询（ Parallel Queries）而调整成本。
- 在Oracle 7.3之前，CBO认为字段的值载最大和最小之间是均匀分布的；这之后，可以根据请求存储柱状图统计。
- 所有等于RBO的情况，以表在FROM子句中从右到左的顺序为驱动顺序(Driving Order)。
CBO根据由收集到的统计信息而导出的成本，来确定连接顺序(Join Order).
假如没有统计信息，CBO就将以表在FROM子句中从左到右的顺序为驱动顺序(Driving Order),正好和RBO相反。
- CBO将结合当前表的高水位信息使用ANALYZE信息。因此，一个语句的执行计划是可能因时间的不同而改变的。
- 注重：TRUNCATE重置了表的“高水位”，但是不修改表的统计信息，而是留下了该表的旧的CBO信息。
- 当执行各种连接时，一些连接组合将被排除以降低确定一个执行计划所需要的整体时间花费。总之，每个连接顺序都要和目前为止最好的一个做比较，显然部分优化的方案将被排除。
PRoblem SQL Statements 问题SQL语句
~~~~~~~~~~~~~~~~~~~~~~
假如 C
