【干货分享】DM7统计信息
一、什么是统计信息统计信息主要是描述数据库中表、索引的大小、规模、数据分布状况等的一类信息。比如,表的行数、...
一、什么是统计信息
统计信息主要是描述数据库中表、索引的大小、规模、数据分布状况等的一类信息。比如,表的行数、块数、平均每行的大小、索引的leaf blocks、索引字段的行数、不同值的大小等,都属于统计信息。CBO正是根据这些统计信息数据,计算出不同访问路径、不同join 方式下,各种执行计划的成本,最后选择出成本最小的执行计划执行查询操作。
(CBO:Cost-BasedOptimization,基于代价的优化器;计算各种“可能”执行计划的“代价”,即COST,从中选用COST最低的执行方案,作为实际运行方案。它依赖数据库对象的统计信息,统计信息的准确与否会影响CBO做出最优的选择。)
二、统计信息的基本内容
统计信息包含几个重要的宏观数据:
- 表所占的数据页数目
- 实际使用数据页数,
- B树的高度 ,(对于聚集索引表)
另外,如果做了列级或者索引的收集,那么统计信息还包括下列信息:
- 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 聚集因子
三、统计信息的收集
DM7收集统计信息有两种方法:一是通过一些存储过程来收集,二是用DBMS_STATS包来收集。
存储过程,如:
- 对表上所有的索引生成统计信息:SP_TAB_INDEX_STAT_INIT
- 对库上所有模式下的所有用户表上的所有索引生成统计信息:SP_DB_STAT_INIT
- 对指定的索引生成统计信息:SP_INDEX_STAT_INIT
- 对指定的列生成统计信息,不支持大字段列:SP_COL_STAT_INIT
使用DBMS_STATS首先得调用一个系统存储过程来创建这个包:
SP_CREATE_SYSTEM_PACKAGES(1);
DBMS_STATS包里面常用的存储过程有:
1. COLUMN_STATS_SHOW
根据模式名,表名和列名获得该列的统计信息。
2. INDEX_STATS_SHOW
根据模式名,索引名获得该索引的统计信息
3. GATHER_TABLE_S TAT S
根据设定的参数,收集表的统计信息
4. GATHER_INDEX_STATS
根据设定的参数,收集索引的统计信息
5. GATHER_SCHEMA_STATS
收集模式下对象的统计信息
举例说明:
收集SYSDBA模式下对象的统计,采样率为50%,对该模式下所有的列做统计信息,大字段的列除外:四、统计信息的查看
五、统计信息对查询的影响
删除表上的统计信息在没有统计信息的情况下,查看一下下列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是怎么来的呢?
如果没有统计信息可用,则对于列名= 的谓词,选择率固定为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很有可能选择错误的执行计划,我们收集一下这个表的统计信息,再来对比一下执行计划:
统计信息对CBO选择正确的执行计划非常重要,我们需要掌握收集统计信息的各种方法灵活运用。
关注 达梦大数据
微信扫一扫关注公众号