Oracle 12c 新特性 --- 根据COMPRESSION_ALGORITHM参数值来确定导出dumpfile的压缩级别

概念

 

There is a new expdp command-line option for Oracle Data Pump Export to control the degree of compression used for a Oracle Data Pump dump file. It also adds the same options to the PL/SQL DBMS_DATAPUMP package. This allows the DBA to trade off time spent compressing data against the size of the Oracle Data Pump dump file.
 
This feature allows the DBA to control the resources used during an export operation.
有一个新的expdp命令行选项，用于Oracle数据泵导出，以控制用于Oracle数据泵转储文件的压缩程度。它还为PL / SQL DBMS_DATAPUMP包添加了相同的选项。这使得DBA可以利用时间来压缩与Oracle数据泵转储文件大小的压缩数据。

这个特性允许DBA控制在导出操作过程中使用的资源。

目的：

Specifies the compression algorithm to be used when compressing dump file data.

指定压缩转储文件数据时使用的压缩算法。

语法及描述：

 

COMPRESSION_ALGORITHM = {BASIC | LOW | MEDIUM | HIGH}

The parameter options are defined as follows:

    BASIC--Offers a good combination of compression ratios and speed; the algorithm used is the same as in previous versions of Oracle Data Pump.   

    LOW---Least impact on export throughput and suited for environments where CPU resources are the limiting factor.  

    MEDIUM---Recommended for most environments. This option, like the BASIC option, provides a good combination of compression ratios and speed, but it uses a different algorithm than BASIC.  

    HIGH--Best suited for situations in which dump files will be copied over slower networks where the limiting factor is network speed.

The performance of a compression algorithm is characterized by its CPU usage and by the compression ratio (the size of the compressed output as a percentage of the uncompressed input). These measures vary on the size and type of inputs as well as the speed of the compression algorithms used. The compression ratio generally increases from low to high, with a trade-off of potentially consuming more CPU resources.

It is recommended that you run tests with the different compression levels on the data in your environment. Choosing a compression level based on your environment, workload characteristics, and size and type of data is the only way to ensure that the exported dump file set compression level meets your performance and storage requirements.

COMPRESSION_ALGORITHM = {BASIC | LOW | MEDIUM | HIGH}

参数选项的定义如下:

    BASIC--提供了压缩比和速度的良好组合;使用的算法与以前版本的Oracle数据泵相同。

    LOW---对出口吞吐量的影响最小，适合CPU资源是限制因素的环境。  

    MEDIUM---建议对于大多数环境。这个选项，像基本选项一样，提供了压缩比和速度的很好的组合，但是它使用的算法不同于BASIC。  

    HIGH--最适合于将转储文件复制到较慢网络的情况，其中限制因素是网络速度。

压缩算法的性能特点是其CPU使用率和压缩比(压缩输出的大小作为未压缩输入的百分比)。这些措施根据输入的大小和类型以及所使用的压缩算法的速度而变化。压缩比通常由低到高，并有可能消耗更多的CPU资源。

建议您在环境中对数据的不同压缩级别进行测试。根据环境、工作负载特性和数据类型选择压缩级别是确保导出的转储文件集压缩级别满足性能和存储需求的惟一方法。

Restrictions

 To use this feature, database compatibility must be set to 12.0.0 or later.

    This feature requires that the Oracle Advanced Compression option be enabled.

实验

1) 参数COMPRESSION_ALGORITHM=LOW 采用这种压缩算法，使用较少的CPU,代价却是小于最佳压缩比，压缩文件会很大。

[[email protected] backup]$ expdp test/[email protected] DIRECTORY=dpump_dir1 DUMPFILE=test.dmp COMPRESSION=DATA_ONLY COMPRESSION_ALGORITHM=LOW

Export: Release 12.1.0.2.0 - Production on Sun Aug 6 16:59:41 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/********@pdbcndba DIRECTORY=dpump_dir1 DUMPFILE=test.dmp COMPRESSION=DATA_ONLY COMPRESSION_ALGORITHM=LOW 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 77 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "TEST"."LEO2"                               108.5 KB    6886 rows
. . exported "TEST"."VW_TEST"                            1.049 MB   90936 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /backup/test.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Aug 6 17:00:31 2017 elapsed 0 00:00:42


-rw-r-----. 1 oracle oinstall 1306624 Aug  6 17:00 test.dmp
-rw-r-----. 1 oracle oinstall  942080 Aug  6 17:04 test3.dmp

2) COMPRESSION_ALGORITHM=basic，这种压缩算法在以前的版本中使用。提供良好的压缩，不会严重影响性能。

[[email protected] backup]$ expdp test/[email protected] DIRECTORY=dpump_dir1 DUMPFILE=test3.dmp COMPRESSION=DATA_ONLY COMPRESSION_ALGORITHM=basic

Export: Release 12.1.0.2.0 - Production on Sun Aug 6 17:04:03 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/********@pdbcndba DIRECTORY=dpump_dir1 DUMPFILE=test3.dmp COMPRESSION=DATA_ONLY COMPRESSION_ALGORITHM=basic 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 77 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "TEST"."LEO2"                               102.5 KB    6886 rows
. . exported "TEST"."VW_TEST"                            735.3 KB   90936 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /backup/test3.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Aug 6 17:04:25 2017 elapsed 0 00:00:21

-rw-r-----. 1 oracle oinstall 1306624 Aug  6 17:00 test.dmp
-rw-r-----. 1 oracle oinstall  942080 Aug  6 17:04 test3.dmp

参考链接： 

http://docs.oracle.com/database/121/NEWFT/chapter12101.htm#NEWFT253

http://docs.oracle.com/database/121/SUTIL/GUID-F81B5F5F-9F40-4EB0-99B8-47C45179DE5E.htm#SUTIL4051