达梦数据库统计信息的导出导入

一、统计信息

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

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

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

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

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

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

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

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

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

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

在执行查询时，如果数据对象存在统计信息，代价算法可以根据统计信息中的数据，比较精确地计算出操作所需花费的成本，以此来确定连接方式、对象访问路径、连接顺序，选择最优的执行计划。

二、应用场景

随着数据库国产化进程的加快，越来越多的行业、越来越多的系统将选择我们的国产数据库，那么这个过程中必然会遇到大数据量系统迁移的情况，异构数据库迁移完成后，一定会在新替换的国产数据库中收集统计信息。在达梦数据库中，统计信息的导出导入，便适合应用于这种数据库数据量比较大，迁移异构数据库数据到DM数据库后统计信息收集耗时较长的场景。举例：假如客户原来使用A数据库，迁移到新数据库后，统计信息首次完全收集耗时会很长，那么为了缩短这一时间，可以在A数据库系统切换到国产数据库的上线演练过程中，将统计信息完全收集并导出，在上线当天将前期的统计信息导入上线的生产环境，以此来缩短收集统计信息的耗时，那么整体的时间窗口也将会大大减少。

三、具体操作

前提说明：假设存在两套达梦数据库，一套是进行过上线演练的DMA库（IP为192.168.10.100），一套是即将上线使用的DMB库（IP为192.168.10.200），其中DMA库已完成数据库统计信息的收集。

DMA库：

1、创建系统包

默认情况下，DM 在新建库第一次启动数据库服务时会自动创建除了 DMGEO、DBMS_JOB、DBMS_WORKLOAD_REPOSITORY 和 DBMS_SCHEDULER 之外的所有系统包。如果数据库没有做过换bin升级，此步可跳过，如不跳过，则执行创建系统包的系统存储过程时会重建指定的系统包。

--检查系统包是否创建，返回1表示已创建，返回0为表示未创建
SELECT SP_CREATE_SYSTEM_PACKAGES(1);
--创建（或重建）指定的系统包
SP_CREATE_SYSTEM_PACKAGES(1,'DBMS_STATS');

2、测试系统包的使用

--测试创建一个增加了前缀的临时统计信息表
DBMS_STATS.CREATE_STAT_TABLE('SYSDBA', 'OADB_ALLTB');
#其中SYSDBA为统计信息表的模式名，区分大小写；OADB_ALLTB为统计信息表名，区分大小写，系统实际创建的表会增加前缀。
--删除统计信息表
DBMS_STATS.DROP_STAT_TABLE ('SYSDBA', 'OADB_ALLTB');
#其中SYSDBA为统计信息表的模式名，区分大小写；OADB_ALLTB为创建时指定的统计信息表名，区分大小写。

3、把目标表的统计信息导出到指定的统计信息表中

--查看用户下的统计信息数量，第二步中指定了统计信息表创建到SYSDBA模式下，所以此处用SYSDBA查看
SELECT COUNT (*) FROM STAT$_OADB_ALLTB;
--备份统计信息，使用SYSDBA用户登陆数据库，执行下面备份语句
语法：DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME, TABNAME, PARTNAME, STATTAB, STATID, CASCADE, STATOWN, STAT_CATEGORY);
参数详解OWNNAME 目标表的模式名，区分大小写。TABNAME 目标表名，区分大小写。PARTNAME 目标表分区名，如果不指定分区，则一起导出所有子表的统计信息，缺省为NULL。STATTAB 统计信息表名，区分大小写。STATID 由用户指定的统计信息标识名，缺省为空字符串。CASCADE 是否连列和索引的统计信息一起导出，缺省为 TRUESTATOWN 统计信息表的模式名，区分大小写，缺省为 NULL。STAT_CATEGORY 仅保留参数以兼容 ORACLE，功能暂未实现。
---单表
DBMS_STATS.EXPORT_TABLE_STATS('OA', 'OA_TABLE1', NULL, 'OADB_ALLTB', 'export stats test', TRUE, 'SYSDBA');
---多表
DECLARE
BEGIN FOR RS IN (SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER IN ('USER1','USER2'))LOOPDBMS_STATS.EXPORT_TABLE_STATS(RS.OWNER,RS.TABLE_NAME,NULL,'OADB_ALLTB','table stats',true);END LOOP;
END;
--查看SYSDBA用户下的统计信息数量
SELECT COUNT (*) FROM STAT$_OADB_ALLTB;
备份统计信息使用ALLSTATS用户登陆188数据库，执行下面备份语句
BEGIN FOR RS IN (SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER='ALLSTATS')
LOOP
DBMS_STATS.EXPORT_TABLE_STATS(RS.OWNER,RS.TABLE_NAME,STATTAB=>'TB_STATS',STATID => 'T1_202108161020');
END LOOP;
END;
－－查看用户下的统计信息数量
SELECT COUNT (*) FROM STAT$_TB_STATS;

