ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 加速exp-direct with recordlength

加速exp-direct with recordlength

原创 Linux操作系统 作者:feiyuzitong 时间:2012-04-08 12:44:36 0 删除 编辑

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博客 ” ,链接:http://blog.itpub.net/22503721/viewspace-720648/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-04-19

  • 博文量
    20
  • 访问量
    70483