Oracle优化器和直方图

huashan8

　　一、优化器的分类
　　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)的个数 'EMP',

　　METHOD_OPT => 'FOR COLUMNS>　　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>　　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