加速exp-direct with recordlength
Need to exp one table from TEST, table segment size 3G with 18M records
TEST-test$ ora seglike TEST
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_N size(M)
-------------------- ------------------------------ ------------------------------ --------------- ------------ ----------
TEST TEST TABLE MD_DATA 3000
TEST_USER@TEST:prod SQL> select count(*) from TEST.TEST;
COUNT(*)
----------
18187113
1) exp table via conventional Path remotely
exp TEST_USER/****@TEST FILE=TEST_1.dmp LOG=TEST_1.log tables=TEST.TEST
About to export specified tables via Conventional Path ...
Current user changed to TEST
. . exporting table TEST 18187113 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
start: Mon Apr 2 19:22:28 GMT 2012
end: Tue Apr 3 05:59:17 GMT 2012
need 10 hours 27 mins
2 ) exp table via direct path remotely
exp TEST_USER/****@TEST FILE=TEST_2.dmp LOG=TEST_2.log direct=y tables=TEST.TEST
About to export specified tables via Direct Path ...
Current user changed to TEST
. . exporting table TEST 18187113 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
start: Tue Apr 3 18:38:45 GMT 2012
end : Wed Apr 4 11:06:07 GMT 2012
need 16 hours 28 mins
3) add record length via direct path remotely
About to export specified tables via Direct Path ...
exp TEST_USER/****@TEST FILE=TEST_3.dmp LOG=TEST_3.log direct=y recordlength=65535 tables=TEST.TEST
Current user changed to TEST
. . exporting table TEST 18187113 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
start : Wed Apr 4 11:06:07 GMT 2012
end : Wed Apr 4 11:39:15 GMT 2012
need 32 mins
then try to use exp on local server
4) exp table via conventional Path locally
exp TEST_USER/**** FILE=TEST_1.dmp LOG=TEST_1.log tables=TEST.TEST
About to export specified tables via Conventional Path ...
Current user changed to TEST
. . exporting table TEST 18187113 rows exported
Export terminated successfully with warnings.
start: Sun Apr 8 01:41:20 GMT 2012
end : Sun Apr 8 01:47:12 GMT 2012
need 5 mins 52 seconds
5 ) exp table via direct path locally
exp TEST_USER/**** FILE=TEST_2.dmp LOG=TEST_2.log direct=y tables=TEST.TEST
About to export specified tables via Direct Path ...
Current user changed to TEST
. . exporting table TEST 18187113 rows exported
Export terminated successfully with warnings.
start: Sun Apr 8 01:47:12 GMT 2012
end : Wed Apr 4 01:50:42 GMT 2012
need 3 mins 30 seconds
5) add record length via direct path locally
About to export specified tables via Direct Path ...
exp TEST_USER/**** FILE=TEST_3.dmp LOG=TEST_3.log direct=y recordlength=65535 tables=TEST.TEST
Current user changed to TEST
. . exporting table TEST 18187113 rows exported
Export terminated successfully with warnings.
start : Sun Apr 8 01:50:42 GMT 2012
end : Sun Apr 8 01:51:48 GMT 2012
need 1 mins 6 seconds
ecord length:
Specifies the length, in bytes, of the file record. The RECORDLENGTH parameter is necessary when you must transfer the export file to another operating system that uses a different default value.
If you do not define this parameter, it defaults to your platform-dependent value for BUFSIZ. For more information about the BUFSIZ default value, see your operating system-specific documentation.
You can set RECORDLENGTH to any value equal to or greater than your system's BUFSIZ. (The highest value is 64KB.) Changing the RECORDLENGTH parameter affects only the size of data that accumulates before writing to the disk. It does not affect the operating system file block size.
Using truss to trace what happened when recordlength happened( linux上可以尝试strace）
Orignian exp with direct
truss -o exp2.trace exp TEST_USER/TEST_USER FILE=TEST_2.dmp LOG=TEST_2.log tables=TEST.TEST direct=y;
read(11, "018F\0\006\0\0\0\0\006\0".., 2064) = 399
write(10, "\015\0\006\0\0\0\0\00305".., 21) = 21
read(11, "018C\0\006\0\0\0\0\006\0".., 2064) = 396
write(10, "\015\0\006\0\0\0\0\00305".., 21) = 21
read(11, "018F\0\006\0\0\0\0\006\0".., 2064) = 399
write(10, "\015\0\006\0\0\0\0\00305".., 21) = 21
read(11, "019A\0\006\0\0\0\0\006\0".., 2064) = 410
write(10, "\015\0\006\0\0\0\0\00305".., 21) = 21
read(11, "0193\0\006\0\0\0\0\006\0".., 2064) = 403
write(10, "\015\0\006\0\0\0\0\00305".., 21) = 21
read(11, "0193\0\006\0\0\0\0\006\0".., 2064) = 403
write(12, " 0 - 107\0 x o0705161C $".., 8192) = 8192
write(10, "\015\0\006\0\0\0\0\00305".., 21) = 21
read(11, "0193\0\006\0\0\0\0\006\0".., 2064) = 403
write(10, "\015\0\006\0\0\0\0\00305".., 21) = 21
read(11, "0193\0\006\0\0\0\0\006\0".., 2064) = 403
write(10, "\015\0\006\0\0\0\0\00305".., 21) = 21
read(11, "0193\0\006\0\0\0\0\006\0".., 2064) = 403
write(10, "\015\0\006\0\0\0\0\00305".., 21) = 21
read(11, "01A2\0\006\0\0\0\0\006\0".., 2064) = 418
write(10, "\015\0\006\0\0\0\0\00305".., 21) = 21
read(11, "0194\0\006\0\0\0\0\006\0".., 2064) = 404
write(10, "\015\0\006\0\0\0\0\00305".., 21) = 21
read(11, "019C\0\006\0\0\0\0\006\0".., 2064) = 412
write(12, "03\0 0 - 101\08001\0 007".., 8192) = 8192
write(10, "\015\0\006\0\0\0\0\00305".., 21) = 21
and with recordlength
truss -o exp2.trace exp TEST_USER/TEST_USER FILE=TEST_2.dmp LOG=TEST_2.log tables=TEST.TEST direct=y recordlength=65535;
read(11, "07DB\0\006\0\0\0\0\0\b\0".., 2064) = 2064
read(11, "\0 9 8\b\0 1 - 4 M 8 6 7".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0 207".., 2064) = 2064
read(11, " 203\0 8 5 204\0 O O T P".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\00705".., 2064) = 2064
read(11, " 3 9 0 2 4 2 307\0 x o07".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0 101".., 2064) = 2064
read(11, " 007\0 x o0705161C &03\0".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0 007".., 2064) = 2064
read(11, "05161C &03\0 0 - 107\0 x".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0\0 O".., 2064) = 2064
read(11, " S Q L L O A D E R10\0".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0 E".., 2064) = 2064
read(11, "1C &01\0 N\n\0 S Q L L".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0 t b".., 2064) = 2064
read(11, "161C &03\0 0 - 101\08001".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\01C &".., 2064) = 2064
read(11, "03\0 0 - 107\0 x o070516".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0 L ".., 2064) = 2064
read(11, "01\08001\0 007\0 x o0705".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0 - ".., 2064) = 2064
read(11, " S Q L L O A D E R10\0".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0 m e".., 2064) = 2064
read(11, " D E R10\0 O u t b o u n".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0 p p".., 2064) = 2064
read(11, " u t b o u n d - E m".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\004\0".., 2064) = 2064
read(11, " r S u p p o r t04\0 N".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0 1 -".., 2064) = 2064
read(11, " u t b o u n d - E m".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0 s t".., 2064) = 2064
read(11, "\0 0 - 101\08001\0 007\0".., 1958) = 1958
read(11, "05FA\0\006\0\0\0\0\0\0 0".., 2064) = 1530
write(12, "05161C &03\0 0 - 107\0 x".., 8192) = 8192
write(12, " &03\0 0 - 107\0 x o0705".., 8192) = 8192
write(12, " u p p o r t04\0 N o n e".., 8192) = 8192
write(12, " 9 0 2 4 6 607\0 x o0705".., 8192) = 8192
write(12, " E m a i l10\0 C u s t o".., 8192) = 8192
write(12, "\0 C u s t o m e r S u".., 8192) = 8192
write(12, "1C &03\0 0 - 107\0 x o07".., 8192) = 8192
write(12, " t b o u n d - E m a".., 8192) = 8192 -64k
write(10, "\0 x\0\006\0\0\0\0\003 [".., 120) = 120
read(11, "07DB\0\006\0\0\0\0\0\b\0".., 2064) = 2011
read(11, "07DB\0\006\0\0\0\0\0 o r".., 2064) = 2064
read(11, "10\0 O u t b o u n d -".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0 e r".., 2064) = 2064
read(11, " S Q L L O A D E R10\0".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\010\0".., 2064) = 2064
read(11, " o0705161C &01\0 N\n\0 S".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\010\0".., 2064) = 2064
read(11, " x o0705161C &03\0 0 - 1".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0 o07".., 2064) = 2064
read(11, " 0 2 9 3 207\0 x o070516".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0 007".., 2064) = 2064
read(11, " - 107\0 x o0705161C &03".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0161C".., 2064) = 2064
read(11, "04\0 2 1 6 104\0 O O T P".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0 O O".., 2064) = 2064
read(11, " s t o m e r S u p p o".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0 u p".., 2064) = 2064
read(11, "0705161C &01\0 N\n\0 S Q".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0 -".., 2064) = 2064
read(11, "01\0 007\0 x o0705161C &".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\010\0".., 2064) = 2064
read(11, "03\0 0 - 101\08001\0 007".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0\0 S".., 2064) = 2064
read(11, "05161C &03\0 0 - 107\0 x".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0 - 1".., 2064) = 2064
read(11, "04\0 O O T P\b\0 6 3 9 0".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\00516".., 2064) = 2064
read(11, "FF04\0 O O T P\b\0 6 3 9".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0 &03".., 2064) = 2064
read(11, " 4 M 8 6 7 W03\0 8 0 304".., 1958) = 1958
read(11, "07DB\0\006\0\0\0\0\0 T P".., 2064) = 2064
read(11, " o m e r S u p p o r t".., 1477) = 1477
write(12, " 1 0 0\b\0 1 - 4 M 8 6 7".., 8192) = 8192
write(12, " u n d - E m a i l10".., 8192) = 8192
write(12, " l10\0 C u s t o m e r ".., 8192) = 8192
write(12, " t o m e r S u p p o r".., 8192) = 8192
write(12, "\0 1 - 4 M 8 6 7 W04\0 1".., 8192) = 8192
write(12, " - E m a i l10\0 C u s".., 8192) = 8192
write(12, " n d - E m a i l10\0".., 8192) = 8192
write(12, "\0 C u s t o m e r S u".., 8192) = 8192 -64k
write(10, "\0 x\0\006\0\0\0\0\003 [".., 120) = 120
Pmap shows only about 100k more memory consumed by adding recordlength
Conclusion:
so when you need to speed up your exp process via direct path, do remember to add recordlength and run it on local server
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22503721/viewspace-720648/ ，如需转载，请注明出处，否则将追究法律责任。
