Oracle优化器和直方图

一、优化器的分类

Oracle7和Oracle8 都有两种可以为SQL语句导出执行计划的优化器:

- 基于规则的优化器（RBO）

　　继承自Oracle6，它使用一系列严格的规则来决定每个SQL语句的执行计划。如果你知道这些规则，

你可以构造一个SQL查询使其以指定的方式访问数据。表的内容对于执行计划没有影响。

　　这个优化器已经不再被增强了，所以不能使用很多oracle8的特性。

- 基于成本的优化器（CBO）

　　从Oracle7开始引入，该优化器尝试找到最低成本的访问数据的方法，为了最大的吞吐量或最快的初始响应时间。

计算使用不同的执行计划的成本，并选择成本最低的一个。关于表的数据内容的统计被用于确定执行计划。

　　 Fundamental Points 基本点

　　对于每个SQL语句，都有很多可能的执行计划。“最佳计划”永远是“最佳计划”，无论它如何到达。

　　最佳计划可以由两个意思：

　　1 此计划使用最小的资源来处理此语句涉及到的所有行。 [叫做ALL_ROWS]

　　2 此计划以最短的时间返回这个语句的第一行 。 [叫做FIRST_ROWS]

　　CBO不理解应用的相关特性，也不能完全理解关联表之间的复杂关系的影响。仅有有限的信息可以用来确定最佳计划。

　　CBO通过计算不同执行方案的估计成本来确定最佳计划，并选用最低成本的计划。因为这个关系到相关成本的假设，

所选的计划不一定是真的最好的计划。这种情况经常被当作BUG报告给oracle 技术支持,因为 CBO没有为一个指定方案选择一个最佳的计划。

二、直方图

从版本10g开始Oracle会自动收集Histogram，Histogram是否收集取决于col_usage$中记录的关于该列用作SQL中谓词条件的信息和数据分布情况.

SMON定期将shared pool中的谓词使用状况刷新到col_usage$表中。

例如:

Select * from tab where colA=1;

则记录为对colA充当 EQUALITY_PREDS—>equality predicates等式谓词

二、CBO何时使用直方图

有2个地方Oracle Optimizer优化器会用到Histogram:

•过滤谓词的选择性评估

•Join连接基数(Cardinality)评估

•在做Join连接基数(Cardinality)评估时，往往差之毫厘谬之千里：

•优化器可能选择错误的Join连接方式或顺序。

•例如分页排序查询 因为基数评估误差导致去用了HASH JOIN…..

三、直方图的bucket桶数

Histogram的Buckets桶数

•对于绝大多数情况默认的75个桶总是合适的

•最大桶数= 最小值(254,其他因素限制桶数)

•若频繁出现地distinct值的数据并不多，则将桶数设置为大于这个数目往往是有益的。

四、直方图的分类

版本12c之前有2种类型Histogram

1、Height Balanced Histogram高度平衡直方图

•列值被分成多个buckets

•每个bucket包含大致一样数目的行数

•当NDV>254时会采用高度平衡直方图(注意dbms_stats采样到的NDV未必是实际的NDV)

特点：

•每个buckets桶里的行数都大致相同，除了最后一个桶

•最后一个桶中的可能比其他桶中的少

•每个桶中最大值成为bucket value endpoint_value

•每个value值占有一个桶的一部分，按比例

2、Frequency Histogram(Value-Based )频率直方图

•该列上每一个值都会具有频率信息

•当NDV( number of distinct values)的个数<=最大桶数buckets 254个时使用频率直方图

特点：

•每一个bucket桶代表一个列值

•列上的所有的值均有对应的桶

•当NDV(采样到的)<min(指定的buckets数量,254)时创建频率直方图

•Density = 1 / ( 2 * NumRows * A4Nulls)

五、查看

   列统计信息可以按柱状统计图的形式存储，柱状统计图为列数据提供了准确的描述信息，尤其是在数据列有倾斜（列为某种值的记录行数非常多，而某种值的记录行数又非常少）的时候。Oracle中包含两种柱状统计图，高度正方图（height-balanced）和频率直方图（frequency histograms），该类型存储在视图*TAB_COL_STATISTICS (* 可为USER或 DBA)，其取值为HEIGHT BALANCED, FREQUENCY或 NONE

（1）高度直方图高度直方图中，数据列的值被分成组，每组包含的数据数据可数基本相等。比如有一个列c其值在1到100之间，当均匀分布，其列的高度直方图如下图：

每个间隔中都包含数据列的10行数据；当不均匀分布时其列直方图如下图

这时，大多数数据行的值为5。查询时最有价值的统计信息就是各个组范围的两个端点的值.

查看统计信息的语法如下：

BEGIN

DBMS_STATS.GATHER_table_STATS (OWNNAME => 'scott', TABNAME => 'EMP',

METHOD_OPT => 'FOR COLUMNS SIZE 6 SAL');

END;

/

SELECT column_name, num_distinct, num_buckets, histogram

FROM USER_TAB_COL_STATISTICS

WHERE table_name = 'EMP' AND column_name = 'SAL';

结果：

SELECT endpoint_number, endpoint_value
FROM USER_HISTOGRAMS
WHERE table_name = 'EMP' and column_name = 'SAL'
ORDER BY endpoint_number;
结果：

上图中，每行对应高度直方图中的每个间隔。

（2）频率直方图在频率直方图中，列中每个唯一数据相当于高度直方图中的每个间隔，其高度对应该数据在列中出现的次数。当列中distinct值得个数小于或等于直方图的分格段的个数（即num_buckets的值）时，频率直方图会被自动建立。查看频率直方图的语法如下：

BEGIN

DBMS_STATS.GATHER_table_STATS (OWNNAME => 'scott', TABNAME => 'EMP',

METHOD_OPT => 'FOR COLUMNS SIZE 12 SAL');

END;

SELECT column_name, num_distinct, num_buckets, histogram

FROM USER_TAB_COL_STATISTICS

WHERE table_name = 'EMP' AND column_name = 'SAL';

结果：

SELECT endpoint_number, endpoint_value
FROM USER_HISTOGRAMS
WHERE table_name = 'EMP' and column_name = 'SAL'
ORDER BY endpoint_number;
结果：

整理的时候发现下面两篇文章也不错

http://blog.csdn.net/outget1/article/details/4833881

http://hi.baidu.com/wuzhanyin/item/6b0d35dff9316439e3108fed

本文出自 “无双城” 博客，请务必保留此出处http://929044991.blog.51cto.com/1758347/1264534