需求:在数据迁移项目中,经常需要计算大批量导出、导入的速度,以估算迁移项目的时间窗口是否满足要求。
例如有如下impdp log文件,需要估算本次impdp过程的速度:
1. $more impdp.log
2. Import: Release 11.2.0.3.0 - Production on Tue Mar 26 20:16:27 2017
3.
4. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
5.
6. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
7. With the Partitioning, OLAP, Data Mining and Real Application Testing options
8. Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=dump logfile=scott_impdp.log remap_schema=scott:LJG network_link=power1
9. Estimate in progress using BLOCKS method...
10. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
11. Total estimation using BLOCKS method: 10.129 GB
12. Processing object type SCHEMA_EXPORT/USER
13. Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
14. Processing object type SCHEMA_EXPORT/ROLE_GRANT
15. Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
16. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
17. Processing object type SCHEMA_EXPORT/TABLE/TABLE
18. . . imported "LJG"."TEST" 3.91 GB 19096576 rows
19. . . imported "LJG"."DEPT" 1.91 GB 2200004 rows
20. . . imported "LJG"."EMP" 1.01 GB 1000014 rows
21. . . imported "LJG"."SALGRADE" 2.03 GB 230005 rows
22. . . imported "LJG"."BONUS" 1.11 GB 3340090 rows
23. . . imported "LJG"."BONUS1" 1.11 MB 3340090 rows
24. . . imported "LJG"."BONUS2" 1.11 MB 3340090 rows
25.
26.
27. Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
28. Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
29. ORA-39082: Object type ALTER_PROCEDURE:"LJG"."BBW_INSERT" created with compilation warnings
30. Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
31. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
32. Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
33. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
34. Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
35. Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 20:19:27
简单一点的方案就是直接复制3.91,1.91......这些数据到Excel中,利用Excel计算总数据量。
但是,如果导入的表有几千张,用Excel就太不方便了!!
这时我们可以利用AWK SHELL脚本,对GB所在的列进行加总求和,计算出总的数据量。
$cat import.log |grep GB|awk ‘{sum=sum+$5} END {print sum}’
10.97
得到本次导入GB级表数据10.97GB
$cat import.log |grep MB|awk ‘{sum=sum+$5} END {print sum}’
2.22
得到本次导入MB级表数据2.22MB
将上面两个数据单们统一为1099MB,再除以总时间(20:19:27 -20:16:27=3分钟),得出导入速度为每分钟366MB。
注意:由于SHELL脚本不支持小数点的浮点数运算,需要引入awk进行计算。