上海专业网站建设网,大气网站欣赏,网站备案申请,个人网站论文结束语一、统计信息对象统计信息描述了对象数据的分布特征。统计信息是优化器的代价计算的依据#xff0c;可以帮助优化器较精确地估算成本#xff0c;对执行计划的选择起着至关重要的作用。统计信息的收集频率是一把双刃剑#xff0c;频率太低导致统计信息滞后#xff0c;频率太…一、统计信息 对象统计信息描述了对象数据的分布特征。统计信息是优化器的代价计算的依据可以帮助优化器较精确地估算成本对执行计划的选择起着至关重要的作用。统计信息的收集频率是一把双刃剑频率太低导致统计信息滞后频率太高又影响查询性能因此系统管理员需要根据实际情况合理安排统计信息收集的频率。 达梦数据库的统计数据对象分三种表统计信息、列统计信息和索引统计信息。统计信息生成过程分三个步骤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 OWNERALLSTATS)
LOOP
DBMS_STATS.EXPORT_TABLE_STATS(RS.OWNER,RS.TABLE_NAME,STATTABTB_STATS,STATID T1_202108161020);
END LOOP;
END;
查看用户下的统计信息数量
SELECT COUNT (*) FROM STAT$_TB_STATS;4、dexp导出统计信息表./dexp SYSDBA/SYSDBA192.168.10.100:5236 DIRECTORY/dm/dumpfile/ FILEALL_STATS_20230301.DMP LOGDEXP_ALL_STATS_20230301.LOG TABLESSYSDBA.STAT$_OADB_ALLTBDMB库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/SYSDBA192.168.10.200:5236 DIRECTORY/dm/dumpfile/ FILEALL_STATS_20230301.DMP LOGDIMP_ALL_STATS_20230301.LOG TABLESSYSDBA.STAT$_OADB_ALLTB TABLE_EXISTS_ACTIONREPLACE4、把统计信息表中的统计信息导入到目标表中--还原统计信息使用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;
