直方图

Oracle 直方图解析 一、何谓直方图 直方图是一种统计学上的工具，并非 Oracle 专有。通常用于对被管理对象的某个方面 的质量情况进行管理，通常情况下它会表现为一种几何图形表，这个图形表是根据从实际环 境中所收集来的被管理对象某个方面的质量分布情况的数据所绘制成的，通常会画成以数量 为底边，以频度为高度的一系列连接起来的矩形图，因此直方图在统计学上也称为质量分布 图。 二、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 => user, tabname=>''， estimate_percent =>dbms_stats.auto_sample_size, method_opt => 'for all columns size skewonly', cascade=>true, degree=> 2); 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 => USER, tabname=>''， estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size auto', cascade=>true， degree => 2 ); end; 重新分析统计数据时，使用 repeat 选项，重新分析任务所消耗的资源就会少一些。使 用 repeat 选项时，只会为现有的直方图重新分析索引，不再生成新的直方图。示例如下： BEGIN dbms_stats.gather_ table _stats( ownname => USER, tabname=>''， estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size repeat', cascade=>true， degree => 2 ); END; （2）、创建直方图的考虑因素 如果想为某一列创建直方图，示例如下： begin dbms_stats.gather_table_stats( ownname => '', tabname=>''， estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for columns size 10 列名', 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 id size 1; 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 size 表的 NUM_DISTINCT 值”，这在某种程度上来说是一个退步，但这个问题在 Oracle 10GR2 后被修正。但是如果列 中有 180 - 200 个不同值时，还是无法创建 FREQUENCY（频率）直方图.此时需要手工建立 直方图,并写入数据字典方能完成 FREQUENCY（频率）直方图的创建。 对于含有较少的不同值而且数据分布又非常不均匀的数据集来说，创建 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 id from all_objects where rownum <= 5000 ) select /*+ ordered use_nl(v2) */ v1.id, rpad('x',400) from generator v1, generator v2 where v1.id <= 80 and v2.id <= 80 and v2.id <= v1.id order by v2.id,v1.id; 收集生成 frequency histograms: begin dbms_stats.gather_table_stats( user, 't1', cascade => true, estimate_percent => null, method_opt => 'for all columns size 120' ); end; / 收集后查询 user_table_histograms： select endpoint_number, endpoint_value from user_tab_histograms where column_name = 'SKEW' and table_name = 'T1' order by endpoint_number; 结果如下： ENDPOINT_NUMBER ENDPOINT_VALUE --------------- -------------- 1 1 3 2 6 3 10 4 15 5 21 6 .................. .................. .................. 3081 78 3160 79 3240 80 我们可以看出频率直方图对 t1 里每一个 distinct 都保留了一行（所以才说 frequency histograms 是只能用在 distinct key <255 的表上，因为 histograms 的最大 bucket 数是 254）从这个输出里面我们可以看到等于 1的值有一个，等于 1和 2的值有 3个（因此等于 2的值有 2 个），等于 1/2/3 的值有 6 个。。。。。。因此从这个角度来说，我们常常把 frequency histograms 称为累计的 frequency histograms。 在 Oracle 10GR2 之前用 dbms_stats 来收集统计信息的时候，你会发现如果你使用的 bucket 是 80，你不会得到一个 frequency histograms 的统计信息，而会得到一个 height balanced histograms 的统计信息，因此只能使用 analyze 命令的 for all columns size 80 来得到 frequency histograms 的统计信息。经过多次实验你会发现对于表 t1，如果你想得 到 frequency histograms，你的 bucket 至少得设置为 107，这个问题在 Oracle10GR2 后得 到了修正，但是因为 histograms 的最大 bucket 为 254 个，所以对于 distinct key 是大于 180 的话，用 dbms_stats 还是永远得不到 frequency histograms 的，这时候必须使用 analyze。或者自己来生成统计信息并且在生成后更新数据字典。 接下来我们看一下频率直方图对于查询语句成本的影响。在讨论这个话题前我们要先明 晰一个概念——查询基数，所谓查询基数可以简单的理解为一个查询语句将要预计返回的查 询结果的行数，计算基数的基本公式为：总记录数*选择率，对于选择率的计算比较复杂， 不同情况下会采用不同的计算方法，但通常都会参照表的 num_distinct 值，在有些使用绑 定变量的情况下甚至直接用 1/num_distinct 值来作为选择率。在得到基数后优化器会使用 基数来生成查询成本，因此基数对于查询成本来说非常重要。因此按照对于 SKEW 列的不同 过滤条件会生成不同得基数如下所示： select * from t1 where SKEW=40;该语句基数会得到 40，因为 SKEW=40 的值共出现了 40 次； select * from t1 where SKEW between 1 and 2; 该语句基数会得到 3, 因为 SKEW=1 和 2 的值共出现了 3次； select * from t1 where SKEW=40.5; 该语句基数会得到 1，因为 SKEW=40.5 的值不存 在。CBO 在我们认为是 0行的地方统一的看作是 1行，实际上除非你的条件里面加入 1=0 这 样的条件，否则 CBO 一般是不允许 cardinality 为 0 的。 select * from t1 where SKEW between 20.5 and 24.5;对于 t1 表该语句将会得到与 select * from t1 where SKEW between 21 and 24;相同的基数。 select * from t1 where skew=:skew 得到的基数将会是 41。对于带有绑定变量的等 值查询，选择率为 1/num_distinct。 select * from t1 where skew>=:skew 得到的基数将会是 162。 对于带有绑定变量的 >=、>、<=、< 选择率固定为 5%。 select * from t1 where skew between :skew1 and :skew2 得到的基数是 8。对于带 有绑定变量的范围查找，选择率为 5%*5%=0.25%； 总结如下： � 基本查询基数会采用作为过滤条件列的不同值数量作为基数； � 对 于 绑 定 变 量 如 果 对 于 等 值 比 较 如 ： column=:bind 的 选 择 率 会 直 接 使 用 1/num_distinct 值，然后在用选择率*数据行数得到基数。对于范围比较的绑定变量， 选择率会固定为 5%，如使用 column between :bind1 and :bind2 那么选择率将等于 5%*5%=0.25%； � 如果生成直方图的关键列值作了修改发生了变化，那么要重新生成直方图，否则得到的 数据描述将会是过时的； � 对于频率直方图我们可以看出，对于走出最大值/最小值范围的区间谓词 CBO 会得到更 好的结果； （2）、高度均衡直方图 当我们给出的 bucket 数目不够大的时候，Oracle 会以 height balanced 的方式记录 histograms，也就是按照 buckets 的值把所有的数据平分，如果 bucket 是 50，就把所有的 数据平分为 50 等份，再告诉我们处于每个边界的值。如下所示： create table t1 ( skew not null, padding ) as with generator as ( select --+ materialize rownum id from all_objects where rownum <= 5000 ) select /*+ ordered use_nl(v2) */ v1.id, rpad('x',400) from generator v1, generator v2 where v1.id <= 80 and v2.id <= 80 and v2.id <= v1.id order by v2.id,v1.id create index t1_i1 on t1(skew); 然后我们以 bucket 为 75 建立 histograms 信息。 begin dbms_stats.gather_table_stats( user, 't1', cascade => true, estimate_percent => null, method_opt => 'for all columns size 75' ); 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，首先运行下列语句收集信息： select min(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 = 1 then 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(order by 目标列) bkt from 目标表名 t where 目标列 is not null) group by val) group by maxbkt order by 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 总数） 其中“出现次数/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) 注： 如果查询 select count(*) from t1 where skew=80 发现估算出来的基数是 65。 SQL> select * from t1 where skew=80; Execution Plan ---------------------------------------------------------- Plan hash value: 838529891 ---------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------- | 0 | SELECT STATEMENT | | 65 | 26260 | 32 | |* 1 | TABLE ACCESS FULL| T1 | 65 | 26260 | 32 | ---------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("SKEW"=80) 由于 80是处在最后一个桶子上，但是 ORACLE 并不知道这个最后桶子的上界值是否还是 80。ORACLE 给出了 50%的可能性。 此时的选择率为：3240*((1+0.5)/75)=65 � 对于 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) 这个计算方式在 10.2.0.4 后又改变了。 以下是我的测试： SQL> conn ysp/ysp Connected. SQL> select * from v$version; BANNER -------------------------------------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio NLSRTL Version 10.2.0.4.0 – Production SQL> select density from user_tab_columns where table_name='T1' AND COLUMN_NAME='SKEW'; DENSITY ---------- .013973812 SQL> SELECT 3240*.013973812 FROM DUAL; 3240*.013973812 --------------- 45.2751509 SQL> set autot traceonly exp SQL> select * from t1 where skew=1; Execution Plan ---------------------------------------------------------- Plan hash value: 2755060842 --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 29 | 11716 | 30 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 29 | 11716 | 30 | |* 2 | INDEX RANGE SCAN | T1_I1 | 29 | | 1 | --------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("SKEW"=1) SQL> alter session set optimizer_features_enable ='10.1.0'; Session altered. SQL> select * from t1 where skew=1; Execution Plan ---------------------------------------------------------- Plan hash value: 838529891 ---------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------- | 0 | SELECT STATEMENT | | 45 | 18180 | 32 | |* 1 | TABLE ACCESS FULL| T1 | 45 | 18180 | 32 | ---------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("SKEW"=1) � 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 <= 5000 ) */ select /*+ ordered use_nl(v2) */ 3000 + trunc(2000 * dbms_random.normal) n1, lpad(rownum,10) small_vc, rpad('x',100) padding from generator v1, generator v2 where rownum <= 10000; insert into t1 select 500 * (1 + trunc((rownum-1)/500)), lpad(rownum,10), rpad('x',100) from t1; commit; begin dbms_stats.gather_table_stats( user, 't1', cascade => true, estimate_percent => null, method_opt => 'for all columns size 250' ); 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(*) bulk collect into m_val_array, m_statrec.bkvals from 目标表 group by 目标列 order by 目标列; 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); select 1 / (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_tab_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.n