DM8优化之收集统计信息

DM8优化之收集统计信息

一、统计信息介绍
达梦数据库的统计数据对象分三种：表统计信息、列统计信息和索引统计信息。统计信
息生成过程分三个步骤：

1. 确定采样的对象：根据数据对象，确定需要分析哪些数据。
   1） 表：计算表的行数、所占的页数目、平均记录长度。
   2） 列：统计列数据的分布特征。
   3） 索引：统计索引列的数据分布特征。
2. 确定采样率。用户根据统计数据对象的大小，确定采样率。如缺省，则默认通过内
   部算法确定数据的采样率。
3. 生成统计信息。根据不同的数据对象生成不同的统计信息。
   1）表：表的行数、所占的页数目、平均记录长度等汇总数据。
   2）列和索引：将采样的数据按照不同的分布特征生成相应的直方图。有两种类型的直
   方图：频率直方图和等高直方图。根据算法分析表的数据分布特征（以不同值的数据量 1 万
   个为分界线），确定直方图的类型。频率直方图的每个桶(保存统计信息的对象)的高度不
   同，等高直方图每个桶的高度相同。生成直方图时，如果不同值少于 1 万个则用频率直方图，否则用等高直方图。

二、统计信息作用
统计信息是优化器的代价计算的依据，可以帮助优化器较精确地估算成本，对执行计划的选择起着至关重要的作用
收集统计信息时机
收集统计信息的时机有两种：一是在查询之前进行静态收集；二是在查询的同时进行动态收集。
静态收集是在查询之前完成。和查询操作互不干涉，因此不影响查询性能。
动态收集是在查询的过程中完成。具体为在构造查询计划阶段进行，统计信息收集完成之后再继续构造计划，因此会影响计划阶段性能，特别是在高并发场景中。从性能角度考虑，推荐用户使用静态收集。
三、手动收集统计信息
3.1收集shema统计信息
DBMS_STATS.GATHER_SCHEMA_STATS(‘DMLOG’, 100,FALSE,‘FOR ALL COLUMNS SIZE AUTO’);-DMLOG是模式名

这种收集方式，并不是100%收集，所以有一定的弊端。
3.2收集表的统计信息
单表收集：
使用DBMS_STATS包中GATHER_TABLE_STATS方法手动收集用户表的统计信息
的完整过程。使用包内的过程和函数之前，如果还未创建过系统包。请先调用系统过程创建系统包。
SP_CREATE_SYSTEM_PACKAGES (1,‘DBMS_STATS’);
SET SERVEROUTPUT ON; //PRINT 需要设置这条语句，才能打印出消息
收集模式 DMLOG下表 EMPLOYEE的统计信息，并打印收集的表信息。
方式一：
BEGIN
DECLARE
OBJTAB DBMS_STATS.OBJECTTAB;
OBJ_FILTER_LIST DBMS_STATS.OBJECTTAB;
BEGIN
OBJ_FILTER_LIST(0).OWNNAME = ‘DMLOG’;
OBJ_FILTER_LIST(0).OBJTYPE = ‘TABLE’;
OBJ_FILTER_LIST(0).OBJNAME = ‘EMPLOYEE’;
DBMS_STATS.GATHER_SCHEMA_STATS(
‘DMLOG’,
1.0,
FALSE,
‘FOR ALL COLUMNS SIZE AUTO’,
1,
‘AUTO’,
TRUE,
NULL,
NULL,
‘GATHER’,
OBJTAB,
NULL,
TRUE,
TRUE,
OBJ_FILTER_LIST
);
PRINT OBJTAB.COUNT;
FOR I IN 0…OBJTAB.COUNT-1 LOOP
PRINT OBJTAB(I).OWNNAME;
PRINT OBJTAB(I).OBJTYPE;
PRINT OBJTAB(I).OBJNAME;
PRINT OBJTAB(I).PARTNAME;
PRINT OBJTAB(I).SUBPARTNAME;
PRINT '-------- ';
END LOOP;
END;
END;
/

查看表统计信息：
DBMS_STATS.TABLE_STATS_SHOW(‘DMLOG’,‘EMPLOYEE’);

删除表统计信息：
DBMS_STATS.DELETE_TABLE_STATS(‘DMLOG’,‘EMPLOYEE’);

方式二：
使用STAT方式生成统计信息：
语法为：
STAT ON [<模式名>.]<表名> [GLOBAL];
参数

1. <模式名> 指定生成统计信息的表的模式。缺省为当前会话的模式名；
2. <表名> 指定生成统计信息的表；
3. GLOBAL 用于MPP环境下各节点数据收集后统一生成统计信息。
   STAT ON DMLOG.EMPLOYEE;
   查看统计信息：
   DBMS_STATS.TABLE_STATS_SHOW(‘DMLOG’,‘EMPLOYEE’);注意不能使用和列、索引等方式用STAT使用采样率百分比的方式收集。
   STAT 30 ON DMLOG.EMPLOYEE;
   

分区表收集：
创建表并插入数据：
CREATE TABLE DMLOG.PARTITIONED_TABLE (
COLUMN1 VARCHAR2(50),
COLUMN2 NUMBER,
PARTITION_KEY DATE
)
PARTITION BY RANGE (PARTITION_KEY) (
PARTITION part1 VALUES LESS THAN (TO_DATE(‘2022-01-01’, ‘YYYY-MM-DD’)),
PARTITION part2 VALUES LESS THAN (TO_DATE(‘2023-01-01’, ‘YYYY-MM-DD’)),
PARTITION part3 VALUES LESS THAN (MAXVALUE)
);

INSERT INTO DMLOG.PARTITIONED_TABLE (COLUMN1, COLUMN2, PARTITION_KEY)
VALUES (‘Data1’, 100, TO_DATE(‘2021-05-15’, ‘YYYY-MM-DD’));

INSERT INTO DMLOG.PARTITIONED_TABLE (COLUMN1, COLUMN2, PARTITION_KEY)
VALUES (‘Data2’, 200, TO_DATE(‘2022-06-20’, ‘YYYY-MM-DD’));

收集分区表DMLOG.PARTITIONED_TABLE统计信息：
DBMS_STATS.G