[20171105]exp imp buffer参数解析.txt

oracle官方所给的关于buffer的解释如下：

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认真看官方文档很重要..^_^.