已归录
几个基本概念
在讲解直方图之前，先解释一下如下几个概念，后面要涉及到。
基数：某列上不同值的个数就是该列的基数，即 select count(distinct 列名) from 表名。
数据分布：表中某列上不同值及其个数的统计。即 select 列名, count(*) from 表名 group by 列名;
选择性：基数/总行数*100% （经验是选择性大于 20% 就可以随便建索引）
脚本：找出选择性大于20%，其上没创建索引，且该列出现在了 where 中的表及列。
什么是直方图
直方图是针对列来说的，某列上的直方图表示该列上每个值在该列中的数量统计，直方图可以用来让优化器判断查询是否走索引（经验值是返回行数占总行数 5% 以内走索引，5% 以上走全表）。
我们通过如下的例子来直观感受一下直方图。
CTAS 方式创建 dba_objects 的复制表 TEST，在 owner 列上创建索引，在不收集该表统计信息的情况下，Oracle 会动态采样，select * from TEST where owner = 'XXX'，如果 where 条件是 owner=’SYS’ 就不走索引，如果条件是 owner=’SCOTT’ 就会走索引。【通过观察执行计划的 Rows 列可以知道，当条件是 SYS 时，Oracle 认为会返回表中的大部分行，当条件是 SCOTT 时，Oracle 认为仅返回少量行，这也就是为什么 SYS 走全表 SCOTT 走索引的原因】
下面对该表收集统计信息：
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
OWNNAME => 'SCOTT',
TABNAME => 'TEST',
ESTIMATE_PERCENT => 100,
METHOD_OPT => 'for all columns size 1',
NO_INVALIDATE => FALSE,
DEGREE => 1,
CASCADE => TRUE);
END;
/"size 1" 表示不收集直方图，收集完统计信息后，SYS 走索引了，从执行计划的 Rows 列可以看出，Oracle 认为要返回的行数变少了。但实际上，现在不论是 SYS 还是 SCOTT，抑或是任何值，执行计划中的 Rows 列的值都是一样的，这个值其实就是这一列的总行数/基数，在统计信息没有收集直方图的情况下， Oracle 认为该列上每个值出现的行数是均等的，那么，这个统计信息其实是有问题的。
我们使用下面的语句查询该表的统计信息，它查询了表的总行数，每个列的基数值，可选性和直方图信息。
set lines 200;
set pages 200;
select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner=b.owner
and a.table_name=b.table_name
and a.owner='&INPUT_USER_NAME'
and a.table_name='&INPUT_TABLE_NAME';
COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------ ---------- ----------- ----------- --------------- -----------
OWNER 86971 31 .04 NONE 1
OBJECT_NAME 86971 52427 60.28 NONE 1
SUBOBJECT_NAME 86971 143 .16 NONE 1
OBJECT_ID 86971 86971 100 NONE 1
DATA_OBJECT_ID 86971 9084 10.44 NONE 1
OBJECT_TYPE 86971 45 .05 NONE 1
CREATED 86971 904 1.04 NONE 1
LAST_DDL_TIME 86971 997 1.15 NONE 1
TIMESTAMP 86971 1035 1.19 NONE 1
STATUS 86971 1 0 NONE 1
TEMPORARY 86971 2 0 NONE 1
GENERATED 86971 2 0 NONE 1
SECONDARY 86971 2 0 NONE 1
NAMESPACE 86971 21 .02 NONE 1
EDITION_NAME 86971 0 0 NONE 0可以看到，目前统计信息是不包含直方图的。
下面我们再次对该表收集统计信息：
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'SCOTT',
tabname => 'TEST',
estimate_percent => 100,
method_opt => 'for all columns size skewonly',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
/size skewonly 表示 Oracle 会根据列上的数据分布来决定是否收集直方图，基本上可以理解为对所有列收集直方图。
之前在统计信息中 HISTOGRAM 列值是 NONE，重新收集统计信息后，再次查询该表的统计信息，就可以看到 HISTOGRAM 列和 NUM_BUCKETS 列有值了:
COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------ ---------- ----------- ----------- --------------- -----------
OWNER 86977 31 .04 FREQUENCY 31
OBJECT_NAME 86977 52427 60.28 HEIGHT BALANCED 254
SUBOBJECT_NAME 86977 146 .17 FREQUENCY 146
OBJECT_ID 86977 86977 100 NONE 1
DATA_OBJECT_ID 86977 9090 10.45 HEIGHT BALANCED 254
OBJECT_TYPE 86977 45 .05 FREQUENCY 45
CREATED 86977 907 1.04 HEIGHT BALANCED 254
LAST_DDL_TIME 86977 1001 1.15 HEIGHT BALANCED 254
TIMESTAMP 86977 1039 1.19 HEIGHT BALANCED 254
STATUS 86977 1 0 FREQUENCY 1
TEMPORARY 86977 2 0 FREQUENCY 2
GENERATED 86977 2 0 FREQUENCY 2
SECONDARY 86977 2 0 FREQUENCY 2
NAMESPACE 86977 21 .02 FREQUENCY 21
EDITION_NAME 86977 0 0 NONE 0此时，再次查询执行上面的查询语句，发现 SYS 走的全表扫描，SCOTT 走的索引，且 Rows 列的值也对了。
可见，不收集直方图，优化器就认为数据分布均衡。收集直方图后，就把列上不同值的个数记录下来了：
select 列名, count(*) from 表名 group by 列名;
理解 buckets（桶）与直方图类型
11g的两种直方图类型：
1). Frequency Histogram（频率直方图）
- 当数据列的不同值个数 <= buckets 数量时，会采用频率直方图，数据列的每个不同值都分配到一个 bucket，每个 bucket 包含该列值的统计行数
2). Height Balanced Histogram（高度平衡直方图）
- 当数据列的不同值个数 > buckets 时，会采用高度平衡直方图
通常我们不会对 buckets 数量进行指定，11g 中桶的最大数量为 254，此时就看数据列不同值的个数跟 254 来比较。
如果上面的查询中，HISTOGRAM 列的值是 NONE，表示该列上没有直方图，一般是如下原因：
- 基数等于总行数（主键）
- 基数等于0（没有值）
- 自动采集时 Oracle 认为该不需要采集
创建直方图的语法
method_opt Accepts either of the following options, or both in combination:
- FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
- FOR COLUMNS [size clause] column [size_clause] [,column [size_clause]...]
size_clause is defined as : SIZE {integer | REPEAT | AUTO | SKEWONLY}
integer : Number of histogram buckets. Must be in the range [1,254].
- 如果不清楚 n 值应该取多少，完全可以直接 254，oracle 会根据实际上有多少个不同值就用多少桶，不够 254 也不会用完 254。
REPEAT : Collects histograms only on the columns that already have histograms
- 原文的意思是只在有直方图的列上收集直方图信息，可以简单理解为之前怎么收集的，就还是怎么收集
AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.
- auto 简单理解就是只对出现在 where 中的并且数据分布不均衡的列才收集直方图（11g 放心大胆随便用，11g 之前注意绑定变量窥探）
SKEWONLY : Oracle determines the columns on which to collect histograms based on the data distribution of the columns.
- skewonly 原文的意思是 Oracle 根据列上的数据分布决定是否收集直方图信息，基本上可以理解为是对所有列收集直方图
说明：AUTO 和 SKEWONLY 都是由 oracle 来决定是否做直方图，区别在于 SKEWONLY 时，只要 Oracle 觉得数据分布可能不均，就会收集列的柱状图。而 AUTO 时，即使某一个 column 它的数据分布不均，但只要这个 column 的 workload 很低，或者说没有 workload，那么 Oracle 就不会收集这个 column 的 histogram。工作中一般对所有列 AUTO 或者指定对某个列收集histogram。
哪些列应该采集直方图
如果要对固定的列采集直方图，如何判断一个列是否应该采集直方图呢？
1.基数很低且数据分布不均衡
2.列在 where 条件里面
3.列上没有收集直言图
如果压测阶段能够比较真实的模拟业务数据，如下方法可供参考：
1.一开始不采集直方图
2.压测发现慢的 SQL 语句，分析是否需要对某列采集直方图信息：for columns xxx size auto
3.后期 for all columns size repeat
