expdp compression圧縮パラメータのテスト

7403 ワード

EXPDPIMPDP

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.

--lobフィールドを含むテーブルbasicfileのデータポンプエクスポートをテストします.
圧縮を有効にしないで、エクスポートします.エクスポートされたdmpファイルサイズは1.4 Gで、1分46秒かかります.

[oracle@redhat762100 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

[oracle@redhat762100 dump]$

compressionパラメータは有効ですが、compressionパラメータはmeatadata_を設定します.only.導出したdmpサイズは1.4 G(変化なし).1分44秒かかり、2秒少なくなりました.(ほとんど効果がない)

[oracle@redhat762100 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ファイルのサイズは217 Mです.1分33秒かかります.13秒もかからなかった.明らかな効果がある.

[oracle@redhat762100 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

[oracle@redhat762100 dump]$

再度lobフィールドを含まない小さなテーブルについてエクスポートテストを行います.圧縮を有効にする前後にエクスポートされるdmpファイルのサイズは、それぞれ1.4 Mと164 Kです.時間はそれぞれ20秒と18秒です.

[oracle@redhat762100 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

[oracle@redhat762100 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

[oracle@redhat762100 dump]$

END

OpenSSLコマンド---s_time

Compare and Swap