oracle统计信息和直方图

对统计信息的认识

统计信息：

1.表中的统计信息

2.索引列的统计信息

3.一般列的统计信息

表的统计信息：

表行数，使用的块数，空的块数，块的使用率，行迁移和链接的数量，pctfree，pctused的数据，行的平均大小

select NUM_ROWS, --表中的记录数
BLOCKS, --表中数据所占的数据块数
EMPTY_BLOCKS, --表中的空块数
AVG_SPACE, --数据块中平均的使用空间
CHAIN_CNT, --表中行连接和行迁移的数量
AVG_ROW_LEN --每条记录的平均长度
from user_tables

索引列的统计信息 :

索引的深度（B-Tree的级别），索引叶级的块数量，集群因子（clustering_factor), 唯一值的个数

select BLEVEL, --索引的层数
LEAF_BLOCKS, --叶子结点的个数
DISTINCT_KEYS, --唯一值的个数
AVG_LEAF_BLOCKS_PER_KEY, --每个KEY的平均叶块个数
AVG_DATA_BLOCKS_PER_KEY, --每个KEY的平均数据块个数
CLUSTERING_FACTOR --群集因子
from user_indexes

列的统计信息

唯一的值个数，列最大小值，密度（选择率），数据分布（直方图信息），NUll值个数

select NUM_DISTINCT, --唯一值的个数
LOW_VALUE, --列上的最小值
HIGH_VALUE, --列上的最大值
DENSITY, --选择率因子（密度）
NUM_NULLS, --空值的个数
NUM_BUCKETS, --直方图的BUCKET个数
HISTOGRAM --直方图的类型
from user_tab_columns

直方图

直方图：是统计信息一种，对数据分布的统计，目的是为了更精确的得到选择率和基数，CBO才能估计出最优的执行计划。

统计信息在：表，列，索引上能够获取的统计信息，但如果有一个sql：

select * from table where x=1;（在x列上有索引）

那Oracle如何选择是表扫描，索引查询数据，通过对列列的统计信息，但没有直方图，CBO优化器不知道x=1的数据有多少？无法提供数据参考。有两钟情况：

table表中：

1. x=1的数据比如就一条 CBO优化器：应该索引查找

2. x=1的数据有很多条，CBO优化器：这时应该表扫描

如果索引列没有直方图，索引上有最大，最小值，总行数，那就不能知道x=1在表中大概有多少行。如果只是有索引上统计信息，就无法获取大概的数据分布
CBO也就得不到正确的结果。

对于数据分布均匀的列，直方图没意义，对应列中数据分布比较倾斜的列（不均匀），直方图就非常有用。CBO就可以估计大概的数据分布，计算查询的选择率和基数也更精确。
当Oracle 做直方图分析时，会将要分析的列上的数据分成很多数量相同的部分，每一部分称为一个bucket,这样CBO就可以非常容易地知道这个列上的数的分布情况，这种数据的分布将作为一个非常重要的因素纳入到执行计划成本的计算当中。
Oracle 有两种类型的直方图： height-balanced histograms and frequency histograms.
直方图的信息存储在DBA_TAB_COL_STATISTICS和USER_ TAB_COL_STATISTICS 视图中。
视图中的histogram列有如下三种值： HEIGHT BALANCED, FREQUENCY, NONE.
1.高度均衡直方图
2.频率直方图

高度均衡直方图：数据分布不均匀 ，由于列中数据很多，这时数据比较密集，不利于分析和评估，这时直方图需要均衡化。
在高度平衡的直方图中， 列的值被分入一些bands中，每个band 包含差不多的rows数。这个band 也称为bucket。 即在一个bucket内，记录基本上是一样的。
假如一个列ID的值是1到100，histogram 有10个buckets。
如果数据是均匀分布的，那么它的直方图就类似与：

每个bucket中的记录数都是表中总数的十分之一。

如果数据是非均匀分布的，那么它的直方图就类似与：

在这个实例中，绝大多数的记录值都是5. 只有1/10的值在60到100之间。

频率直方图： 在Frequency Histograms 里，每个bucket 只包含一个记录。 因此当表记录中distinct values 小于等于histograms buckets时，会创建Frequency histograms 会自动创建。
信息的准确性就由两个数值决定，一个是bucket的个数，一个NUM_DISTINCT的个数。一般来说，bucket的数据越多，关于列数据分布的信息就越正确，但统计直方图的花费的时间和资源就多，oracle中bucket的最大254个，默认是75个。而SQL Server默认是200个。

如果一个列上的数据有比较严重的倾斜，对这个列做直方图是必要的，但是，Oracle 对数据分析是需要消耗资源的，特别是对于一些很大的段对象，分析的时间尤其长。对于OLAP系统，可能需要几个小时才能完成。 所以做不做分析就需要DBA 权衡好了。 但有一点要注意， 不要在生产环境中随便修改分析方案，除非你有十足的把握。 否则可能导致非常严重的后果。
通常情况下当BUCTET < 表的NUM_DISTINCT值得到的是HEIGHT BALANCED（高度平衡）直方图，而当BUCTET = 表的NUM_DISTINCT值的时候得到的是FREQUENCY（频率）直方图。 由于满足BUCTET = 表的NUM_DISTINCT值概率较低，所以在Oracle中生成的直方图大部分是HEIGHT BALANCED（高度平衡）直方图.

在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');

---