[20171105]exp imp buffer引數解析.txt

[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認真看官方文件很重要..^_^.