达梦数据库教程（14）统计信息与执行计划

一、达梦数据库执行计划

所有DBMS的执行计划作用基本都是相同的，用于显示一条 SQL 语句在数据库中的执行过程，通过执行计划可以看出SQL影响数据量、是否有使用合理的索引等，有助于SQL优化

1、达梦数据库执行计划解析

SQL> EXPLAIN SELECT * FROM SYSOBJECTS;
1   #NSET2: [1, 3225, 397]
    2     #PRJT2: [1, 3225, 397]; exp_num(17), is_atom(FALSE)
        3       #CSCN2: [1, 3225, 397]; SYSINDEXSYSOBJECTS(SYSOBJECTS as SYSOBJECTS)
        已用时间: 0.639(毫秒). 执行号:0.

执行计划的每行即为一个计划节点，当执行计划存在多行时，存在缩进的会优先执行，如果同样缩进则是按照先上后下执行，上下的优先级高于内外。

主要包含三部分信息：

· 第一列：NEST2、PRJT2、CSCN2 代表操作符及数据库具体执行的操作

· 第二列：通过一个列表展示执行代价，三组数字分别代表 [代价，记录行数，字节数]

· 第三列：额外补充信息

在上面的执行计划中，第三个计划节点表示操作符是 CSCN2（即全表扫描），代价估算是 1 ms，扫描数据行数3225行，查询397字节。

2、达梦执行计划常见操作符

· NSET：结果集收集操作符，一般是执行计划的顶层节点，无需过多关注，一般没有优化空间。

· PRJT：一般用于查询、排序、函数索引创建等。无需过多关注，一般没有优化空间。

· SLCT：根据查询条件进行过滤。可比较返回结果集与代价估算是否接近，如相差较大可考虑收集统计信息。若过滤条件过滤性较好，可考虑在条件列增加索引。

SQL> EXPLAIN SELECT * FROM YWPIE.T1 WHERE NAME='TANGLU';
1   #NSET2: [1, 250, 160]
2     #PRJT2: [1, 250, 160]; exp_num(5), is_atom(FALSE)
3       #SLCT2: [1, 250, 160]; T1A.C2 = 'TEST' SLCT_PUSHDOWN(TRUE)
4         #CSCN2: [1, 10000, 160]; INDEX33573393(T1A) NEED_SLCT(TRUE)

· AAGR：简单聚集，用于没有 GROUP BY 的 COUNT、SUM、AGE、MAX、MIN 等聚集函数的计算。

SQL> EXPLAIN SELECT COUNT(*) FROM YWPIE.T1 WHERE C2='TEST';
1   #NSET2: [1, 1, 48]
2     #PRJT2: [1, 1, 48]; exp_num(1), is_atom(FALSE)
3       #AAGR2: [1, 1, 48]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
4         #SLCT2: [1, 250, 48]; T1A.C2 = 'TEST'
5           #CSCN2: [1, 10000, 48]; INDEX33573393(T1A)
已用时间: 0.832(毫秒). 执行号:0.

· FAGR：快速聚集，用于没有过滤条件时，从表或索引快速获取 MAX、MIN、COUNT 值。

· HAGR：用于分组列没有索引只能走全表扫描的分组聚集。

· SAGR：流分组聚集，用于分组列是有序的情况下，可以使用流分组聚集，C1 列上已经创建了索引，SAGR2 性能优于 HAGR2。

· BLKUP：二次扫描 (回表)，先使用二级索引索引定位 rowid，再根据表的主键、聚集索引、rowid 等信息获取数据行中其它列。

· CSCN：全表扫描，I/O 开销较大，需要重点关注。

· SSEK：二级索引扫描，即先扫描索引，再通过主键、聚集索引、rowid 等信息去扫描表

SQL> EXPLAIN SELECT * FROM YWPIE.T1A WHERE C1=10;
1   #NSET2: [1, 250, 160]
2     #PRJT2: [1, 250, 160]; exp_num(5), is_atom(FALSE)
3       #BLKUP2: [1, 250, 160]; IDX_C1_T1A(T1A)
4         #SSEK2: [1, 250, 160]; scan_type(ASC), IDX_C1_T1A(T1A), scan_range[10,10]
已用时间: 1.039(毫秒). 执行号:0.

· CSEK：聚集索引扫描只需要扫描索引，不需要扫描表，即无需 BLKUP 操作，如果 BLKUP 开销较大时，可考虑创建聚集索引。

SQL> EXPLAIN SELECT * FROM YWPIE.T2A WHERE C1=10;
1   #NSET2: [1, 250, 160]
2     #PRJT2: [1, 250, 160]; exp_num(5), is_atom(FALSE)
3       #CSEK2: [1, 250, 160]; scan_type(ASC), IDX_C1_T2A(T2A), scan_range[10,10]
已用时间: 0.786(毫秒). 执行号:0.

