[20180226]exp buffer RECORDLENGTH.txt
--//虽然已经很少使用exp导致,如果加入direct=y参数,设置RECORDLENGTH参数能加快数据导出.
--//做一些简单探究:
--//以前测试链接:[20171105]exp imp buffer参数解析.txt 2=>http://blog.itpub.net/267265/viewspace-2146870/
--//另外按照文档介绍使用直接路径导出以及导入,参数RECORDLENGTH才有用.
--//另外也顺便探究buffer参数.
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
---------------
232
2.测试:
--//先测试不使用RECORDLENGTH ,direct=y.
$strace -f -o /tmp/exp1.trace exp scott/book file=t.dmp tables=t direct=y
$strace -f -o /tmp/exp2.trace exp scott/book file=t.dmp tables=t direct=y RECORDLENGTH=65535
--//对比:
$ grep 'pread(256' /tmp/exp1.trace | wc
240 1682 31242
$ grep 'pread(256' /tmp/exp2.trace | wc
241 1690 31356
--//可以发现读取记录调用次数一样.
$ grep 'write(9' /tmp/exp1.trace | wc
51468 363523 4364551
$ grep 'write(9' /tmp/exp2.trace | wc
29040 190891 2606066
$ grep 'write(9' /tmp/exp2.trace | head
13278 write(9, "NTP0 13278\n", 11) = 11
13278 write(9, "\0 \0\0\2\0\0\0\1:\f\1 \0\20\0\1\0\0\0\0 AA\0\0\0\0\0\0\0\0", 32 <unfinished ...>
13278 write(9, "\0\213\0\0\6\0\0\0\0\0\336\255\276\357\0\201\0\0\0\0\0\4\0\0\4\0\3\0\0\0\0\0"..., 139) = 139
13278 write(9, "\0\332\0\0\6\0\0\0\0\0\1\6\0x86_64/Linux 2.4.xx"..., 218) = 218
13278 write(9, "\0\32\0\0\6\0\0\0\0\0\2\200\0\0\0<<<\200\0\0\0\0\0\0\16", 26) = 26
13278 write(9, "\1\206\0\0\6\0\0\0\0\0\10\3\0\f\0\0\0\fAUTH_SESSKEY`\0"..., 390) = 390
13278 write(9, "\4\210\0\0\6\0\0\0\0\0\10\31\0\23\0\0\0\23AUTH_VERSION_S"..., 1160) = 1160
13278 write(9, "\0\34\0\0\6\0\0\0\0\0\10j\0\0\0\1\0\0\0\0\0\t\1\0\0\0\3\0", 28) = 28
13278 write(9, "\0\26\0\0\6\0\0\0\0\0\10\3\0\0\0\t\1\0\0\0\4\0", 22) = 22
13278 write(9, "\0\222\0\0\6\0\0\0\0\0\4\1\0\0\0\5\0\1\0\0\0\0\0\0\0\0\0\0\3\0\0\0"..., 146) = 146
$ grep 'write(9' /tmp/exp1.trace | head
13255 write(9, "NTP0 13255\n", 11) = 11
13255 write(9, "\0 \0\0\2\0\0\0\1:\f\1 \0\20\0\1\0\0\0\0 AA\0\0\0\0\0\0\0\0", 32 <unfinished ...>
13255 write(9, "\0\213\0\0\6\0\0\0\0\0\336\255\276\357\0\201\0\0\0\0\0\4\0\0\4\0\3\0\0\0\0\0"..., 139) = 139
13255 write(9, "\0\332\0\0\6\0\0\0\0\0\1\6\0x86_64/Linux 2.4.xx"..., 218) = 218
13255 write(9, "\0\32\0\0\6\0\0\0\0\0\2\200\0\0\0<<<\200\0\0\0\0\0\0\16", 26) = 26
13255 write(9, "\1\206\0\0\6\0\0\0\0\0\10\3\0\f\0\0\0\fAUTH_SESSKEY`\0"..., 390) = 390
13255 write(9, "\4\207\0\0\6\0\0\0\0\0\10\31\0\23\0\0\0\23AUTH_VERSION_S"..., 1159) = 1159
13255 write(9, "\0\34\0\0\6\0\0\0\0\0\10P\0\0\0\3\0\0\0\0\0\t\1\0\0\0\3\0", 28) = 28
13255 write(9, "\0\26\0\0\6\0\0\0\0\0\10\3\0\0\0\t\1\0\0\0\4\0", 22) = 22
13255 write(9, "\0\222\0\0\6\0\0\0\0\0\4\1\0\0\0\5\0\1\0\0\0\0\0\0\0\0\0\0\3\0\35\0"..., 146) = 146
--//可以发现写入文件描述9,开始基本一样.差异在中间:
$ grep 'write(9' /tmp/exp1.trace | less
...
13255 write(9, "\37\333\0\0\6\0\0\0\0\0\10\0 \0\0 "..., 8155) = 8155
13255 write(9, "\0J\0\0\6\0\0\0\0\0 "..., 74 <unfinished ...>
13255 write(9, "\37\333\0\0\6\0\0\0\0\0\10\0 \0\0aaaaaaaaaaaaaaaaa"..., 8155) = 8155
13255 write(9, "\0J\0\0\6\0\0\0\0\0aaaaaaaaaaaaaaaaaaaaaa"..., 74 <unfinished ...>
13255 write(9, "\37\333\0\0\6\0\0\0\0\0\10\0 \0\0 "..., 8155) = 8155
13255 write(9, "\0J\0\0\6\0\0\0\0\0 "..., 74 <unfinished ...>
13255 write(9, "\37\333\0\0\6\0\0\0\0\0\10\0 \0\0 "..., 8155) = 8155
--//direct=y,缺省RECORDLENGTH=8192,中间有写入仅仅74字节的情况.
$ grep 'write(9' /tmp/exp2.trace | less
..
13278 write(9, "\37\333\0\0\6\0\0\0\0\0\10\230\375\0\0\3\0\302\2&d\0 "..., 8155) = 8155
13278 write(9, "\37\333\0\0\6\0\0\0\0\0aaaaaa\2\0\301\7d\0 "..., 8155 <unfinished ...>
13278 write(9, "\37\333\0\0\6\0\0\0\0\0 "..., 8155 <unfinished ...>
13278 write(9, "\37\333\0\0\6\0\0\0\0\0 "..., 8155 <unfinished ...>
13278 write(9, "\37\333\0\0\6\0\0\0\0\0 "..., 8155 <unfinished ...>
13278 write(9, "\37\333\0\0\6\0\0\0\0\0 "..., 8155 <unfinished ...>
13278 write(9, "\37\333\0\0\6\0\0\0\0\0 "..., 8155 <unfinished ...>
13278 write(9, "\36\374\0\0\6\0\0\0\0\0 "..., 7932 <unfinished ...>
13278 write(9, "\37\333\0\0\6\0\0\0\0\0\10\377\377\0\0 "..., 8155) = 8155
13278 write(9, "\37\333\0\0\6\0\0\0\0\0 "..., 8155 <unfinished ...>
13278 write(9, "\37\333\0\0\6\0\0\0\0\0 "..., 8155 <unfinished ...>
13278 write(9, "\37\333\0\0\6\0\0\0\0\0\0\302\3<d\0 "..., 8155 <unfinished ...>
13278 write(9, "\37\333\0\0\6\0\0\0\0\0aaaaa\3\0\302\5Ed\0 "..., 8155 <unfinished ...>
13278 write(9, "\37\333\0\0\6\0\0\0\0\0aaaaaaaaaa\3\0\302\6\10d\0 "..., 8155 <unfinished ...>
13278 write(9, "\37\333\0\0\6\0\0\0\0\0aaaaaaaaaaaaaaaa\3\0\302\0071d"..., 8155 <unfinished ...>
13278 write(9, "\37\333\0\0\6\0\0\0\0\0aaaaaaaaaaaaaaaaaaaaaa"..., 8155 <unfinished ...>
13278 write(9, "\1\222\0\0\6\0\0\0\0\0aaaaaaaaaaaaaaaaaaaaaa"..., 402 <unfinished ...>
--//8155*7+7932 = 65017 在直接路径读取方式下.每次读取几乎都是8155,一定程度减少write调用次数.
$ grep 'read(8' /tmp/exp1.trace | wc
51469 205891 1647256
$ grep 'read(8' /tmp/exp2.trace | wc
29041 116232 930375
--//同样通过管道read的调用次数也与前面对应.read次数也对应增加.仅仅多1次.
$ grep 'write(6' /tmp/exp1.trace | wc
51267 339810 3501967
$ grep 'write(6' /tmp/exp2.trace | wc
6412 42503 444485
$ grep 'write(6' /tmp/exp1.trace | less
13254 write(6, "\3\3TD\nEXPORT:V11.02.00\nDSCOTT\nRTA"..., 8192) = 8192
13254 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
13254 write(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 4096) = 4096
13254 write(6, " "..., 4096) = 4096
13254 write(6, "aaaaaaaaa\2\0\301\30d\0 "..., 4096) = 4096
13254 write(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 4096) = 4096
13254 write(6, " "..., 4096) = 4096
13254 write(6, " "..., 4096) = 4096
13254 write(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 4096) = 4096
13254 write(6, " "..., 4096) = 4096
$ grep 'write(6' /tmp/exp2.trace | less
13277 write(6, "\3\3TD\nEXPORT:V11.02.00\nDSCOTT\nRTA"..., 61440) = 61440
13277 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
13277 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
13277 write(6, "\0aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 4096) = 4096
13277 write(6, " "..., 61440) = 61440
13277 write(6, " "..., 4096) = 4096
13277 write(6, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"..., 61440) = 61440
--//对比可以发现设置RECORDLENGTH=65535,加大每次写入的字节数,不过开始总是4096,然后61440,
--//4096+61440 = 65536,我估计RECORDLENGTH=65535是从0开始记数的.这样设置大的RECORDLENGTH=65535减少写入调用.
--//而缺省不设置RECORDLENGTH,应该8192.
$ grep 'write(7' /tmp/exp1.trace | wc
25838 180765 3269275
$ grep 'write(7' /tmp/exp2.trace | wc
3410 20478 443296
$ grep 'read(6' /tmp/exp1.trace | grep "^13255" |wc
25838 103355 826900
$ grep 'read(6' /tmp/exp2.trace | grep "^13278" |wc
3410 13640 109120
--//注意另外一个进程也会出现"read(6"的情况,必须加入开头进程号的过滤.
--//这里文件描述7,6是用来2个进程之间通讯的(我的理解不知道是否正确,^_^).如果你看前面
$ grep 'write(9' /tmp/exp1.trace | less
...
13255 write(9, "\37\333\0\0\6\0\0\0\0\0\10\0 \0\0 "..., 8155) = 8155
13255 write(9, "\0J\0\0\6\0\0\0\0\0 "..., 74 <unfinished ...>
--//我的理解以上2行等于写缓存满,写出后,主进程通过文件描述7写信息,通知子进程读取然可以继续读数据写入到文件描述9.
13255 write(9, "\37\333\0\0\6\0\0\0\0\0\10\0 \0\0aaaaaaaaaaaaaaaaa"..., 8155) = 8155
13255 write(9, "\0J\0\0\6\0\0\0\0\0aaaaaaaaaaaaaaaaaaaaaa"..., 74 <unfinished ...>
13255 write(9, "\37\333\0\0\6\0\0\0\0\0\10\0 \0\0 "..., 8155) = 8155
13255 write(9, "\0J\0\0\6\0\0\0\0\0 "..., 74 <unfinished ...>
13255 write(9, "\37\333\0\0\6\0\0\0\0\0\10\0 \0\0 "..., 8155) = 8155
$ grep 'write(9' /tmp/exp1.trace | rev | cut -f1 -d"=" | grep -v dehsinifnu|wc
25933 25933 155045
$ grep 'write(9' /tmp/exp2.trace | rev | cut -f1 -d"=" | grep -v dehsinifnu|wc
3480 3480 20578
$ grep 'write(9' /tmp/exp1.trace | rev | cut -f1 -d"=" | grep "^5518"|wc
25593 25593 153558
$ grep 'write(9' /tmp/exp2.trace | rev | cut -f1 -d"=" | grep "^5518"|wc
3257 3257 19542
--//可以看到这样的结果与上面write 7 的数量很接近.总之可以看出设置direct=y RECORDLENGTH=65535,可以减少open,write调用.
--//加快导出速度.
3.测试:
--//测试加入buffer的情况.
$ strace -f -o /tmp/exp3.trace exp scott/book file=t.dmp tables=t direct=y RECORDLENGTH=65535 buffer=10485760
$ grep 'pread(256' /tmp/exp2.trace | wc
241 1690 31356
$ grep 'pread(256' /tmp/exp3.trace | wc
240 1682 31242
--//可以发现读取记录调用次数一样.
$ grep 'write(9' /tmp/exp2.trace | wc
29040 190891 2606066
$ grep 'write(9' /tmp/exp3.trace | wc
29040 190884 2606153
--//可以发现写入调用次数一样.
$ grep 'write(6' /tmp/exp2.trace | wc
6412 42503 444485
$ grep 'write(6' /tmp/exp3.trace | wc
6412 42498 444515
--//可以发现读取调用次数一样.
$ grep 'write(7' /tmp/exp2.trace | wc
3410 20478 443296
$ grep 'write(7' /tmp/exp3.trace | wc
3410 20478 443642
$ grep 'read(6' /tmp/exp2.trace | grep "^13278" |wc
3410 13640 109120
$ grep 'read(6' /tmp/exp3.trace | grep "^14045" |wc
3409 13639 109151
--//基本一致.
$ grep 'write(9' /tmp/exp2.trace | rev | cut -f1 -d"=" | grep -v dehsinifnu|wc
3480 3480 20578
$ grep 'write(9' /tmp/exp3.trace | rev | cut -f1 -d"=" | grep -v dehsinifnu|wc
3496 3496 20679
$ grep 'write(9' /tmp/exp2.trace | rev | cut -f1 -d"=" | grep "^5518"|wc
3257 3257 19542
$ grep 'write(9' /tmp/exp3.trace | rev | cut -f1 -d"=" | grep "^5518"|wc
3279 3279 19674
--//基本接近.说明buffer=10485760对于直接路径导出基本无用.
--//或者讲设置diect=y的情况下buffer设置无用,影响性能的是RECORDLENGTH.
$ time exp scott/book file=t.dmp tables=t direct=y RECORDLENGTH=65535
real 0m2.072s
user 0m0.132s
sys 0m1.185s
$ time exp scott/book file=t.dmp tables=t buffer=10485760 direct=y RECORDLENGTH=65535
real 0m2.135s
user 0m0.139s
sys 0m1.158s
--//2者很接近.
$ time exp scott/book file=t.dmp tables=t direct=y
real 0m3.424s
user 0m0.358s
sys 0m1.424s
$ time exp scott/book file=t.dmp tables=t buffer=10485760
real 0m1.866s
user 0m1.046s
sys 0m0.636s
--//时候使用buffer参数还快一点点.
4.测试使用buffer的情况:
$strace -f -o /tmp/exp2.trace exp scott/book file=t.dmp tables=t direct=y RECORDLENGTH=65535
$strace -f -o /tmp/exp4.trace exp scott/book file=t.dmp tables=t buffer=10485760
$ grep 'pread(256' /tmp/exp2.trace | wc
241 1690 31356
$ grep 'pread(256' /tmp/exp4.trace | wc
0 0 0
$ grep 'pread' /tmp/exp4.trace | wc
0 0 0
--//使用缓存参数,根本不调用pread.如果你看共享池,可以知道使用buffer参数会执行如下语句.
SCOTT@book> select sql_id,executions,sql_text from v$sql where sql_id='5v855bth711dw';
SQL_ID EXECUTIONS SQL_TEXT
------------- ---------- --------------------------------------------------------------------------------
5v855bth711dw 1 SELECT /*+NESTED_TABLE_GET_REFS+*/ "SCOTT"."T".* FROM "SCOTT"."T"
--//找到这样的语句,查询sql_text包含NESTED_TABLE_GET_REFS字符串就可以找到.如果使用直接路径读.
$ exp scott/book file=t.dmp tables=t direct=y RECORDLENGTH=65535
SCOTT@book> select sql_id,executions,sql_text from v$sql where sql_id='5v855bth711dw';
SQL_ID EXECUTIONS SQL_TEXT
------------- ---------- --------------------------------------------------------------------------------
5v855bth711dw 1 SELECT /*+NESTED_TABLE_GET_REFS+*/ "SCOTT"."T".* FROM "SCOTT"."T"
--//你可以发现执行次数并没有增加,这也是直接路径读的特点,直接读取数数据块,绕过了数据缓存.
SCOTT@book> select object_id,data_object_id from dba_objects where owner=user and object_name='T';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
90385 90385
SCOTT@book> select count(*) from v$bh where objd=90385 and status!='free';
COUNT(*)
----------
29696
SCOTT@book> select 29696*8192/1024/1024 from dual ;
29696*8192/1024/1024
--------------------
232
--//是因为我的测试环境已经缓存了表T.
SCOTT@book> alter system flush buffer_cache;
System altered.
$ time exp scott/book file=t.dmp tables=t buffer=10485760
real 0m2.177s
user 0m1.223s
sys 0m0.706s
--//这样基本接近了.
--//限于篇幅,不再展开,总之使用direct=y recordlength=65536 buffer=10485760导出.
--//加快导出速度,为什么要使用buffer,因为如果表lob类型,不支持diect导出,
--//另外11g的一些特性,比如增加字段
--//alter table t add ( c number default 10 not null);这样的情况不能使用direct导出.
--//如果对某一个已经存在数据的表进行了新增了非空+default字段之后，实际上11g因为避免把所有block都修改一遍，所
--//以并没有真正的update底层数据，而是直接修改了数据字典。这样的好处显而易见，alter 表非常快，不会长时间持有library cache
--//lock。执行sql查询这个新字段的时候，对于老的数据sql引擎会自动从数据字典里面把default读出来，对于新的数据就直接读取磁盘上
--//的数据，但是当exp导出的时候，若是采用direct=y，因为跳过sql层，所以直接读取了block，所以老数据的block里面因为没有这个字段
--//当然最终被处理成null插入新表，所以就出现了上述的问题。那么这个问题解决的办法也很简单，就是采用常规形式导出，避免使用
--//direct=y，另外oracle 在10g之后就推荐使用expdp+impdp，这套新工具也能避免这个问题。
--//参考连接=>http://blog.itpub.net/267265/viewspace-2145141/ [20170918]exp 直接路径导出.txt
--//测试例子:
create table t ( a number);
insert into t values(1);
insert into t values(2);
insert into t values(3);
commit;
alter table t add ( c number default 10 not null);
$ exp scott/book file=t.dmp tables=t direct=y RECORDLENGTH=65535
SCOTT@book> alter table t add ( c number default 10 not null);
Table altered.
SCOTT@book> alter table t rename to t1;
Table altered.
$ imp scott/book tables=T file=t.dmp buffer=1048576
Import: Release 11.2.0.4.0 - Production on Mon Feb 26 16:52: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 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"
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("SCOTT"."T"."C")
Column : 1
Column :
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("SCOTT"."T"."C")
Column : 2
Column :
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("SCOTT"."T"."C")
Column : 3
Column : 0 rows imported
Import terminated successfully with warnings.
SCOTT@book> select * from t;
no rows selected
--//如果要使用direct要检查sys.ecol$;表.看看是否这样定义的表.
SCOTT@book> column BINARYDEFVAL format a10
SCOTT@book> select * from sys.ecol$;
TABOBJ# COLNUM BINARYDEFV
---------- ---------- ----------
90387 2 C10B
SCOTT@book> select object_id,data_object_id,owner,object_name from dba_objects where DATA_OBJECT_ID=90387;
OBJECT_ID DATA_OBJECT_ID OWNER OBJECT_NAME
---------- -------------- ------ --------------------
90387 90387 SCOTT T1
