DM 统计信息

什么是统计信息

统计信息主要是描述数据库中表、索引的大小、规模、数据分布状况等的一类信息。比如，表的行数、块数、平均每行的大小、索引的 leaf blocks、索引字段的行数、不同值的大小等，都属于统计信息。CBO 正是根据这些统计信息数据，计算出不同访问路径、不同 join 方式下，各种执行计划的成本，最后选择出成本最小的执行计划执行查询操作。

（CBO：Cost-BasedOptimization，基于代价的优化器；计算各种“可能”执行计划的“代价”，即 COST，从中选用 COST 最低的执行方案，作为实际运行方案。它依赖数据库对象的统计信息，统计信息的准确与否会影响 CBO 做出最优的选择。）

统计信息的基本内容

统计信息包含几个重要的宏观数据：

表所占的数据页数目
实际使用数据页数，
B 树的高度，（对于聚集索引表）

无论做不做统计信息收集，表的当前记录数永远是有效的，因为系统自动维护了表的记录总数，这一点与大部分其他 DBMS 系统有所差别。

另外，如果做了列级或者索引的收集，那么统计信息还包括下列信息：

COLID 列的ID
SQL_PL_TYPE 数据类型
N_TOTAL 总记录数
N_SAMPLE 采样记录数
N_DISTINCT 不同值数
N_NULL 空值数
V_MIN 最小值
V_MAX 最大值
BLEVEL B树的高度
N_LEAF_PAGES 叶子段的页数目
N_LEAF_USED_PAGES 叶子段实际使用的页数目
CLUSTER_FACTOR 聚集因子

CBO依据这些信息对选择率和基数进行估算。

统计信息的收集

DM 收集统计信息有两种方法：一是通过一些存储过程来收集，二是用 DBMS_STATS 包来收集。

存储过程，如：

对表上所有的索引生成统计信息：SP_TAB_INDEX_STAT_INIT
对库上所有模式下的所有用户表上的所有索引生成统计信息：SP_DB_STAT_INIT
对指定的索引生成统计信息：SP_INDEX_STAT_INIT
对指定的列生成统计信息，不支持大字段列：SP_COL_STAT_INIT

这里推荐使用 DBMS_STATS 包来收集，有别于存储过程，通过这个工具包收集可以指定采用率，在数据分布极不均匀的情况下，提高统计信息的采用率，有助于提供更精确的统计信息。

使用 DBMS_STATS 首先得调用一个系统存储过程来创建这个包：

SP_CREATE_SYSTEM_PACKAGES(1);

DBMS_STATS 包里面常用的存储过程有：

COLUMN_STATS_SHOW

根据模式名，表名和列名获得该列的统计信息。

INDEX_STATS_SHOW

根据模式名，索引名获得该索引的统计信息。

GATHER_TABLE_S TAT S

根据设定的参数，收集表的统计信息。

GATHER_INDEX_STATS

根据设定的参数，收集索引的统计信息。

GATHER_SCHEMA_STATS

收集模式下对象的统计信息。

举例说明：

收集 SYSDBA 模式下对象的统计，采样率为 50%，对该模式下所有的列做统计信息，大字段的列除外：

DBMS_STATS.GATHER_SCHEMA_STATS('SYSDBA',50,TRUE,'FOR ALL COLUMNS SIZE AUTO');

统计信息的查看

begin
   for i in 1..100000 loop
      insert into test_tj values(mod(I,9700),trunc(rand * 120));
   end loop;

   commit;
end;

DBMS_STATS.GATHER_TABLE_STATS('SYSDBA','TEST_TJ',null,100,flase,'FOR ALL COLUMNS SIZE AUTO');

--查看 ID 列的统计信息
DBMS_STATS.COLUMN_STATS_SHOW('SYSDBA','TEST_TJ','ID');

--解读统计信息
--1.类型：等高直方图
--2.ENDPOINT_VALUE 样本值：30
--3.ENDPOINT_HEIGHT 小于样本值大于前一个样本值的个数：329
  select COUNT(*) from TEST_TJ where id<30
--4.ENDPOINT_KEYGHT 样本值的个数：11
  select COUNT(*) from TEST_TJ where id=30
--5.ENDPOINT_DISTINCT 小于样本值大于前一个样本值之间不同样本的个数：30
  select COUNT(distinct id) from TEST_TJ where id<30

--查看 AGE 列的统计信息
DBMS_STATS.column_stats_show('SYSDBA','TEST_TJ','AGE');

--解读统计信息
--1.类型：频率直方图
--2.ENDPOINT_VALUE 样本值：1
--3.ENDPOINT_HEIGHT 样本值的个数：773
select COUNT(*) from TEST_TJ where age=1

统计信息对查询的影响

删除表上的统计信息

DBMS_STATS.DELETE_TABLE_STATS('SYSDBA','TEST_TJ');

在没有统计信息的情况下，查看一下下列 SQL 的执行计划:

explain select count(*) from TEST_TJ where age = 20;
1   #NSET2: [11, 1, 4]
2     #PRJT2: [11, 1, 4]; exp_num(1),is_atom(FALSE)
3       #AAGR2: [11, 1, 4]; grp_num(0),sfun_num(1)
4         #SLCT2: [11, 2500, 4]; TEST_TJ.AGE = 20
5          #CSCN2: [11, 100000, 4];INDEX33559695(TEST_TJ)
--以 age 为条件字段，进行等值扫描，计划评估出来的行数是 2500

这个 2500 是怎么来的呢？

select * from "V$DM_INI" where "PARA_NAME" like 'sel&';

如果没有统计信息可用，则对于列名= <常量>的谓词，选择率固定为SEL_RATE_EQU, 缺省为2.5%，其他谓词一律为SEL_RATE_SINGLE, 缺省为5%。

那么在这个例子中这个2500=100000*0.025;

我们再来看下面这个 SQL 的计划:

explainselect count(*) from TEST_TJ where age > 20;
1   #NSET2: [11, 1, 4]
2     #PRJT2: [11, 1, 4]; exp_num(1),is_atom(FALSE)
3       #AAGR2: [11, 1, 4]; grp_num(0),sfun_num(1)
4         #SLCT2: [11, 5000, 4]; TEST_TJ.AGE = 20
5          #CSCN2: [11, 100000, 4];INDEX33559695(TEST_TJ)

这里的 5000=100000*0.05；

很显然这个默认值代价的估算是非常粗糙的，特别是对于复杂的查询，如果没有统计信息 CBO 很有可能选择错误的执行计划，我们收集一下这个表的统计信息，再来对比一下执行计划：

DBMS_STATS.GATHER_TABLE_STATS('SYSDBA','TEST_TJ',null,100,false,'FOR ALL COLUMNS SIZE AUTO');

explain select count(*) from TEST_TJ where age = 20;

统计信息对 CBO 选择正确的执行计划非常重要，我们需要掌握收集统计信息的各种方法灵活运用。

什么是统计信息

统计信息的基本内容

统计信息的收集

统计信息的查看

统计信息对查询的影响

作者