Oracle直方图解析

shuijingping

　　Oracle直方图解析
　　一、何谓直方图：
　　直方图是一种统计学上的工具，并非Oracle专有。通常用于对被管理对象的某个方面的质量情况进行管理，通常情况下它会表现为一种几何图形表，这个图形表是根据从实际环境中所收集来的被管理对象某个方面的质量分布情况的数据所绘制成的，通常会画成以数量为底边，以频度为高度的一系列连接起来的矩形图，因此直方图在统计学上也称为质量分布图。比如下图所示，是一个以关学生化学考试成绩分数分布情况绘制的直方图：

http://my.csdn.net/uploads/201207/27/1343381399_8976.JPG

　　二、Oracle中直方图的作用：
　　既然直方图是一种对被管理对象某一方面质量进行管理的描述工具，那么在Oracle中自然它也是对Oracle中某个对象质量的描述工具，这个对象就是Oracle中最重要的东西——“数据”。
　　在Oracle中直方图是一种对数据分布质量情况进行描述的工具。它会按照某一列不同值出现数量多少，以及出现的频率高低来绘制数据的分布情况，以便能够指导优化器根据数据的分布做出正确的选择。在某些情况下，表的列中的数值分布将会影响优化器使用索引还是执行全表扫描的决策。当 where 子句的值具有不成比例数量的数值时，将出现这种情况，使得全表扫描比索引访问的成本更低。这种情况下如果where 子句的过滤谓词列之上上有一个合理的正确的直方图，将会对优化器做出正确的选择发挥巨大的作用，使得SQL语句执行成本最低从而提升性能。
　　三、Oracle中使用直方图的场合：
　　在分析表或索引时，直方图用于记录数据的分布。通过获得该信息，基于成本的优化器就可以决定使用将返回少量行的索引，而避免使用基于限制条件返回许多行的索引。直方图的使用不受索引的限制，可以在表的任何列上构建直方图。
　　构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划：例如，如果一到两个值构成了表中的大部分数据(数据偏斜)，相关的索引就可能无法帮助减少满足查询所需的I/O数量。创建直方图可以让基于成本的优化器知道何时使用索引才最合适，或何时应该根据WHERE子句中的值返回表中80％的记录。
　　通常情况下在以下场合中建议使用直方图：
　　（1）、当Where子句引用了列值分布存在明显偏差的列时：当这种偏差相当明显时，以至于 WHERE 子句中的值将会使优化器选择不同的执行计划。这时应该使用直方图来帮助优化器来修正执行路径。（注意：如果查询不引用该列，则创建直方图没有意义。这种错误很常见，许多 DBA 会在偏差列上创建柱状图，即使没有任何查询引用该列。）
　　（2）、当列值导致不正确的判断时：这种情况通常会发生在多表连接时，例如，假设我们有一个五项的表联接，其结果集只有 10 行。Oracle 将会以一种使第一个联接的结果集（集合基数）尽可能小的方式将表联接起来。通过在中间结果集中携带更少的负载，查询将会运行得更快。为了使中间结果最小化，优化器尝试在 SQL 执行的分析阶段评估每个结果集的集合基数。在偏差的列上拥有直方图将会极大地帮助优化器作出正确的决策。如优化器对中间结果集的大小作出不正确的判断，它可能会选择一种未达到最优化的表联接方法。因此向该列添加直方图经常会向优化器提供使用最佳联接方法所需的信息。
　　四、如何使用直方图：
　　（1）、创建直方图：
　　通过使用早先的analyze命令和最新的dbms_stats工具包都可以创建直方图。Oracle推荐使用后者来创建直方图，而且直方图的创建不受任何条件限制，可以在一张表上的任何你想创建直方图的列上创建直方图。我们这里主要介绍如何通过dbms_stats包来创建直方图。
　　Oracle 通过指定 dbms_stats 的 method_opt 参数，来创建直方图。在method_opt 子句中有三个相关选项，即 skewonly、repeat 和 auto。
　　“skewonly” 选项，它的时间性很强，因为它检查每个索引中每列值的分布。如果 dbms_stats 发现一个索引中具有不均匀分布的列，它将为该索引创建直方图，以帮助基于成本的 SQL 优化器决定是使用索引还是全表扫描访问。示例如下：
　　begin
　　dbms_stats.gather_table_stats (
　　ownname          => '',
　　tabname=>''，
　　estimate_percent =>dbms_stats.auto_sample_size,

　　method_opt       => 'for all columns>　　cascade=>true,
　　degree           => 7);
　　end;
　　其中degree指定了并行度视主机的CPU个数而定，estimate_percent指定了采样比率，此处使用了auto目的是让oracle来决定采样收集的比率，绘制直方图时会根据采样的数据分析结果来绘制，当然也可以人为指定采样比率。如：estimate_percent=>20指定采样比率为20%，cascade=>true指定收集相关表的索引的统计信息，该参数默认为false，因此使用dbms_stats收集统计信息时抹人事部收集表的索引信息的。
　　在对表实施监视 (alter table xxx monitoring;) 时使用 auto 选项，它基于数据的分布以及应用程序访问列的方式（例如由监视所确定的列上的负载）来创建直方图。示例如下：
　　begin
　　dbms_stats.gather_ table _stats(
　　ownname          => '',
　　tabname=>''，
　　estimate_percent => dbms_stats.auto_sample_size,

　　method_opt       => 'for all columns>　　cascade=>true，
　　degree           => 7
　　);
　　end;
　　重新分析统计数据时，使用repeat选项，重新分析任务所消耗的资源就会少一些。使用repeat选项时，只会为现有的直方图重新分析索引，不再生成新的直方图。示例如下：
　　dbms_stats.gather_ table_stats(
　　ownname => '',
　　tabname=>''，
　　estimate_percent => dbms_stats.auto_sample_size,

　　method_opt => 'for all columns>　　cascade=>true，
　　degree => 7
　　);
　　end;
　　（2）、创建直方图的考虑因素：
　　如果想为某一列创建直方图，示例如下：
　　dbms_stats.gather_ table_stats(
　　ownname => '',
　　tabname=>''，
　　estimate_percent => dbms_stats.auto_sample_size,

　　method_opt => 'for columns>　　cascade=>true，
　　degree => 7
　　);
　　end;
　　其中size 10指定的是直方图所需的存储桶(bucket)数，所谓存储桶可以理解为存储数据的容器，这个容器会按照数据的分布将数据尽量平均到各个桶里，如一张表如果有6000条记录，那么每个桶中平均就会有600条记录，但这只是一个平均数，每个桶中的记录数并不会相等，它会将高频出现记录集中在某一些桶中，低频记录会存放在少量桶中，因此如果存储桶(bucket)数合适的增加就会减少高频记录使用的桶数，统计结果也会更加准确（可以避免被迫将低频记录存入高频桶中，影响优化器生成准确的执行计划）。所以我们最后得到的直方图信息的准确性就由两个数值决定，一个是BUCTET的个数，一个NUM_DISTINCT的个数。所以创建直方图时首先要正确地估计存储桶(bucket)数。默认情况时，Oracle的直方图会产生75个存储桶。可以把SIZE的值指定在1~254之间。
　　（3）、删除直方图信息：
　　在oracle中要删除直方图信息就是设置bucket的数据为1，可以使用如下两个命令来实现：

