查看: 14571|回复: 21

[精华] DM7统计信息初探

[复制链接]
认证徽章
论坛徽章:
43
现任管理团队成员
日期:2011-05-07 01:45:08ITPUB元老
日期:2012-09-12 14:50:28版主5段
日期:2014-06-11 02:21:31阿斯顿马丁
日期:2013-11-19 10:38:16祖母绿
日期:2012-11-06 12:43:12路虎
日期:2013-11-20 11:37:53雪佛兰
日期:2013-09-05 13:28:25ITPUB9周年纪念徽章
日期:2010-10-08 09:28:512015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24秀才
日期:2015-05-26 13:32:07
发表于 2013-9-5 20:58 | 显示全部楼层 |阅读模式
本帖最后由 myth8860 于 2013-9-5 21:58 编辑

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

2.统计信息的基本内容
   统计信息包含几个重要的宏观数据:

  • 表所占的数据页数目
  • 实际使用数据页数,
  • 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依据这些信息对选择率和基数进行估算。

3.直方图
      基本的统计信息并不能完全反应数据的分布情况,考虑下面这个例子,假定字段AGE, 表示年龄, 给出最小年龄10, 最大年龄80, 总的人数为10万,优化器还是不知道这些人的年龄分布情况。一个合理的假定是所有的记录都是平均分布的,但是现实世界并不完全这样,因此需要更加详细的信息,因此引入了直方图这个概念。直方图又分为频率直方图和等高直方图。
  频率直方图
    有些字段的取值范围非常有限,比如人类的年龄,一般不可能超过120, 因此无论表中有多少记录,年龄字段的唯一值个数都不会超过120, 我们可以采样部分记录,统计出每个年龄(0-120)的记录数,可以使用120个(V, count)二元组作为元素的数组,来表示这个频率直方图。比如这个表有1亿条记录,随机采用5%的记录,记录每个年龄出现的次数,然后再乘以20,即可获得这个年龄字段每一个取值的记录数目。
  等高直方图
    频率直方图虽然精确,但是它只能处理取值范围较小的情况,如果字段的取值范围很大,那么就不可能为每一个值统计出它的出现次数,这个时候我们需要等高直方图。等高直方图是针对一个数据集合不同值个数很多的情况,把数据集合划分为若干个记录数相同或相近的不同区间,并记录区间的不同值个数。每个区间的记录数比较接近,这就是所谓等高的含义。
系统也适用一个数组来表示等高直方图,数组的每一项包含下列信息:
  •   左边界值
  •   除边界值以外值的个数
  •   唯一值个数

     目前等高直方图数组的项(或者称为桶)的个数为131个,而频率直方图的个数为2000。等高直方图的目的,是尽可能精确地描述不同值数据的分布情况。在数据取值密集的地方那个,用来描述的桶就多,反之则少。利用它,可以比较精确地估算一个特定范围内记录的数量。


4.统计信息的收集
DM7收集统计信息有两种方法,一是通过一些存储过程来收集,二是用DBMS_STATS包来收集。
--对表上所有的索引生成统计信息
SP_TAB_INDEX_STAT_INIT
--对库上所有模式下的所有用户表上的所有索引生成统计信息
SP_DB_STAT_INIT
--对指定的索引生成统计信息
SP_INDEX_STAT_INIT
--对指定的列生成统计信息,不支持大字段列
SP_COL_STAT_INIT
----------详见《DM SQL语言手册附录3》
这里推荐使用DBMS_STATS包来收集,有别于存储过程,通过这个工具包收集可以指定采用率,
在数据分布极不均匀的情况下,提高统计信息的采用率,有助于提供更精确的统计信息。
使用DBMS_STATS首先得调用一个系统存储过程来创建这个包:
SP_CREATE_SYSTEM_PACKAGES(1);
包里面常用的存储过程有:

  • COLUMN_STATS_SHOW

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

     根据模式名,索引名获得该索引的统计信息。
  •    GATHER_TABLE_ S TATS

     根据设定的参数,收集表的统计信息。
  •    GATHER_INDEX_STATS

      根据设定的参数,收集索引的统计信息
  •    GATHER_SCHEMA_STATS

      收集模式下对象的统计信息
       --具体用法见《DM SQL语言手册》第二十章
       举例说明:
            --收集SYSDBA模式下对象的统计,采样率为50%,
            对该模式下所有的列做统计信息,大字段的列除外
             DBMS_STATS.GATHER_SCHEMA_STATS(' SYSDBA',50,TRUE,
'FOR ALL COLUMNS SIZE AUTO');  
5.统计信息的查看
create table TEST_TJ(id int,age int);
begin
      for i in 1..100000 loop
          insert into test_tj values(mod(i,9700),trunc(rand * 120));
      end loop;
      commit;
   end;