4、dexp导出统计信息表

./dexp  SYSDBA/SYSDBA@192.168.10.100:5236 DIRECTORY=/dm/dumpfile/ FILE=ALL_STATS_20230301.DMP LOG=DEXP_ALL_STATS_20230301.LOG TABLES=SYSDBA.'STAT$_OADB_ALLTB'

DMB库：

1、创建系统包

默认情况下，DM 在新建库第一次启动数据库服务时会自动创建除了 DMGEO、DBMS_JOB、DBMS_WORKLOAD_REPOSITORY 和 DBMS_SCHEDULER 之外的所有系统包。如果数据库没有做过换bin升级，此步可跳过，如不跳过，则执行创建系统包的系统存储过程时会重建指定的系统包。

--检查系统包是否创建，返回1表示已创建，返回0为表示未创建
SELECT SP_CREATE_SYSTEM_PACKAGES(1);
--创建（或重建）指定的系统包
SP_CREATE_SYSTEM_PACKAGES(1,'DBMS_STATS');

2、测试系统包的使用

--测试创建一个增加了前缀的临时统计信息表
DBMS_STATS.CREATE_STAT_TABLE('SYSDBA', 'OADB_ALLTB');
#其中SYSDBA为统计信息表的模式名，区分大小写；OADB_ALLTB为统计信息表名，区分大小写，系统实际创建的表会增加前缀。
--删除统计信息表
DBMS_STATS.DROP_STAT_TABLE ('SYSDBA', 'OADB_ALLTB');
#其中SYSDBA为统计信息表的模式名，区分大小写；OADB_ALLTB为创建时指定的统计信息表名，区分大小写。

3、dimp导入统计信息表

./dimp SYSDBA/SYSDBA@192.168.10.200:5236 DIRECTORY=/dm/dumpfile/ FILE=ALL_STATS_20230301.DMP LOG=DIMP_ALL_STATS_20230301.LOG TABLES=SYSDBA.'STAT$_OADB_ALLTB' TABLE_EXISTS_ACTION=REPLACE

4、把统计信息表中的统计信息导入到目标表中

--还原统计信息，使用SYSDBA用户登陆数据库，执行下面备份语句
语法：DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME, TABNAME, PARTNAME, STATTAB, STATID, CASCADE, STATOWN, NO_INVALIDATE, FORCE, STAT_CATEGORY);
参数详解OWNNAME 目标表的模式名，区分大小写。TABNAME 目标表名，区分大小写。PARTNAME 目标表分区名，如果不指定分区，则一起导出所有子表的统计信息，缺省为NULL。STATTAB 统计信息表名，区分大小写。STATID 由用户指定的统计信息标识名，缺省为空字符串。CASCADE 是否连列和索引的统计信息一起导出，缺省为 TRUESTATOWN 统计信息表的模式名，区分大小写，缺省为 NULL。NO_INVALIDATE 是否让依赖游标失效，缺省为 TRUE。FORCE 仅保留参数以兼容 ORACLE，功能暂未实现。STAT_CATEGORY 仅保留参数以兼容 ORACLE，功能暂未实现。
---单表
DBMS_STATS.IMPORT_TABLE_STATS('OA', 'OA_TABLE1', NULL, 'OADB_ALLTB', 'import stats test', TRUE, 'SYSDBA');
---多表
DECLARE
BEGINFOR RS IN (SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER IN ('SYSDBA','USER2'))LOOPBEGINDBMS_STATS.IMPORT_TABLE_STATS(RS.OWNER,RS.TABLE_NAME,NULL,'OADB_ALLTB','TABLE STATS',TRUE);EXCEPTIONWHEN OTHERS THENPRINT ('OWNER:' || RS.OWNER);PRINT ('TABLE_NAME:' || RS.TABLE_NAME);PRINT ('CODE: ' || SQLCODE);PRINT ('ERRM: ' || SQLERRM);END;END LOOP;
END;