达梦8统计信息收集

1、统计信息简介

     对象统计信息描述了对象数据的分布特征。统计信息是优化器的代价计算的依据，可以帮助优化器较精确地估算成本，对执行计划的选择起着至关重要的作用。统计信息的收集频率是一把双刃剑，频率太低导致统计信息滞后，频率太高又影响查询性能，因此，系统管理员需要根据实际情况，合理安排统计信息收集的频率。

2、统计信息对象

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

1. 确定采样的对象：根据数据对象，确定需要分析哪些数据。

1） 表：计算表的行数、所占的页数目、平均记录长度。

2） 列：统计列数据的分布特征。

3） 索引：统计索引列的数据分布特征。

2. 确定采样率。用户根据统计数据对象的大小，确定采样率。如缺省，则默认通过内部算法确定数据的采样率。

3. 生成统计信息。根据不同的数据对象生成不同的统计信息。

1）表：表的行数、所占的页数目、平均记录长度等汇总数据。

2）列和索引：将采样的数据按照不同的分布特征生成相应的直方图。有两种类型的直方图：频率直方图和等高直方图。根据算法分析表的数据分布特征（以不同值的数据量 1万个为分界线），确定直方图的类型。频率直方图的每个桶(保存统计信息的对象)的高度不同，等高直方图每个桶的高度相同。生成直方图时，如果不同值少于 1 万个则用频率直方图，否则用等高直方图。

3、收集统计信息时机

收集统计信息的时机有两种：一是在查询之前进行静态收集；二是在查询的同时进行动态收集。

静态收集是在查询之前完成。和查询操作互不干涉，因此不影响查询性能。

动态收集是在查询的过程中完成。具体为在构造查询计划阶段进行，统计信息收集完成之后再继续构造计划，因此会影响计划阶段性能，特别是在高并发场景中。

从性能角度考虑，推荐用户使用静态收集。

4、统计信息收集建议

4.1 全模式收集

    制定月度收集计划，进行全模式收集，语句如下：

DBMS_STATS.GATHER_SCHEMA_STATS('用户名',100,TRUE,'FOR ALL COLUMNS SIZE AUTO',1,'GLOBAL');

4.2 小表收集方式

对于数据量不大，收集统计信息耗时短的表，建议在更新统计信息时，由开发人员配合梳理数据量每天变化比较大的表，进行全表收集，按照表数据量排序，第一和最后一个、第二和倒数第二这样的规则来组合收集，可以减少一次性全部收集占用的时间，提高效率。

1. 查询条件都是按天进行筛选，这样的查询所涉及的表建议每天导入数据以后收集。

收集语句如下：