　　Analyze table 表  compute statistics for table for columns>　　exec dbms_stats.gather_table_stats('用户', '表',cascade=>false, method_opt=>'for columns 列size 1');
　　五、Oracle直方图的种类：

　　Oracle利用直方图来提高非均匀数据分布的选择率和技术的计算精度。但是实际上Oracle会采用另种不同的策略来生成直方图：其中一种是针对包含很少不同值的数据集；另一种是针对包含很多不同的数据集。Oracle会针对第一种情况生成频率直方图，针对第二种情况生成高度均衡直方图。通常情况下当BUCTET < 表的NUM_DISTINCT值得到的是HEIGHT BALANCED（高度平衡）直方图，而当BUCTET = 表的NUM_DISTINCT值的时候得到的是FREQUENCY（频率）直方图。由于满足BUCTET = 表的NUM_DISTINCT值概率较低，所以在Oracle中生成的直方图大部分是HEIGHT BALANCED（高度平衡）直方图。在Oracle 10GR2之前如果使用dbms_stats包来创建直方图，那么如果指定需要创建的直方图的桶的数目与表的NUM_DISTINCT值相等，那么几乎无法创建出一个FREQUENCY（频率）直方图，此时为了得到频率直方图只能使用analyze命令的“for all columns>　　对于含有较少的不同值而且数据分布又非常不均匀的数据集来说，创建FREQUENCY（频率）直方图将会更加合适，因为它不存在会将低频出现的记录存入高频桶中的情况，而HEIGHT BALANCED（高度平衡）直方图在存储桶(bucket)数分配不合理时就可能会出现这种情况。因此一定要在创建直方图前确定使用何种直方图，并且要合理估计存储桶(bucket)数。
　　（1）、频率直方图：
　　Oracle中的频率直方图是按照累积某一列值的出现次数来生成数据分布描述的。我们举一个例子如下所示：我们创建一张表表t1，其中包含一个skew列，我们规定如下数据填充策略：skew=1出现一次，skew=2出现两次......skew=80出现80次。因此代码如下：
　　create table t1 (
　　skew not null,
　　padding
　　)
　　as
　　with generator as (
　　select --+ materialize

　　rownum>　　from all_objects
　　where rownum 　　);
　　end;
　　/
　　查询histograms的信息：
　　ENDPOINT_NUMBER ENDPOINT_VALUE
　　--------------- --------------
　　0 1
　　1 9
　　2 13
　　3 16
　　4 19
　　5 21
　　..................
　　..................
　　..................
　　62 73
　　64 74
　　65 75
　　67 76
　　69 77
　　71 78
　　73 79
　　75 80
　　59 rows selected.
　　发现查询user_tab_histograms只有59行输出，在仔细看看发现Oracle确实是产生了75个bucket，因为ENDPOINT_NUMBER的最大值是75，只不过在记录统计的信息Oracle进行了压缩，省略了一些bucket的输出。上面的输出其实可以展开成如下形式：
　　ENDPOINT_NUMBER ENDPOINT_VALUE
　　60 72
　　61 73
　　62 73
　　63 74
　　64 74
　　65 75
　　66 76
　　67 76
　　68 77
　　69 77
　　70 78
　　71 78
　　72 79
　　73 79
　　74 80
　　75 80
　　在进一步解释其他关于histograms的信息之前，我们要先介绍一个数据density的概念。Density的出现就是为了分析高频率出现的值的影响。没有histograms信息的时候，DENSITY永远等于1/NUM_DISTINCT，但是当我们统计了histograms之后，DENSITY就会发生改变。
　　2.1 DENSITY的计算方法如下：
　　如果想计算一张表的DENSITY，首先运行下列语句收集信息：
　　selectmin(minbkt),
　　maxbkt,
　　substrb(dump(min(val), 16, 0, 32), 1, 120) minval,
　　substrb(dump(max(val), 16, 0, 32), 1, 120) maxval,
　　sum(rep) sumrep,
　　sum(repsq) sumrepsq,
　　max(rep) maxrep,
　　count(*) bktndv,
　　sum(case
　　when rep = 1then
　　1
　　else
　　0
　　end) unqrep
　　from (select val,
　　min(bkt) minbkt,
　　max(bkt) maxbkt,
　　count(val) rep,
　　count(val) * count(val) repsq
　　from (select
　　/*+
　　                  cursor_sharing_exact
　　                  use_weak_name_resl  -- 10g hint
　　                  dynamic_sampling(0)
　　                  no_monitoring
　　                */
　　目标列 val, ntile(10) over(orderby目标列) bkt
　　from目标表名 t
　　where目标列isnotnull)
　　groupby val)
　　groupby maxbkt
　　orderby maxbkt;
　　通过上面收集的信息计算densitiy：
　　（1）、基本公式：
　　(Sum(sumrepsq)-sum(maxrep(i)*maxrep(i))/(sum(sumrep)*(sum(sumrep)-sum(maxrep(i))))
　　注：i表示只有某些特定的行从查询结果中选出来计算maxrep值，这些行必须满足条件：
　　maxbkt>min(minbkt)+1
　　或者
　　min(val)=max(val)
　　（2）、简化公式（在没有高频值出现的情况下使用）：
　　Sum(sumrepsq)/(sum(sumrep)*sum(sumrep))
　　2.2 使用高度均衡直方图成本计算方法：
　　在知道了densitiy的概念后我们再回头关注我们的表t1的histograms信息，我们尤其关注一下ENDPOINT_VALUE=75的情况，之所以关注75是因为我们发现75周围的数值（比如74）都在输出当中出现了多次，只有75只出现了一次，我们自己知道75其实出现的次数应该是不比74在表里出现的次数少的，但不巧的是75在统计Histograms的时候处在了一个特殊的位置，如下图所示：
　　这种不巧会导致Oracle使用不同的机制来计算skew=75的基数，在oracle看来当把histograms数据完全展开成上面查询结果的形式后，凡是在histograms信息里面出现2次或2次以上的数据都是常见的数据，叫做popular的数据，只出现一次或没出现的数据都是un-popular的数据。
　　对于popular的数据，对于=的条件，计算基数的公式是：
　　基数=总行数*（出现次数/bucket总数）
　　比如我们上面的74的基数= 3240 * （2/75) = 86.4，和我们下面的实验相符：
　　SQL> select count(*) from t1 where skew=74;
　　Execution Plan
　　----------------------------------------------------------
　　0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=3)
　　1 0 SORT (AGGREGATE)
　　2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=1 Card=86 Bytes=258)
　　对于un-popular的数据，对于=的条件，计算基数的公式是：
　　总行数*DENSITY，这里就是3240*0.013885925=44.99：
　　SQL> select count(*) from t1 where skew=75;
　　Execution Plan
　　----------------------------------------------------------
　　0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=3)
　　1 0 SORT (AGGREGATE)
　　2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=1 Card=45 Bytes=135)
　　range scan的公式是：
　　如果不跨整个的bucket:
　　选择率 = (required range) / (high value - low value) + 2 * density
　　基数=每个bucket的行数*选择率
　　如果跨整个的bucket：
　　选择率= (required range) / (high value - low value) + 2 * density + 整个的bucket的个数
　　基数=每个bucket的行数*选择率
　　我们一个实例来说明：
　　create table t1
　　as
　　/*
　　with generator as (
　　select --+ materialize
　　rownum n1
　　from all_objects
　　where rownum  null,

　　method_opt => 'for all columns>　　);
　　end;
　　/
　　histograms的信息大致如下：
　　.......
　　ENDPOINT_NUMBER ENDPOINT_VALUE
　　--------------- --------------
　　8 -120
　　9 17
　　10 117
　　11 251
　　12 357
　　13 450
　　19 500
　　20 520
　　21 598
　　22 670
　　首先如果不跨整个的bucket:
　　select
　　small_vc
　　from t1
　　where n1 between 100 and 200;
　　Execution Plan
　　----------------------------------------------------------
　　0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=53 Card=63 Bytes=945)
　　1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=53 Card=63 Bytes=945)
　　按照公式计算：
　　选择率 = (required range) / (high value - low value) + 2 * density =
　　(200–117)/(251-117) + (117-100)/(117-17) + 2 * 0.000177746 =
　　0.619403 + 0.17 + .000355486 =
　　0.789047508
　　基数 = 选择率 * number of rows IN A BUCKET =
　　0.789047508 * 80 = 63.1238
　　如果跨多个bucket：
　　select
　　small_vc
　　from t1
　　where n1 between 400 and 600;
　　Execution Plan
　　----------------------------------------------------------
　　0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=53 Card=685 Bytes=10275)
　　1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=53 Card=685 Bytes=10275)
　　按照公式计算：
　　选择率 = (required range) / (high value - low value) + 2 * density + 整个的bucket的个数
　　该查询中共垮了从13-21的8个bucket，因此计算结果如下：
　　(450 - 400) / (450 - 357) + (600 - 598) / (670 - 598) + 2 * 0.000177746 + 8 =
　　50 / 93 + 2 / 72 + 0.000355486 +8 =
　　0.537634 + 0.0277778 + 0.000355486 + 8=
　　8.565768
　　基数 = 基数 * number of rows IN A BUCKET =
　　8.565867 * 80 = 685.3
　　六、手工生成频率直方图：
　　上面我们曾经讲过，如果一张表中的不同值超过了180，那么是无法生成频率直方图的，因此在这种情况下我们要手动生成频率直方图，手动生成频率直方图的代码如下：
　　declare
　　m_statrec   dbms_stats.statrec;
　　m_val_array dbms_stats.numarray;
　　--  m_val_array    dbms_stats.datearray;
　　--  m_val_array    dbms_stats.chararray;    -- 32 byte char max
　　--  m_val_array    dbms_stats.rawarray;    -- 32 byte raw max
　　m_distcnt number;
　　m_density number;
　　m_nullcnt number;
　　m_avgclen number;
　　begin
　　dbms_stats.get_column_stats(ownname =>目标用户,
　　tabname => '目标表',
　　colname => '目标列',
　　distcnt => m_distcnt,
　　density => m_density,
　　nullcnt => m_nullcnt,
　　srec    => m_statrec,
　　avgclen => m_avgclen);
　　--
　　--  Load column information into the two critical arrays
　　--
　　select目标列, count(*) bulkcollect
　　into m_val_array, m_statrec.bkvals
　　from目标表
　　groupby 目标列
　　orderby 目标列;
　　m_statrec.epc := m_val_array.count;
　　--
　　--  Should terminate here if the count exceeds 254
　　--
　　dbms_stats.prepare_column_values(srec    => m_statrec,
　　numvals => m_val_array);
　　select1 / (2 * count(*)) into m_density from目标表;
　　dbms_stats.set_column_stats(ownname =>目标用户,
　　tabname => '目标表',
　　colname => '目标列',
　　distcnt => m_distcnt,
　　density => m_density,
　　nullcnt => m_nullcnt,
　　srec    => m_statrec,
　　avgclen => m_avgclen);
　　end;
　　该方法通过dbms_stats.get_column_stats包来收集需要创建直方图的目标表和目标列信息，然后通过1 / (2 * num_rows)来生成频率直方图的density，最后通过dbms_stats.set_column_stats将收集的信息和计算结果更新到存放统计信息数据字典中。
　　在频率直方图中有一些特殊的需要注意之处，在频率直方图中density始终等于1 / (2 * num_rows)，对于查询返回的行数来说density非常关键。如果直方图中存在一个特定值一共有X行，但是如果X小于density*（user_tables.num_rows-user_tables_columns.num_nulls），那么优化器将采用后者来取代X。
　　七、直方图与绑定变量：
　　我们知道当我们拥有了histograms的统计信息之后我们就可以使用这些信息计算我们的选择率和基数。但是如果我们使用了绑定变量的时候，情况总会有所改变。
　　首先，在Oracle9i里面新引入了bind variable peeking的功能，这个功能我们前面讲过，是一个带绑定变量的SQL第一次parse的时候，让CBO可以根据绑定的具体的值来决定所要使用的执行计划，而以后如果遇到同样的SQL，即使绑定变量的值不一样，也不会在peek绑定变量的值，而是使用已经生成的计划。这里的一个潜在的问题就是如果我们有了histograms信息，而且我们的数据分布是一小部分数据的分布和其他部分的分布相差很远，那么当我们在做bind variable peeking，如果很不幸运的peek到了那一小部分的数据，就会导致以后所有的同样的SQL都使用了不恰当的执行计划。
　　当然这个bind variable peeking有时候也有意外，那就是如果我们存在shared pool里的执行计划信息或其他相关的信息由于某种原因失效了或者被age out of shared pool，那当我们再次运行这个SQL的时候，就会重新peek绑定变量的值，从而重新生成计划。关于执行计划信息或其他相关的信息的失效或age out，可以通过v$sql的reloads和invalidations字段获得。
　　和绑定变量有关的另一个就是参数cursor_sharing，cursor_sharing这个参数有三个取值：FORCE、EXACT、SIMILAR。
　　有时候，很可能是在OLTP的系统中，为了最大限度的减少SQL PARSE的消耗，让类似的SQL可以尽可能的重用，我们会考虑设置cursor_sharing为force。当cursor_sharing被设置为force的时候，优化器会用系统指定的绑定变量来替代SQL里面所有的literal constants，然后以此为基础判断我们的shared pool里面是不是有可以重用的cursor。按照我们上面的讨论，设置cursor_sharing为force对histograms影响最大的，因为系统指定的绑定变量替换后很可能与histograms收集的数据分布不符。
　　这个问题可以有两个解决办法,一是在我们认为影响会很到的SQL里面加上hint /*+ cursor_sharing_exact */，这回告诉CBO对于这个SQL采用cursor_sharing=exact的策略。另一个解决方法是设置cursor_sharing=similar，按照Oracle文档的说法，设置cursor_sharing为similar也会首先把SQL里的literals替换为绑定变量，并且也会在第一次分析SQL的时候做bind variable peeking，但是当以后重新运行类似的SQL的时候，CBO会查看如果发现新的绑定变量会影响到执行计划（当然，之所以会产生不同的执行计划往往是因为存在histograms），就会重新生成执行计划。经过一些实验，我们可以发现，当设置cursor_sharing=similar的时候，如果我们的条件是range scan或等于的条件，并且条件涉及的列上有histograms信息的时候，CBO会在分析SQL的时候对绑定变量做检查，如果发现新的绑定变量有可能影响SQL的执行计划，则会重新评估并生成新的计划。
　　但是往往我们在优化系统的一个方面的时候会导致其他方面的问题，cursor_sharing=similar就是一个很典型的例子，当我们这样的设置的时候，首先优化器的压力会变大，因为CBO要做很多的重新优化。更严重的问题在于cursor_sharing=similar会导致同样的SQL（除了绑定变量的值不一样之外）在library cache里面拥有很多不同的执行计划，因为我们知道一个SQL下面的所有执行计划都是被一个latch保护的，所以cursor_sharing=similar会导致更严重的latch 争用。因此当我们使用cursor_sharing=similar的时候，除非必要，无需统计histograms信息，因为我们要保证我们为了解决一个问题不会导致其他的更严重的问题。
　　八、使用直方图的注意事项：
　　（1）、Oracle不能保证在join中可以充分使用histograms，如果你有一个列col，Oracle只有你明确的指定了col operation(,=,in,between等等) 常量（这个常量当然也可以是通过bind variable peeking获得的）的时候，才会使用histograms。如下所示：
　　select
　　t1.v1, t2.v1
　　from
　　t1,
　　t2
　　where
　　t1.n2 = 99
　　and t1.n1 = t2.n1;
　　如果我们在t1和t2上都有histograms，Oracle会在t1.n2=99这个条件上使用histograms，但Oracle不能在and t1.n1 = t2.n1这个条件上使用histograms，当然如果我们的条件改成：
　　t1.n2 = 99
　　and t1.n2 = t2.n1
　　这时候histograms就可以使用了，因为Oracle会自己把这个SQL改写成：
　　t1.n2 = 99
　　and
　　t2.n1 = 99
　　（2）、Oracle在分布式查询中，即通过DBLINK查询远程数据库表，此时不会使用远程表的histograms信息
　　（3）、在创建高度均衡直方图时的例外情况：
　　即使在粒度最细的情况下，一个桶也只能大约表示某个值所对应行的1/1250（0.4%）。如果表中存在大于250个不同值时，那么直方图肯定会漏掉一些值。实际情况会更糟糕，如果某一行可能会跨接近两个桶（行数目的0.8%），是无法被Oracle认定为高频出现值的。更糟糕的是，如果存在某个出现频率非常高的值所跨越的桶数超过了平均数，此时就会导致Oracle遗漏掉许多本来会被认定为高频出现的数据行。因此确切估计桶数和选择创建直方图列对于高度均衡直方图来说非常重要。
　　转自：http://blog.csdn.net/javacoffe/article/details/5578206