expdp+compression效能測試

weixin_33766168發表於2018-03-09
以下在11.2.0.3上，2 cores+ext3檔案系統， 匯出schema下10G資料，使用compression+ parallel=2 ，耗時大越10分鐘， 生成的dump檔案總共4.7GB大小    
[oracle@mlab1 ~]$ expdp  dumpfile=HDUMP:bcm%U.dmp filesize=2048M compression=all schemas=bcm parallel=2 logfile=HDUMP:bcm.log

Export: Release 11.2.0.3.0 - Production on Wed Sep 26 11:56:19 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA dumpfile=HDUMP:bcm%U.dmp filesize=2048M compression=all schemas=bcm parallel=2 logfile=HDUMP:bcm.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 10.82 GB
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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
. . exported "BCM"."C_CUSTOMER"                          1.194 GB 3000000 rows
. . exported "BCM"."C_STOCK"                             2.311 GB 10000000 rows
. . exported "BCM"."C_ORDER_LINE"                        809.8 MB 30004095 rows
. . exported "BCM"."E_TRADE"                             81.05 MB 4419000 rows
. . exported "BCM"."E_SETTLEMENT"                        19.90 MB 4590000 rows
. . exported "BCM"."E_CASH_TRANSACTION"                  32.58 MB 4383000 rows
. . exported "BCM"."E_DAILY_MARKET"                      33.43 MB 3861000 rows
. . exported "BCM"."C_HISTORY"                           56.62 MB 3000115 rows
. . exported "BCM"."E_TRADE_HISTORY"                     6.042 MB 4481000 rows
. . exported "BCM"."C_ORDER"                             15.22 MB 3000128 rows
. . exported "BCM"."E_WATCH_ITEM"                        17.15 MB 4414000 rows
. . exported "BCM"."E_FINANCIAL"                         40.82 MB 1000000 rows
. . exported "BCM"."E_ACCOUNT_PERMISSION"                9.659 MB  710000 rows
. . exported "BCM"."E_NEWS_ITEM"                         1.133 MB  100000 rows
. . exported "BCM"."E_CUSTOMER"                          3.526 MB  100000 rows
. . exported "BCM"."E_ADDRESS"                           1.731 MB  150000 rows
. . exported "BCM"."E_COMPANY"                           826.3 KB   50000 rows
. . exported "BCM"."C_DISTRICT"                          67.51 KB    1000 rows
. . exported "BCM"."C_ITEM"                              5.609 MB  100000 rows
. . exported "BCM"."E_CUSTOMER_ACCOUNT"                  6.697 MB  500000 rows
. . exported "BCM"."C_WAREHOUSE"                         12.47 KB     100 rows
. . exported "BCM"."E_BROKER"                            9.898 KB    1000 rows
. . exported "BCM"."E_CHARGE"                            4.968 KB      15 rows
. . exported "BCM"."E_COMMISSION_RATE"                   5.937 KB     240 rows
. . exported "BCM"."E_COMPANY_COMPETITOR"                703.2 KB  150000 rows
. . exported "BCM"."C_NEW_ORDER"                         744.1 KB  900088 rows
. . exported "BCM"."E_EXCHANGE"                          5.468 KB       4 rows
. . exported "BCM"."E_INDUSTRY"                          6.460 KB     102 rows
. . exported "BCM"."E_CUSTOMER_TAXRATE"                  413.0 KB  200000 rows
. . exported "BCM"."E_NEWS_XREF"                         471.7 KB  100000 rows
. . exported "BCM"."E_LAST_TRADE"                        485.3 KB   68500 rows
. . exported "BCM"."E_SECTOR"                            5.039 KB      12 rows
. . exported "BCM"."E_STATUS_TYPE"                       4.867 KB       5 rows
. . exported "BCM"."E_TAXRATE"                           8.703 KB     320 rows
. . exported "BCM"."E_TRADE_TYPE"                        5.062 KB       5 rows
. . exported "BCM"."E_SECURITY"                          2.177 MB   68500 rows
. . exported "BCM"."E_WATCH_LIST"                        252.6 KB  100000 rows
. . exported "BCM"."E_HOLDING"                               0 KB       0 rows
. . exported "BCM"."E_HOLDING_HISTORY"                       0 KB       0 rows
. . exported "BCM"."E_HOLDING_SUMMARY"                       0 KB       0 rows
. . exported "BCM"."E_TRADE_REQUEST"                         0 KB       0 rows
. . exported "BCM"."E_ZIP_CODE"                          103.3 KB   14741 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/dump/bcm01.dmp
  /home/oracle/dump/bcm02.dmp
  /home/oracle/dump/bcm03.dmp
  /home/oracle/dump/bcm04.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:05:03

[oracle@mlab1 ~]$ cd /home/oracle/dump/
[oracle@mlab1 dump]$ ls -lh
total 4.7G
-rw-r----- 1 oracle asmadmin 2.0G Sep 26 12:03 bcm01.dmp
-rw-r----- 1 oracle asmadmin 2.0G Sep 26 12:04 bcm02.dmp
-rw-r----- 1 oracle asmadmin 521M Sep 26 12:05 bcm03.dmp
-rw-r----- 1 oracle asmadmin 121M Sep 26 12:05 bcm04.dmp
-rw-r--r-- 1 oracle asmadmin 4.9K Sep 26 12:05 bcm.log

由於以上dump檔案已被壓縮過，所以後續的bzip2或者gz壓縮幾乎沒有意義，而且耗時超長且壓縮比極低：



本文轉自maclean_007 51CTO部落格，原文連結:http://blog.51cto.com/maclean/1278102

相關文章