--更新统计信息
SP_COL_STAT_INIT('SYSDBA', 'TEST_TJ', 'ID'); --方法1
DBMS_STATS.GATHER_TABLE_STATS('SYSDBA', 'TEST_TJ',null,100,false,'FOR ALL COLUMNS SIZE AUTO'); --100%采样,方法2
--查看ID列的统计信息
DBMS_STATS.column_stats_show('SYSDBA', 'TEST_TJ','ID');
11.png
--解读统计信息
--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');

12.png

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


6.统计信息对查询的影响
--删除表上的统计信息
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%';
4.png

如果没有统计信息可用,则对于 列名= <常量>的谓词,选择率固定为SEL_RATE_EQU, 缺省为2.5%,其他谓词一律为SEL_RATE_SINGLE, 缺省为5%。
那么在这个例子中这个2500=100000*0.025;
我们再来看下面这个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, 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');
1.png


3.png

2.png



论坛徽章:
1
ITPUB社区OCM联盟徽章
日期:2014-11-17 13:33:27
发表于 2013-9-6 11:08 | 显示全部楼层
dm做的不错,确实厉害。
等高直方图直指12c啊...
频率直方图使rows的估算也是非常精确...
佩服...

使用道具 举报

回复
论坛徽章:
401
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2013-9-6 12:48 | 显示全部楼层
DBMS_STATS包好

使用道具 举报

回复
论坛徽章:
6
2012新春纪念徽章
日期:2012-01-04 11:53:292013年新春福章
日期:2013-02-25 14:51:24林肯
日期:2013-09-12 15:57:33优秀写手
日期:2013-12-18 09:29:09慢羊羊
日期:2015-03-04 14:51:352015年新春福章
日期:2015-03-06 11:57:31
发表于 2013-9-6 12:49 | 显示全部楼层
CBO 比较复杂, 统计信息只是其中的一小部分,需要继续努力。

使用道具 举报

回复
论坛徽章:
0
发表于 2013-9-6 13:40 | 显示全部楼层
不错、。。。

使用道具 举报

回复
论坛徽章:
11
SQL极客
日期:2013-12-09 14:13:35SQL数据库编程大师
日期:2013-12-06 13:59:43SQL大赛参与纪念
日期:2013-12-06 14:03:45红孩儿
日期:2012-12-19 11:08:17优秀写手
日期:2013-12-18 09:29:09暖羊羊
日期:2015-04-22 14:41:41
发表于 2013-9-6 20:42 | 显示全部楼层
顶一个,统计信息越准确,cbo就越给力,期待统计信息自动搜集方面有更多进步。

使用道具 举报

回复
认证徽章
论坛徽章:
43
现任管理团队成员
日期:2011-05-07 01:45:08ITPUB元老
日期:2012-09-12 14:50:28版主5段
日期:2014-06-11 02:21:31阿斯顿马丁
日期:2013-11-19 10:38:16祖母绿
日期:2012-11-06 12:43:12路虎
日期:2013-11-20 11:37:53雪佛兰
日期:2013-09-05 13:28:25ITPUB9周年纪念徽章
日期:2010-10-08 09:28:512015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24秀才
日期:2015-05-26 13:32:07
发表于 2013-9-6 22:37 来自手机 | 显示全部楼层
申请加精,哈哈