· SSCN：SSCN 是索引全扫描，不需要扫描表。

SQL> EXPLAIN SELECT C1,C2 FROM YWPIE.T1A;
1   #NSET2: [1, 10000, 64]
2     #PRJT2: [1, 10000, 64]; exp_num(3), is_atom(FALSE)
3       #SSCN: [1, 10000, 64]; IDX_C1_C2_T1A(T1A)
已用时间: 0.595(毫秒). 执行号:0.

二、达梦数据库统计信息

数据库统计信息用于描述数据的分布特征。数据库执行计划是根据语句执行代价作为判断的标准，而执行代价的计算依据则是依赖统计信息。所以对于统计信息的日常维护也是十分重要的一环，如果统计信息收集频率太低会导致统计信息滞后，影响执行计划的准确性；而收集频率太高则会影响数据库性能。

达梦数据库统计信息分为表统计信息、列统计信息和索引统计信息三类。而以下对象不支持统计信息：

· 外部表、DBLINK 远程表、动态视图表、记录类型数组所用的临时表。

· 所在表空间为 OFFLINE 的对象。

· 位图索引，位图连接索引、虚索引、全文索引、空间索引、数组索引、无效的索引。

· BLOB、IMAGE、LONGVARBINARY、CLOB、TEXT、LONGVARCHAR、自定义类型列和空间类型列等列类型不支持统计信息，在使用 GATHER_TABLE_STATS()方法收集列统计信息时，这些类型的列会被忽略。

1、统计信息收集方法

统计信息支持自动收集和手动收集，不管哪种方式都会将结果均会保存到系统表中

自动收集

当配置文件 dm.ini中的参数 AUTO_STAT_OBJ 值为 1 或 2 时，可以开启统计信息的自动收集，该参数默认为值0。主要区别如下：

1：对所有表的统计信息进行监控

2：只对同义词 DBMS_STATS.SET_TABLE_PREFS 表设置过 STALE_PERCENT 属性的对象进行监控

CREATE TABLE T1(A INT);
#用AUTO_STAT_OBJ=1打开对T1表的监控
SP_SET_PARA_VALUE(1,'AUTO_STAT_OBJ',1);
#用AUTO_STAT_OBJ=2打开对T1表的监控
#对修改行数占总行数的比达到STALE_PERCENT要求的对象和总行数为0的对象收集统计信息
SP_SET_PARA_VALUE(1,'AUTO_STAT_OBJ',2);
DBMS_STATS.SET_TABLE_PREFS('SYSDBA','T1','STALE_PERCENT',15);

手动收集

通过同义词 DBMS_STATS 中的 GATHER_TABLE_STATS 方法手动收集统计信息。除了 GATHER_TABLE_STATS（表统计信息）以外还支持GATHER_INDEX_STATS（索引统计信息） 或 GATHER_SCHEMA_STATS（SCHEMA统计信息）

在使用包内的过程和函数之前，如果还未创建过系统包，需要先调用系统过程创建系统包。

SP_CREATE_SYSTEM_PACKAGES (1,'DBMS_STATS');
SET SERVEROUTPUT ON;

收集表统计信息

DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'YWPIE',TABNAME=>'T1',ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);
select TABLE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,LAST_ANALYZED from dba_tables where owner='YWPIE' and table_name='T1';

三、达梦优化器Hint特性

hint（提示）是一种嵌入在 SQL 查询中的指令，我们已经知道数据库是通过优化器来自动选择一个最优的执行计划，但是在部分场景下维护人员基于业务场景和数据特性可能知道更优的执行方式，这个时候就可以通过hint特性来对优化器发出指示或者建议，影响原本执行计划的生成方式。比如指定走某个索引或者不走索引、表的连接顺序、是否并行执行等。在使用hint时需要注意它只是对优化器发起建议，不代表一定会这样执行。不同的数据库对hint的支持也不一样，通常仅当优化器判断不准时才使用 Hint ，而且它可能会导致查询更慢，所以能不用就不用吧，仅作为了解即可。

hint语法格式

如果 HINT 语法错误或指定的值不正确，并不会导致语句报错，而是直接忽略 HINT 继续执行。

# SELECT /*+ HINT1 [HINT2]*/ 列名 FROM 表名 WHERE_CLAUSE ;
select /*+PLAN_NO_CACHE*/ * from t1;  #使用不生成缓存的hint，该查询部生成缓存
select * from t1;  #生成缓存
select * from t1;  #使用上一个查询的缓存

达梦国产数据库信创