[20171105]exp imp buffer参数解析.txt
oracle官方所给的关于buffer的解释如下：
https://docs.oracle.com/cd/A84870_01/doc/server.816/a76955/ch01.htm
BUFFER
Default: operating system-dependent. See your Oracle operating system-specific documentation to determine the default
value for this parameter.
Specifies the size, in bytes, of the buffer used to fetch rows. As a result, this parameter determines the maximum
number of rows in an array fetched by Export. Use the following formula to calculate the buffer size:
buffer_size = rows_in_array * maximum_row_size
If you specify zero, then the Export utility fetches only one row at a time.
Tables with columns of type LOBs, LONG, BFILE, REF, ROWID, LOGICAL ROWID, or DATE are fetched one row at a time.
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.
Example: Calculating Buffer Size
This section shows an example of how to calculate buffer size.
The following table is created:
CREATE TABLE sample (name varchar(30), weight number);
The maximum size of the name column is 30, plus 2 bytes for the indicator. The maximum size of the weight column is 22
(the size of the internal representation for Oracle numbers), plus 2 bytes for the indicator.
Therefore, the maximum row size is 56 (30+2+22+2).
To perform array operations for 100 rows, a buffer size of 5600 should be specified.
--//才知道以前写的猜测胡乱蒙对了,链接:blog.itpub.net/267265/viewspace-2144708/
--//另外感觉这份文档有点旧(8.1.6),明显数据类型date应该不存在这样的问题.
Tables with columns of type LOBs, LONG, BFILE, REF, ROWID, LOGICAL ROWID, or DATE are fetched one row at a time.
--//我当时计算的行长度每个字段少考虑2个字节.
CREATE TABLE sample (name varchar(30), weight number);
The maximum size of the name column is 30, plus 2 bytes for the indicator. The maximum size of the weight column is 22
(the size of the internal representation for Oracle numbers), plus 2 bytes for the indicator.
Therefore, the maximum row size is 56 (30+2+22+2).
To perform array operations for 100 rows, a buffer size of 5600 should be specified.
--//如果考虑2个字节,计算结果会更加接近.比如上次测试的例子:
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);
$ exp scott/book tables=T file=t.dmp direct=y buffer=1280000
...
alter table t rename to t1;
//drop table t purge ;
alter system flush shared_pool;
$ imp scott/book tables=T file=t.dmp buffer=1048576
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)
1024*1024/(3022+6) = 346.29326287978863936591
1000000/346.29326287978863936591 = 2887.72583007812500000006
1000000/346=2890.1734104462427745664
--//按照这样推断插入是2890次(实际上剩下的零头算1次就是2891次),与实际的结果非常接近.
//drop table t purge;
//drop table t1 purge;
create table t(x number, x2 varchar2(2000),x3 varchar2(2000)) 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);
$ exp scott/book tables=T file=t.dmp direct=y buffer=1280000
...
alter table t rename to t1;
//drop table t purge ;
alter system flush shared_pool;
$ imp scott/book tables=T file=t.dmp buffer=1048576
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" 3847
) VALUES (:1, :2, :3)
1024*1024/(4022+6) = 260.32174776564051638530
1000000/260.32174776564051638530 = 3841.40014648437500000004
1000000/260=3846.15384615384615384615
--//按照这样推断插入是3847次,与实际的结果非常接近.
--//另外按照文档介绍使用直接路径导出以及导入,参数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.
--//看来,学习oracle认真看官方文档很重要..^_^.