使用道具 举报

回复
论坛徽章:
401
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2013-9-7 20:55 | 显示全部楼层
加了。

使用道具 举报

回复
认证徽章
论坛徽章:
43
现任管理团队成员
日期:2011-05-07 01:45:08ITPUB元老
日期:2012-09-12 14:50:28版主5段
日期:2014-06-11 02:21:31阿斯顿马丁
日期:2013-11-19 10:38:16祖母绿
日期:2012-11-06 12:43:12路虎
日期:2013-11-20 11:37:53雪佛兰
日期:2013-09-05 13:28:25ITPUB9周年纪念徽章
日期:2010-10-08 09:28:512015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24秀才
日期:2015-05-26 13:32:07
发表于 2013-9-7 21:14 | 显示全部楼层
〇〇 发表于 2013-9-7 20:55
加了。

哈哈,谢谢,我会分享更多的好文章的

使用道具 举报

回复
论坛徽章:
11
SQL极客
日期:2013-12-09 14:13:35SQL数据库编程大师
日期:2013-12-06 13:59:43SQL大赛参与纪念
日期:2013-12-06 14:03:45红孩儿
日期:2012-12-19 11:08:17优秀写手
日期:2013-12-18 09:29:09暖羊羊
日期:2015-04-22 14:41:41
发表于 2013-9-9 11:33 | 显示全部楼层
这个直方图信息真心不错,能解决数据分布极度不均匀的问题。
测试sql如下:
--------------------------------------测试用例--------------------------------------------------------------
--测试在表数据分布极度不均匀的情况下,有无统计信息对CBO执行计划的影响
--
--1 构建一个数据分布极度不均匀的表,X列含数据1到10000,其中1有5001条,其他各1条
select user;
drop table t1;
create table t1(x int,y varchar(100));
create index ix_t1 on t1(x);

begin
   for i in 1..10000 loop
       insert into t1 values(i,'x');
   end loop;
   
   for i in 1..5 loop
     insert into t1 select 1,'y' connect by level<=1000;
   end loop;
end;   

--2 此时,未搜集统计信息
--查看统计信息默认参数
select * from "V$DM_INI" where para_name like 'SEL%';
--查看x列统计信息,无
DBMS_STATS.column_stats_show('ZTJ17', 'T1','X');
--查看无统计信息时的执行计划
select * from t1 where x=1; --此时应该全表扫描,由于无统计信息,CBO估算错误选择了走索引
1   #NSET2: [0, 1, 112]
2     #PRJT2: [0, 1, 112]; exp_num(3), is_atom(FALSE)
3       #BLKUP2: [0, 1, 112]; IX_T1(T1)  【这里CBO错误的估计为1条记录,实际为5001】
4         #SSEK2: [0, 1, 112]; scan_type(ASC), IX_T1(T1), scan_range[1,1]
--3 搜集列统计信息
--100%搜集所有列信息,含等高直方图
DBMS_STATS.GATHER_TABLE_STATS('ZTJ17', 'T1',null,100,false,'FOR ALL COLUMNS SIZE AUTO');
--查看X列统计信息,发现有直方图信息
DBMS_STATS.column_stats_show('ZTJ17', 'T1','X');
--再次查看执行计划
select * from t1 where x=1;--此时,CBO估算正确选择了全表扫描
1   #NSET2: [0, 5001, 112]
2     #PRJT2: [0, 5001, 112]; exp_num(3), is_atom(FALSE)
3       #SLCT2: [0, 5001, 112]; T1.X = 1
4         #CSCN2: [0, 1, 112]; INDEX33559000(T1)

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表