DB2执行LOAD操作的同时进行统计信息的收集
来源:互联网 发布:nginx 外网访问内网ip 编辑:程序博客网 时间:2024/06/06 20:54
DB2 执行LOAD命令之后,数据会有改变,这时候需要使用RUNSTATS收集统计信息,那么有没有办法在LOAD的同时收集统计信息呢?
答案是有的,需要使用到LOAD命令的下面这个选项:
收集表统计信息概要文件中指定的统计信息。即使装入操作本身的性能下降(特别是在指定 DETAILED INDEXES ALL 时),与在完成装入操作后调用 RUNSTATS 实用程序相比,使用此参数来收集数据分布和索引统计信息更有效。 为优化性能,应用程序需要尽可能最佳的数据分布和索引统计信息。一旦更新统计信息,应用程序就可以根据最新的统计信息使用新的表数据存取路径。可通过使用 BIND 命令重新绑定应用程序包来创建新的表访问路径。通过运行带有 SET PROFILE 选项的 RUNSTATS 命令来创建表统计信息概要文件。
将数据装入到大表中时,建议对 stat_heap_sz(统计信息堆大小)数据库配置参数指定较大的值。
按照上面的要求,需要先使用RUNSTATS命令创建统计信息概要,这个操作是一次性的,一旦完成,之后所有的LOAD操作都可以按照这个概要文件指定的选项来进行统计信息的收集工作。
此外,对于LOAD的类型有限制:
在装入过程中是否收集统计信息。仅当以 REPLACE 方式运行装入操作时,才支持此选项。根据为表定义的概要文件收集统计信息。必须在执行 LOAD 命令前使用 RUNSTATS 命令创建该概要文件。如果不存在该概要文件,但您指示装入操作根据该概要文件收集统计信息,装入操作就会失败并返回错误消息。
示例:
$ db2 "RUNSTATS ON TABLE T2 AND DETAILED INDEXES ALL SET PROFILE ONLY"DB20000I The RUNSTATS command completed successfully.$ db2 "load from c:\t2.del of del replace into T2 STATISTICS USE PROFILE"SQL3109N The utility is beginning to load data from file "c:\t2.del".SQL3500W The utility is beginning the "LOAD" phase at time "2016-09-0713:38:54.597265".SQL3519W Begin Load Consistency Point. Input record count = "0".SQL3520W Load Consistency Point was successful.SQL3110N The utility has completed processing. "2" rows were read from theinput file.SQL3519W Begin Load Consistency Point. Input record count = "2".SQL3520W Load Consistency Point was successful.SQL3515W The utility has finished the "LOAD" phase at time "2016-09-0713:38:54.769188".SQL3500W The utility is beginning the "BUILD" phase at time "2016-09-0713:38:54.773675".SQL3213I The indexing mode is "REBUILD".SQL3515W The utility has finished the "BUILD" phase at time "2016-09-0713:38:54.896751".Number of rows read = 2Number of rows skipped = 0Number of rows loaded = 2Number of rows rejected = 0Number of rows deleted = 0Number of rows committed = 2
$ db2 "RUNSTATS ON TABLE T2 UNSET PROFILE"DB20000I The RUNSTATS command completed successfully.$ db2 "load from c:\t2.del of del replace into T2 STATISTICS USE PROFILE"SQL3109N The utility is beginning to load data from file "c:\t2.del".SQL3500W The utility is beginning the "LOAD" phase at time "2016-09-0713:40:42.692386".SQL3519W Begin Load Consistency Point. Input record count = "0".SQL3520W Load Consistency Point was successful.SQL2315N The RUNSTATS utility was called with the " USE PROFILE" option.However, a statistics profile for this table does not exist.SQL3110N The utility has completed processing. "2" rows were read from theinput file.SQL3519W Begin Load Consistency Point. Input record count = "2".SQL3520W Load Consistency Point was successful.SQL3515W The utility has finished the "LOAD" phase at time "2016-09-0713:40:42.812534".SQL3500W The utility is beginning the "BUILD" phase at time "2016-09-0713:40:42.814839".SQL3213I The indexing mode is "REBUILD".SQL3515W The utility has finished the "BUILD" phase at time "2016-09-0713:40:42.928173".SQL3107W At least one warning message was encountered during LOAD processing.Number of rows read = 2Number of rows skipped = 0Number of rows loaded = 2Number of rows rejected = 0Number of rows deleted = 0Number of rows committed = 2
参考资料:
LOAD概述
用于提高装入性能的选项
RUNSTATS命令
LOAD命令
0 0
- DB2执行LOAD操作的同时进行统计信息的收集
- 统计信息的收集
- 统计信息的自动收集
- DB2 查询统计信息的命令
- DB2 查询统计信息的命令
- Oracle CBO 统计信息的收集与执行计划的选择
- 【Job】收集统计信息的job GATHER_STATS_JOB在上午十点执行
- Gprof -收集程序的统计信息
- 收集全库统计信息的解释
- oracle收集表的统计信息
- 关于11g+的统计信息收集
- db2 load的用法
- db2 load的用法
- DB2 Load的用法
- 6.2.3 收集执行计划统计信息
- oracle同时执行saveOrUpdate的操作
- sqlplus得到执行计划的统计信息
- oracle -执行计划的统计信息
- QT 5.5.1版本静态库编译
- struct和class
- sip协议学习
- hibernate中注解总结
- oci
- DB2执行LOAD操作的同时进行统计信息的收集
- 大型网站架构演化之路
- JSON相关操作函数
- Dialog的简单使用
- Codeforces Round #344 (Div. 2) B. Print Check (模拟)
- 编译安装R+Rstudio步骤
- [后端]MyBatis
- 记一次调bug的过程:windows下查找java应用程序CPU与内存过高
- 关于用java编写生成word文档,动态添加数据到word文档的一些心得