玉面飞龙的BLOG

数据库升级后，难免要用到expdp impdp这些10g的new utilities.阅读一下相关的文档还是需要的。

Unload(load) Performance的提高，确实是选择expdp（impdp）的理由,而且是唯一理由。

简单的表模式unload测试，比较expdp VS exp的时间。

USER-TESTDB$> expdp SCOTT/TIGER DIRECTORY=DATADUMPDIR DUMPFILE=bigtable_t.dmp TABLES=bigtable_t

Export: Release 10.2.0.3.0 - 64bit Production on Sunday, 10 June, 2007 2:01:31
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting “SCOTT”.”SYS_EXPORT_TABLE_01″: SCOTT/******** DIRECTORY=DATADUMPDIR DUMPFILE=bigtable_t.dmp TABLES=bigtable_t
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.220 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported “SCOTT”.”bigtable_t”:”PART2″ 139.4 MB 1868290 rows
. . exported “SCOTT”.”bigtable_t”:”PART1″ 265.7 MB 3559742 rows
. . exported “SCOTT”.”bigtable_t”:”PART0″ 267.0 MB 3623889 rows
. . exported “SCOTT”.”bigtable_t”:”PARTMAX” 0 KB 0 rows
Master table “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
****************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/oracle/USER/data06/datadump/bigtable_t.dmp
Job “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully completed at 02:02:33

USER-TESTDB$> exp SCOTT/TIGER TABLES=bigtable_t file=/oracle/USER/data06/datadump/bigtable_t.exp.dmp direct=y ; date

Export: Release 10.2.0.3.0 - Production on Sun Jun 10 02:03:56 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Direct Path …
. . exporting table bigtable_t
. . exporting partition PART0 3623889 rows exported
. . exporting partition PART1 3559742 rows exported
. . exporting partition PART2 1868290 rows exported
. . exporting partition PARTMAX 0 rows exported
Export terminated successfully without warnings.
USER-TESTDB$> Sun Jun 10 02:07:02 GMT 2007

expdp:62 seconds. VS exp:186 seconds,三倍差距

expdp可以并行导出数据，设置3个并行度，生成3个导出数据文件。

USER-TESTDB$> expdp SCOTT/TIGER DIRECTORY=DATADUMPDIR DUMPFILE=test_U%u.dmp parallel=3 TABLES=bigtable_t
Export: Release 10.2.0.3.0 - 64bit Production on Sunday, 10 June, 2007 2:18:58
********************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/oracle/USER/data06/datadump/test_U01.dmp
/oracle/USER/data06/datadump/test_U02.dmp
/oracle/USER/data06/datadump/test_U03.dmp
Job “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully completed at 02:19:33

使用并行expdp的时候花费35秒。

expdp还可以估计导出文件所用的磁盘空间，而不需实际执行导出过程，不过就是估计的不是很准确。

USER-TESTDB$> expdp SCOTT/TIGER DIRECTORY=DATADUMPDIR DUMPFILE=bigtable_t2.dmp TABLES=bigtable_t ESTIMATE_ONLY=y

ORA-39002: invalid operation
ORA-39201: Dump files are not supported for estimate only jobs.

USER-TESTDB$> expdp SCOTT/TIGER DIRECTORY=DATADUMPDIR TABLES=bigtable_t ESTIMATE_ONLY=y

Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated “SCOTT”.”bigtable_t”:”PART2″ 520 MB
. estimated “SCOTT”.”bigtable_t”:”PART1″ 370 MB
. estimated “SCOTT”.”bigtable_t”:”PART0″ 360 MB
. estimated “SCOTT”.”bigtable_t”:”PARTMAX” 0 KB
Total estimation using BLOCKS method: 1.220 GB
Job “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully completed at 02:28:34

使用expdp的时候，日志文件输出一大堆log，似乎expdp和Oracle Stream有联系。

streams_pool_size defaulting to 429496729
kupprdp: master process DM00 started with pid=229, OS id=2270
to execute - SYS.KUPM$MCP.MAIN(’SYS_EXPORT_TABLE_01′, ‘SCOTT’, ‘KUPC$C_1_20070610020132′, ‘KUPC$S_1_20070610020132′, 0);
kupprdp: worker process DW01 started with worker id=1, pid=231, OS id=2272
to execute - SYS.KUPW$WORKER.MAIN(’SYS_EXPORT_TABLE_01′, ‘SCOTT’);
kupprdp: master process DM00 started with pid=229, OS id=3598
to execute - SYS.KUPM$MCP.MAIN(’SYS_EXPORT_TABLE_01′, ‘SCOTT’, ‘KUPC$C_1_20070610021859′, ‘KUPC$S_1_20070610021859′, 0);
kupprdp: worker process DW01 started with worker id=1, pid=231, OS id=3613
to execute - SYS.KUPW$WORKER.MAIN(’SYS_EXPORT_TABLE_01′, ‘SCOTT’);
kupprdp: worker process DW02 started with worker id=2, pid=233, OS id=3615
to execute - SYS.KUPW$WORKER.MAIN(’SYS_EXPORT_TABLE_01′, ‘SCOTT’);
kupprdp: worker process DW03 started with worker id=3, pid=234, OS id=3617
to execute - SYS.KUPW$WORKER.MAIN(’SYS_EXPORT_TABLE_01′, ‘SCOTT’);
kupprdp: master process DM00 started with pid=229, OS id=3929
to execute - SYS.KUPM$MCP.MAIN(’SYS_EXPORT_TABLE_01′, ‘SCOTT’, ‘KUPC$C_1_20070610022235′, ‘KUPC$S_1_20070610022235′, 0);
kupprdp: master process DM00 started with pid=229, OS id=3959
to execute - SYS.KUPM$MCP.MAIN(’SYS_EXPORT_TABLE_01′, ‘SCOTT’, ‘KUPC$C_1_20070610022305′, ‘KUPC$S_1_20070610022305′, 0);
kupprdp: worker process DW01 started with worker id=1, pid=231, OS id=3961
to execute - SYS.KUPW$WORKER.MAIN(’SYS_EXPORT_TABLE_01′, ‘SCOTT’);

记录到这里，看不进去文档了…….老了sigh