DBMS_STATS.GATHER_TABLE_STATS('模式名','表名',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO',1,'GLOBAL');

以如下两个表为例。

call SP_CREATE_JOB('统计信息收集',1,0,'',0,0,'',0,'');

call SP_JOB_CONFIG_START('统计信息收集');

call SP_ADD_JOB_STEP('统计信息收集', '统计信息收集语句', 0, 'DBMS_STATS.GATHER_TABLE_STATS(''模式名'',''表名'',null,100,TRUE,''FOR ALL COLUMNS SIZE AUTO'',1,''GLOBAL'');

DBMS_STATS.GATHER_TABLE_STATS(''模式名'',''表名'',null,100,TRUE,''FOR ALL COLUMNS SIZE AUTO'',1,''GLOBAL'');', 0, 0, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('统计信息收集', '统计信息收集时间', 1, 1, 1, 0, 0, '22:00:00', NULL, '2023-07-27 09:45:17', NULL, '');

call SP_JOB_CONFIG_COMMIT('统计信息收集');

4.3 大表收集方式

4.3.1 全列收集

初次迁移完成，对于允许统计信息收集时间短，并且需要收集统计信息的大表，选择跑存储过程，以列为单位进行收集，可以有效缩短统计信息收集时间。

收集脚本如下：

--更新指定表的所有列的统计信息可使用sysdba或者用户自身

--DROP TABLE stat_history;

--SELECT * FROM stat_history;

create table stat_history (beg_time TIMESTAMP,end_time timestamp,sql_str varchar2(500),table_name varchar2(200),col_name VARCHAR2(200));

declare --更新所有列的统计信息

v_sql varchar(4000);

v_begtime VARCHAR2(200);

v_endtime varchar2(200);

CURSOR c1 IS SELECT

  SCH.NAME AS SCHEMA_NAME,

        TAB.NAME AS TABLE_NAME ,

        SYSCOL.NAME AS COLUMN_NAME

FROM

        SYSOBJECTS AS TAB,

        SYSOBJECTS AS SCH,

        SYSCOLUMNS AS SYSCOL

WHERE

        SCH.ID =TAB.SCHID

    AND TAB.ID =SYSCOL.ID

    AND SCH.NAME in ('ABCD')   --填写实际用户名

    AND SYSCOL.TYPE$ NOT IN ('BLOB','CLOB','TEXT')

    and TAB.NAME  IN(select  TABLE_NAME  from ALL_TABLES WHERE OWNER in ('ABCD'));  --填写实际用户名

 begin

  for i in c1

  loop     v_sql:='DBMS_STATS.GATHER_TABLE_STATS('''||i.SCHEMA_NAME||''','''||i.TABLE_NAME||''',null,100,TRUE,''FOR COLUMNS “'||i.COLUMN_NAME||'” SIZE AUTO'',1,''GLOBAL'');';   

--v_sql:='stat 100 on '||i.SCHEMA_NAME||'.'||i.TABLE_NAME||'("'||i.COLUMN_NAME||'");';

  --print v_sql;

  v_begtime := sysdate();

  execute immediate v_sql;

  v_endtime :=sysdate();

  insert into stat_history VALUES (v_begtime,v_endtime,v_sql,i.TABLE_NAME,i.COLUMN_NAME);

  commit;

  end loop;

end;

如上面脚本收集统计信息时间较长，可根据表中数据量将数据量较大的表拆分出来，编写多个脚本并行执行，可有效提高收集效率。收集脚本如下：

--更新指定表的所有列的统计信息可使用sysdba或者用户自身

--DROP TABLE stat_history;

--SELECT * FROM stat_history;

create table stat_history (beg_time TIMESTAMP,end_time timestamp,sql_str varchar2(500),table_name varchar2(200),col_name VARCHAR2(200));

declare --更新所有列的统计信息

v_sql varchar(4000);

v_begtime VARCHAR2(200);

v_endtime varchar2(200);

CURSOR c1 IS SELECT

  SCH.NAME AS SCHEMA_NAME,

        TAB.NAME AS TABLE_NAME ,

        SYSCOL.NAME AS COLUMN_NAME

FROM

        SYSOBJECTS AS TAB,

        SYSOBJECTS AS SCH,

        SYSCOLUMNS AS SYSCOL

WHERE

        SCH.ID =TAB.SCHID

    AND TAB.ID =SYSCOL.ID

    AND SCH.NAME in ('ABCD')   --填写实际用户名

    AND SYSCOL.TYPE$ NOT IN ('BLOB','CLOB','TEXT')

    and TAB.NAME  IN(select  TABLE_NAME  from ALL_TABLES WHERE OWNER in ('ABCD') AND TABLE_NAME IN ('TABLE_NAME'));  --填写实际用户名和表名

 begin

  for i in c1

  loop     v_sql:='DBMS_STATS.GATHER_TABLE_STATS('''||i.SCHEMA_NAME||''','''||i.TABLE_NAME||''',null,100,TRUE,''FOR COLUMNS “'||i.COLUMN_NAME||'” SIZE AUTO'',1,''GLOBAL'');';   

--v_sql:='stat 100 on '||i.SCHEMA_NAME||'.'||i.TABLE_NAME||'("'||i.COLUMN_NAME||'");';

  --print v_sql;

  v_begtime := sysdate();

  execute immediate v_sql;

  v_endtime :=sysdate();

  insert into stat_history VALUES (v_begtime,v_endtime,v_sql,i.TABLE_NAME,i.COLUMN_NAME);

  commit;

  end loop;

end;

4.3.2 部分列统计信息收集

若不具备4.1和4.2统计信息收集的时间条件，可选择收集部分列的统计信息，包括表的索引列和条件列。

对于索引列和SQL语句所涉及的条件列，需要查出来以后，添加到统计信息收集语句中。

--条件列统计信息收集

普通表：

stat 100 on 用户名.表名(列名1,列名2);

分区表：

DBMS_STATS.GATHER_TABLE_STATS('用户名','表名',null,100,TRUE,'FOR COLUMNS 列名 SIZE AUTO',1,'GLOBAL');

5、查看统计信息

--用于经过 GATHER_TABLE_STATS、GATHER_INDEX_STATS 或 GATHER_SCHEMA_STATS 收集之后展示。

dbms_stats.table_stats_show('模式名','表名');

--用于经过 GATHER_TABLE_STATS、GATHER_INDEX_STATS 或 GATHER_SCHEMA_STATS 收集之后展示。 返回两个结果集：一个是索引的统计信息；另一个是直方图的统计信息。

dbms_stats.index_stats_show('模式名','索引名');

–用于经过 GATHER_TABLE_STATS、GATHER_INDEX_STATS 或 GATHER_SCHEMA_STATS 收集之后展示。

dbms_stats.COLUMN_STATS_SHOW('模式名','表名','列名');

更多统计信息收集方式参考达梦在线服务平台：

https://eco.dameng.com/document/dm/zh-cn/ops/performance-optimization#5.1%20%E7%BB%9F%E8%AE%A1%E4%BF%A1%E6%81%AF%E6%A6%82%E8%BF%B0

6、一般情况收集建议

量体裁衣，找到适合自己系统的统计信息收集策略，用尽量小的代价收集到能稳定跑出正确执行计划的统计信息即可。也就是说统计信息不一定要特别准，只要有代表性，足够跑出正确执行计划就可以了。

根据实际业务情况以及经验，对统计信息的收集建议从如下几个维度考虑：

1）对于数据量小的库，全库统计信息收集时间短，并且不会影响到跑批业务，可按月进行全库收集；

2）按照表的数据变化量进行收集，数据变化量超过30%进行收集；

3）收集变更频率高的表；

4）分批次按照数据量大小组合收集，数据量大的表和数据量小的表进行组合收集，尽量保持每组收集的时间差距不大；

5）从业务角度对使用感受影响明显的表；

6）可以设置多个作业并行进行，对多个用户，多个Table。

统计信息收集语句如下：

DBMS_STATS.GATHER_TABLE_STATS('USERNAME','RISK_WARNED_INSTITUTION',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO',1,'GLOBAL');