一、统计信息
对象统计信息描述了对象数据的分布特征。统计信息是优化器的代价计算的依据,可以帮助优化器较精确地估算成本,对执行计划的选择起着至关重要的作用。统计信息的收集频率是一把双刃剑,频率太低导致统计信息滞后,频率太高又影响查询性能,因此,系统管理员需要根据实际情况,合理安排统计信息收集的频率。
达梦数据库的统计数据对象分三种:表统计信息、列统计信息和索引统计信息。统计信息生成过程分三个步骤:
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');---多表DECLAREBEGIN 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')LOOPDBMS_STATS.EXPORT_TABLE_STATS(RS.OWNER,RS.TABLE_NAME,STATTAB=>'TB_STATS',STATID => 'T1_08161020');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_0301.DMP LOG=DEXP_ALL_STATS_0301.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_0301.DMP LOG=DIMP_ALL_STATS_0301.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');---多表DECLAREBEGINFOR 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;