expdp compression压缩参数的测试

程序员文章站 2022-03-01 17:27:44

...

RDBMS 12.2.0.1

测试数据泵的compression参数。expdp帮助中该参数的说明如下：

compression默认压缩的是metadata_only,默认的压缩算法是basic。

COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.

COMPRESSION_ALGORITHM
Specify the compression algorithm that should be used.
Valid keyword values are: [BASIC], LOW, MEDIUM and HIGH.

--测试，对表basicfile进行数据泵导出，该表8W行数据，其中包括lob字段。

不启用压缩，进行导出。导出的dmp文件大小1.4G，耗时1分46秒。

[[email protected] dump]$ expdp bb/oracle dumpfile=basicfile_nocomp.dmp logfile=basicfile_nocomp0723.log directory=DUMP tables=basicfile

Export: Release 12.2.0.1.0 - Production on Tue Jul 23 08:40:59 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "BB"."SYS_EXPORT_TABLE_01":  bb/******** dumpfile=basicfile_nocomp.dmp logfile=basicfile_nocomp0723.log directory=DUMP tables=basicfile
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "BB"."BASICFILE"                            1.408 GB   80000 rows
Master table "BB"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for BB.SYS_EXPORT_TABLE_01 is:
  /u01/dump/basicfile_nocomp.dmp
Job "BB"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jul 23 08:42:47 2019 elapsed 0 00:01:46

[[email protected] dump]$

启用compression参数，但是compression参数设置的是meatadata_only.导出的dmp大小是1.4G(没用变化)。耗时1分44秒，少了2秒。（几乎没用什么效果）

[[email protected] dump]$ expdp bb/oracle dumpfile=basicfile_comp.dmp logfile=basicfile_comp0723.log directory=DUMP COMPRESSION=METADATA_ONLY tables=basicfile

Export: Release 12.2.0.1.0 - Production on Tue Jul 23 08:45:21 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "BB"."SYS_EXPORT_TABLE_01":  bb/******** dumpfile=basicfile_comp.dmp logfile=basicfile_comp0723.log directory=DUMP COMPRESSION=METADATA_ONLY tables=basicfile
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "BB"."BASICFILE"                            1.408 GB   80000 rows
Master table "BB"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for BB.SYS_EXPORT_TABLE_01 is:
  /u01/dump/basicfile_comp.dmp
Job "BB"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jul 23 08:47:06 2019 elapsed 0 00:01:44

启用compression参数，设置其值为all。导出的dmp文件大小为217M。耗时1分33秒。耗时少了13秒。有明显的效果。

[[email protected] dump]$ expdp bb/oracle dumpfile=basicfile_comp.dmp logfile=basicfile_comp0723.log directory=DUMP COMPRESSION=ALL tables=basicfile

Export: Release 12.2.0.1.0 - Production on Tue Jul 23 09:13:32 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "BB"."SYS_EXPORT_TABLE_01":  bb/******** dumpfile=basicfile_comp.dmp logfile=basicfile_comp0723.log directory=DUMP COMPRESSION=ALL tables=basicfile
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "BB"."BASICFILE"                            217.5 MB   80000 rows
Master table "BB"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for BB.SYS_EXPORT_TABLE_01 is:
  /u01/dump/basicfile_comp.dmp
Job "BB"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jul 23 09:15:06 2019 elapsed 0 00:01:33

[[email protected] dump]$

再次针对一个不包含lob字段的小表进行导出测试。启用压缩前后导出的dmp文件大小分别为1.4M和164K。耗时分别为20秒和18秒。

[[email protected] dump]$ expdp bb/oracle dumpfile=t1_nocomp.dmp logfile=t1_nocomp0723.log directory=DUMP tables=t1

Export: Release 12.2.0.1.0 - Production on Tue Jul 23 08:49:23 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "BB"."SYS_EXPORT_TABLE_01":  bb/******** dumpfile=t1_nocomp.dmp logfile=t1_nocomp0723.log directory=DUMP tables=t1
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "BB"."T1"                                   1.406 MB   70001 rows
Master table "BB"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for BB.SYS_EXPORT_TABLE_01 is:
  /u01/dump/t1_nocomp.dmp
Job "BB"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jul 23 08:49:45 2019 elapsed 0 00:00:20

[[email protected] dump]$ expdp bb/oracle dumpfile=t1_comp.dmp logfile=t1_comp0723.log directory=DUMP COMPRESSION=ALL tables=t1

Export: Release 12.2.0.1.0 - Production on Tue Jul 23 09:19:10 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "BB"."SYS_EXPORT_TABLE_01":  bb/******** dumpfile=t1_comp.dmp logfile=t1_comp0723.log directory=DUMP COMPRESSION=ALL tables=t1
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "BB"."T1"                                   163.9 KB   70001 rows
Master table "BB"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for BB.SYS_EXPORT_TABLE_01 is:
  /u01/dump/t1_comp.dmp
Job "BB"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jul 23 09:19:29 2019 elapsed 0 00:00:18

[[email protected] dump]$

END

上一篇：在云服务器上安装docker

下一篇：一个挺有意思的code

expdp compression压缩参数的测试

ASP.NET页面进行GZIP压缩优化的几款压缩模块的使用简介及应用测试!(附源码)第1/2页

详解linux下tar压缩解压缩文件夹的命令与参数

python的默认参数问题测试代码解析

ASP.NET页面进行GZIP压缩优化的几款压缩模块的使用简介及应用测试!(附源码)第1/2页

MySQL增强半同步几个重要参数搭配的测试

[原创] dd 命令进行磁盘测试及 conv 和 oflag 参数的说明

postman做接口测试，body穿json格式的参数，json中的参数值是list类型，且列表项是图片时如何传参

带Boolean类型的参数的接口用postman测试时传参问题

带Boolean类型的参数的接口用postman测试时传参问题

pytest接口测试之fixture传参数request的使用