[20180224]exp参数RECORDLENGTH.txt
--//虽然已经很少使用exp导致,如果加入direct=y参数,设置RECORDLENGTH参数能加快数据导出.
--//做一些简单探究:
--//以前测试链接:[20171105]exp imp buffer参数解析.txt 2=>http://blog.itpub.net/267265/viewspace-2146870/
--//另外按照文档介绍使用直接路径导出以及导入,参数RECORDLENGTH才有用.
Note:
The BUFFER parameter applies only to conventional path Export. It has no effect on a direct path Export. For direct path
Exports, use the RECORDLENGTH parameter to specify the size of the buffer that Export uses for writing to the export
file.
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> show parameter filesystemio_options
NAME TYPE VALUE
-------------------- ------ -----
filesystemio_options string NONE
--//主要目的不使用异步IO,跟踪信息目前还不会看,读懂.
create table t(x number, x2 varchar2(2000),x3 varchar2(1000)) SEGMENT CREATION IMMEDIATE;
insert into t select level, rpad(' ', 100, ' '),rpad('a',100,'a') from dual connect by level <= 1e6;
commit ;
exec sys.dbms_stats.gather_table_stats ( OwnName => 'SCOTT',TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false);
SCOTT@book> select bytes/1024/1024 from dba_segments where owner=user and segment_name='T';
BYTES/1024/1024
---------------
238
2.测试:
--//先测试不使用RECORDLENGTH ,direct=y.
$strace -f -o /tmp/exp.trace exp scott/book file=t.dmp tables=t direct=y
...
$ grep t.dmp /tmp/exp1.trace
52705 execve("/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/exp", ["exp", "scott/book", "file=t.dmp", "tables=t", "direct=y"], [/* 48 vars */]) = 0
52705 stat("t.dmp", 0x7fff829c9090) = -1 ENOENT (No such file or directory)
52705 stat("t.dmp", 0x7fff829c8b30) = -1 ENOENT (No such file or directory)
52705 open("t.dmp", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 6
--//可以看出t.dmp文件对应的文件描述是6.
$ grep "write(6" /tmp/exp1.trace | head
52705 write(6, "\3\3TD\nEXPORT:V11.02.00\nDSCOTT\nRTA"..., 8192) = 8192
52705 write(6, "d\0\0\0\0`\1$\17\5\v\f\3\f\f\5\4\5\r\6\t\7\10\5\5\5\5\5\17\5\5\5"..., 4096) = 4096
52705 write(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 4096) = 4096
52705 write(6, " "..., 4096) = 4096
52705 write(6, "aaaaaaaaa\2\0\301\30d\0 "..., 4096) = 4096
52705 write(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 4096) = 4096
52705 write(6, " "..., 4096) = 4096
52705 write(6, " "..., 4096) = 4096
52705 write(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 4096) = 4096
52705 write(6, " "..., 4096) = 4096
$ grep "write(6" /tmp/exp1.trace | tail
52705 write(6, " "..., 4096) = 4096
52705 write(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 4096) = 4096
52705 write(6, " "..., 4096) = 4096
52705 write(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 4096) = 4096
52705 write(6, " "..., 4096) = 4096
52705 write(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa\4"..., 4096) = 4096
52705 write(6, " "..., 4096) = 4096
52705 write(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaa\4\0\303"..., 4096) = 4096
52705 write(6, " d"..., 4096 <unfinished ...>
52705 write(6, " SREC DBMS_STATS.STATREC; BEGIN "..., 4096 <unfinished ...>
$ grep "write(6" /tmp/exp1.trace | rev | cut -d"=" -f1 | rev | sort |uniq -c
51258 4096
1 52705 write(6, "\303U?ad\0 "..., 4096 <unfinished ...>
1 52705 write(6, " "..., 4096 <unfinished ...>
1 52705 write(6, "aaaaaaaaaaaaaaaaaaaaaa\3\0\3033Xd\0 "..., 4096 <unfinished ...>
1 52705 write(6, " d\0aaaa"..., 4096 <unfinished ...>
1 52705 write(6, " d\0aaaaaaaaaaaaaaaaaa"..., 4096 <unfinished ...>
1 52705 write(6, " d\0aaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 4096 <unfinished ...>
1 52705 write(6, " d"..., 4096 <unfinished ...>
1 52705 write(6, " SREC DBMS_STATS.STATREC; BEGIN "..., 4096 <unfinished ...>
1 8192
--//注:使用rec反转显示,尾部到行头显示,这样cut -d"=" -f1 可以取出最后写入的字节量.在rec回来.
--//可以发现每次写入都是4096字节,而出现如下8192,仅仅第1次写入,看grep "write(6" /tmp/exp1.trace | head.
--//可以看出这样操作写入次数大量增加.
3.测试
--//先测试使用RECORDLENGTH ,direct=y.
$ strace -f -o /tmp/exp2.trace exp scott/book file=t.dmp tables=t direct=y RECORDLENGTH=1048576
Export: Release 11.2.0.4.0 - Production on Sat Feb 24 09:44:41 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Note: RECORDLENGTH=1048576 truncated to 65535
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Direct Path ...
. . exporting table T 1000000 rows exported
Export terminated successfully without warnings.
--//注意看下划线内容,实际上接收的最大参数65535,很奇怪为什么不是65536(64*1024=65536)而是65535.这么奇怪的数值.
--//或许从0开始记数.
$ grep t.dmp /tmp/exp2.trace
52926 execve("/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/exp", ["exp", "scott/book", "file=t.dmp", "tables=t", "direct=y", "RECORDLENGTH=1048576"], [/* 48 vars */]) = 0
52926 stat("t.dmp", {st_mode=S_IFREG|0644, st_size=209993728, ...}) = 0
52926 stat("t.dmp", {st_mode=S_IFREG|0644, st_size=209993728, ...}) = 0
52926 open("t.dmp", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 6
$ grep "write(6" /tmp/exp2.trace | head
52926 write(6, "\3\3TD\nEXPORT:V11.02.00\nDSCOTT\nRTA"..., 61440) = 61440
52926 write(6, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 4096) = 4096
52926 write(6, "\0\0\0\0`\1$\17\5\v\f\3\f\f\5\4\5\r\6\t\7\10\5\5\5\5\5\17\5\5\5\5"..., 61440) = 61440
52926 write(6, " d\0aaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 4096) = 4096
52926 write(6, " "..., 61440) = 61440
52926 write(6, " "..., 4096) = 4096
52926 write(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 61440) = 61440
52926 write(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 4096) = 4096
52926 write(6, " "..., 61440) = 61440
52926 write(6, " "..., 4096) = 4096
$ grep "write(6" /tmp/exp2.trace | tail
52926 write(6, "aaa\4\0\303dY)d\0 "..., 61440) = 61440
52926 write(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 4096) = 4096
52926 write(6, " "..., 61440) = 61440
52926 write(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 4096) = 4096
52926 write(6, " "..., 61440) = 61440
52926 write(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 4096) = 4096
52926 write(6, " "..., 61440) = 61440
52926 write(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 4096 <unfinished ...>
52926 write(6, " "..., 61440 <unfinished ...>
52926 write(6, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 890 <unfinished ...>
--//61440+4096 = 65536,实际上最大的写入61440.
--//先写4096,再写61440.如此往复.
$ grep "write(6" /tmp/exp2.trace | rev | cut -d"=" -f1 | rev | sort |uniq -c
3204 4096
1 52926 write(6, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 890 <unfinished ...>
1 52926 write(6, " "..., 61440 <unfinished ...>
1 52926 write(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 4096 <unfinished ...>
3205 61440
--//对比前面可以发现如果加大RECORDLENGTH参数,可以减少写入次数.
$ echo -e $(grep "write(6" /tmp/exp1.trace | wc -l ) "\n" $(grep "write(6" /tmp/exp2.trace | wc -l )
51267
6412
--//不使用参数51267次数,使用参数 6412次. 51267/6412 = 7.99547723019338739862 ,减少大约8倍的写入数量.
$ grep "pread(257" /tmp/exp1.trace | wc
242 1696 30745
$ grep "pread(256" /tmp/exp2.trace | wc
242 1696 30745
$ grep "pread(257" /tmp/exp1.trace | rev | cut -f1 -d"=" | rev | sort| uniq -c
21 1015808
62 1032192
1 1040384
147 1048576
1 106496
5 122880
1 131072
2 57344
2 65536
$ grep "pread(256" /tmp/exp2.trace | rev | cut -f1 -d"=" | rev | sort| uniq -c
21 1015808
62 1032192
1 1040384
147 1048576
1 106496
5 122880
1 131072
2 57344
2 65536
1 8192
--//不知道为什么第1次文件描述257,不过可以看出读取大部分在1M的数量.两者基本一致.
$ grep "pread(256" /tmp/exp2.trace | rev | cut -f1 -d"=" | rev | xargs echo | sed 's/ /+/g' | bc -l
241614848
--//241614848/1024/1024 = 230.421875M.
4.测试导入的情况:
alter table t rename to t1;
//drop table t purge ;
alter system flush shared_pool;
$ strace -f -o /tmp/imp1.trace imp scott/book tables=T file=t.dmp direct=y
LRM-00101: unknown parameter name 'direct'
IMP-00022: failed to process parameters, type 'IMP HELP=Y' for help
IMP-00000: Import terminated unsuccessfully
--//没有参数direct=y.
$ strace -f -o /tmp/imp1.trace imp scott/book tables=T file=t.dmp RECORDLENGTH=1048576
Import: Release 11.2.0.4.0 - Production on Sat Feb 24 10:18:32 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Note: RECORDLENGTH=1048576 truncated to 65535
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via direct path
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "T" 1000000 rows imported
Import terminated successfully without warnings.
--//注意看下划线.RECORDLENGTH=65535,注意
Export file created by EXPORT:V11.02.00 via direct path
--//可以发现oracle知道生成的文件是经过direct path导出的.
--//慢的惊人!!不知道是否跟踪有一定影响.(生成的跟踪文件182M)
$ ls -l /tmp/imp*
-rw-r--r-- 1 oracle oinstall 190436703 2018-02-24 10:21:34 /tmp/imp1.trace
SCOTT@book> select sql_id,sql_text,executions from v$sql where sql_id='62m8tgc8mhwr2';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
62m8tgc8mhwr2 INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "T" ("X", "X2", "X3" 100000
) VALUES (:1, :2, :3)
--//可以发现导入使用RECORDLENGTH没用,每条记录1次插入.要使用buffer.我重新测试:
$ strace -f -o /tmp/imp2.trace imp scott/book tables=T file=t.dmp RECORDLENGTH=1048576 buffer=1048576
Import: Release 11.2.0.4.0 - Production on Sat Feb 24 10:27:58 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Note: RECORDLENGTH=1048576 truncated to 65535
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via direct path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "T" 1000000 rows imported
Import terminated successfully without warnings.
SCOTT@book> select sql_id,sql_text,executions from v$sql where sql_id='62m8tgc8mhwr2';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
62m8tgc8mhwr2 INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "T" ("X", "X2", "X3" 2891
) VALUES (:1, :2, :3)
ls -l /tmp/imp*
-rw-r--r-- 1 oracle oinstall 190436703 2018-02-24 10:21:34 /tmp/imp1.trace
-rw-r--r-- 1 oracle oinstall 40022490 2018-02-24 10:28:41 /tmp/imp2.trace
--//快许多.生成的跟踪文件小许多
--//可以发现导入真正起作用是参数buffer.切记!!如果对比我以前的测试http://blog.itpub.net/267265/viewspace-2146870/,执行次数没有变化.
--//在buffer=1M的情况下.
--//可以发现起作用的是参数buffer.
5.简单分析看看:
$ grep 't.dmp' /tmp/imp1.trace
53571 execve("/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/imp", ["imp", "scott/book", "tables=T", "file=t.dmp", "RECORDLENGTH=1048576"], [/* 48 vars */]) = 0
53571 stat("t.dmp", {st_mode=S_IFREG|0644, st_size=210105210, ...}) = 0
53571 stat("t.dmp", {st_mode=S_IFREG|0644, st_size=210105210, ...}) = 0
53571 open("t.dmp", O_RDONLY) = 6
--//t.dmp对应文件描述6.
$ grep 'write(7,' /tmp/imp1.trace |wc
100069 600808 11071065
$ grep 'write(7,' /tmp/imp2.trace |wc
26081 170956 2360152
--//写入调用的数量存在很大区别.
$ grep 'read' /tmp/imp1.trace | wc
407255 2449519 29203620
$ grep 'read' /tmp/imp2.trace | wc
41750 272291 2900552
--//读取调用的数量存在很大区别.
$ grep 'read(6' /tmp/imp1.trace
....
53571 read(6, "\302\26\7d\0 "..., 4096) = 4096
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53572 read(6, <unfinished ...>
53571 read(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 61440) = 61440
--//注意看前面第1列的进程号53571,读4096字节,然后使用进程号53572连续读,然后汇集到进程53571.(我的理解不知道是否正确!!)
$ grep 'read(6' /tmp/imp2.trace
53726 read(6, " "..., 4096) = 4096
53727 read(6, <unfinished ...>
53727 read(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 8155) = 8155
53727 read(6, " "..., 8155) = 8155
53727 read(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 8155) = 8155
53727 read(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaa\7\3\302\0325"..., 8155) = 8155
53727 read(6, " "..., 8155) = 8155
53727 read(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 8155) = 8155
53727 read(6, "aaaaaaaaaaaaaaaaa\7\3\302\35)d "..., 8155) = 6434
53727 read(6, <unfinished ...>
53726 read(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 61440) = 61440
--//读取次数明显减少.
$ egrep 'read|write' /tmp/imp1.trace |less
...
53571 read(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 4096) = 4096
53571 write(7, "\0103\0\0\6\0\0\0\0\0\3\4J\5\0\0\0\n\0\0\0\0\0\0\0\0\0\0\0\7\3\302"..., 2099 <unfinished ...>
53572 <... read resumed> "\0103\0\0\6\0\0\0\0\0\3\4J\5\0\0\0\n\0\0\0\0\0\0\0\0\0\0\0\7\3\302"..., 8208) = 2099
53571 <... write resumed> ) = 2099
53571 read(8, <unfinished ...>
53572 write(9, "\0\242\0\0\6\0\0\0\0\0\4\2\0\0\0G\1\1\n\0\0\0\0\0\0\0\0\0\5\0\0\0"..., 162) = 162
53571 <... read resumed> "\0\242\0\0\6\0\0\0\0\0\4\2\0\0\0G\1\1\n\0\0\0\0\0\0\0\0\0\5\0\0\0"..., 8208) = 162
53572 read(6, <unfinished ...>
53571 write(7, "\0103\0\0\6\0\0\0\0\0\3\4K\5\0\0\0\n\0\0\0\0\0\0\0\0\0\0\0\7\3\302"..., 2099 <unfinished ...>
53572 <... read resumed> "\0103\0\0\6\0\0\0\0\0\3\4K\5\0\0\0\n\0\0\0\0\0\0\0\0\0\0\0\7\3\302"..., 8208) = 2099
53571 <... write resumed> ) = 2099
53571 read(8, <unfinished ...>
53572 write(9, "\0\242\0\0\6\0\0\0\0\0\4\2\0\0\0H\1\1\n\0\0\0\0\0\0\0\0\0\5\0\0\0"..., 162) = 162
53571 <... read resumed> "\0\242\0\0\6\0\0\0\0\0\4\2\0\0\0H\1\1\n\0\0\0\0\0\0\0\0\0\5\0\0\0"..., 8208) = 162
53572 read(6, <unfinished ...>
53571 write(7, "\0103\0\0\6\0\0\0\0\0\3\4L\5\0\0\0\n\0\0\0\0\0\0\0\0\0\0\0\7\3\302"..., 2099 <unfinished ...>
53572 <... read resumed> "\0103\0\0\6\0\0\0\0\0\3\4L\5\0\0\0\n\0\0\0\0\0\0\0\0\0\0\0\7\3\302"..., 8208) = 2099
53571 <... write resumed> ) = 2099
53571 read(8, <unfinished ...>
53572 write(9, "\0\242\0\0\6\0\0\0\0\0\4\2\0\0\0I\1\1\n\0\0\0\0\0\0\0\0\0\5\0\0\0"..., 162) = 162
53571 <... read resumed> "\0\242\0\0\6\0\0\0\0\0\4\2\0\0\0I\1\1\n\0\0\0\0\0\0\0\0\0\5\0\0\0"..., 8208) = 162
53572 read(6, <unfinished ...>
53571 write(7, "\0103\0\0\6\0\0\0\0\0\3\4M\5\0\0\0\n\0\0\0\0\0\0\0\0\0\0\0\7\3\302"..., 2099 <unfinished ...>
53572 <... read resumed> "\0103\0\0\6\0\0\0\0\0\3\4M\5\0\0\0\n\0\0\0\0\0\0\0\0\0\0\0\7\3\302"..., 8208) = 2099
53571 <... write resumed> ) = 2099
53571 read(8, <unfinished ...>
53572 write(9, "\0\242\0\0\6\0\0\0\0\0\4\2\0\0\0J\1\1\n\0\0\0\0\0\0\0\0\0\5\0\0\0"..., 162) = 162
53571 <... read resumed> "\0\242\0\0\6\0\0\0\0\0\4\2\0\0\0J\1\1\n\0\0\0\0\0\0\0\0\0\5\0\0\0"..., 8208) = 162
53572 read(6, <unfinished ...>
53571 write(7, "\0103\0\0\6\0\0\0\0\0\3\4N\5\0\0\0\n\0\0\0\0\0\0\0\0\0\0\0\7\3\302"..., 2099 <unfinished ...>
53572 <... read resumed> "\0103\0\0\6\0\0\0\0\0\3\4N\5\0\0\0\n\0\0\0\0\0\0\0\0\0\0\0\7\3\302"..., 8208) = 2099
53571 <... write resumed> ) = 2099
53571 read(8, <unfinished ...>
....
$ egrep 'read|write' /tmp/imp2.trace |less
53726 read(6, " "..., 4096) = 4096
53726 write(7, "\37\333\0\0\6\0\0\0\0\0\3\4C\1\0\0\0Z\1\0\0\0\0\0\0\0\0\0\0\7\3\302"..., 8155) = 8155
53727 <... read resumed> "\37\333\0\0\6\0\0\0\0\0\3\4C\1\0\0\0Z\1\0\0\0\0\0\0\0\0\0\0\7\3\302"..., 8208) = 8155
53726 write(7, "\37\333\0\0\6\0\0\0\0\0 "..., 8155 <unfinished ...>
53726 <... write resumed> ) = 8155
53726 write(7, "\37\333\0\0\6\0\0\0\0\0aaaaaaaaaaaaaaaaaaaaaa"..., 8155 <unfinished ...>
53726 <... write resumed> ) = 8155
53726 write(7, "\37\333\0\0\6\0\0\0\0\0aaaaaaaaaa\7\3\302-,d "..., 8155 <unfinished ...>
53726 <... write resumed> ) = 8155
53726 write(7, "\37\333\0\0\6\0\0\0\0\0 "..., 8155 <unfinished ...>
53726 <... write resumed> ) = 8155
53726 write(7, "\37\333\0\0\6\0\0\0\0\0aaaaaaaaaaaaaaaaaaaaaa"..., 8155) = 8155
53727 read(6, <unfinished ...>
53726 write(7, "\37\333\0\0\6\0\0\0\0\0\7\3\302-<d "..., 8155 <unfinished ...>
53727 <... read resumed> "\37\333\0\0\6\0\0\0\0\0 "..., 8208) = 8208
53726 <... write resumed> ) = 8155
53726 write(7, "\37\333\0\0\6\0\0\0\0\0 "..., 8155 <unfinished ...>
53726 <... write resumed> ) = 8155
53726 write(7, "\31X\0\0\6\0\0\0\0\0aaaaaaaaaaaaaaaaaaaaaa"..., 6488 <unfinished ...>
53726 <... write resumed> ) = 6488
53726 read(8, <unfinished ...>
53727 read(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 8155) = 8155
53727 read(6, " "..., 8155) = 8155
53727 read(6, " daaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 8155) = 8155
53727 read(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa\7\3"..., 8155) = 8155
53727 read(6, " "..., 8155) = 8155
53727 read(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 8155) = 8155
53727 read(6, "aaaaaaaaaaaaaaaaaaa\7\3\3028]d "..., 8155) = 6435
53727 write(9, "\0\242\0\0\6\0\0\0\0\0\4\2\0\0\0@\0\1Z\1\0\0\0\0\0\0\0\0\1\0\0\0"..., 162) = 162
53726 <... read resumed> "\0\242\0\0\6\0\0\0\0\0\4\2\0\0\0@\0\1Z\1\0\0\0\0\0\0\0\0\1\0\0\0"..., 8208) = 162
53727 read(6, <unfinished ...>
53726 read(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 61440) = 61440
总结:
1.exp 要使用参数RECORDLENGTH=1048576 direct=y,缺省就是direct=N.RECORDLENGTH 最大65535.
2.imp 要使用参数buffer
3.这样才能加快导入导出.
--//补充测试:
$ exp scott/book file=t.dmp tables=t RECORDLENGTH=1048576
Export: Release 11.2.0.4.0 - Production on Sat Feb 24 11:30:00 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Note: RECORDLENGTH=1048576 truncated to 65535
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
. . exporting table T 1000000 rows exported
Export terminated successfully without warnings.
--//光指定参数RECORDLENGTH=1048576,并不会执行direct path,注意看下划线.
$ imp scott/book tables=T file=t.dmp buffer=1048576
Import: Release 11.2.0.4.0 - Production on Sat Feb 24 11:32:37 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "T" 1000000 rows imported
Import terminated successfully without warnings.
--//从这里更加明显看出oracle知道文件生成是conventional path.
SCOTT@book> select sql_id,sql_text,executions from v$sql where sql_id='62m8tgc8mhwr2';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
62m8tgc8mhwr2 INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "T" ("X", "X2", "X3" 2891
) VALUES (:1, :2, :3)
