it'll be roughly 64k -- there is overhead.

For example, a number will always consume 44 bytes in buffer. But the numbers you are inserting will most likely NOT consume 44 bytes. 44 bytes is the max

Think about this -- you have a table with 10 varchar2(4000) columns. The maxed out space will be 40,000 (plus overhead) bytes. Hence, using a 64k buffer, you'll get at most 1 record per insert. Even if you insert a record with 9 nulls and the string "hello world" -- one record (since the buffer is a maxed out representation of the row)

so, you'll get a commit after not each 64k of data -- but after you've inserted the number of rows that, when you compute the WORST CASE rowsize -- fit into that 64k buffer (eg: if your worst case rowsize is 32k - 1 row per insert. 20k -- 3 rows, 10k -- 6rows and so on).


You can observe this behaviour fairly easily. Consider a script like this:


create table t_1byte ( x char(1) );
create table t_1k ( x varchar2(1000) );
create table t_5k ( a varchar2(4000), b varchar2(1000) );
create table t_10k
( a varchar2(4000), b varchar2(4000), c varchar2(2000) );
create table t_20k
( a varchar2(4000), b varchar2(4000), c varchar2(4000),
d varchar2(4000), e varchar2(4000) );
create table t_40k
( a varchar2(4000), b varchar2(4000), c varchar2(4000),
d varchar2(4000), e varchar2(4000) ,
f varchar2(4000), g varchar2(4000), h varchar2(4000),
i varchar2(4000), j varchar2(4000) );

insert into t_1byte select 1 from all_objects;
insert into t_1k select 1 from all_objects;
insert into t_5k(a) select 1 from all_objects;
insert into t_10k(a) select 1 from all_objects;
insert into t_20k(a) select 1 from all_objects;
insert into t_40k(a) select 1 from all_objects;
commit;

!exp userid=/ 'tables=(t_1byte,t_1k,t_5k,t_10k,t_20k,t_40k)'

so, we have tables of various sizes -- various MAX sizes -- but they all have the same *data* in them and then we


create or replace trigger tkyte_logon
after logon on schema
begin
execute immediate 'alter session set sql_trace=true';
end;
/

drop table t_1byte;
drop table t_1k;
drop table t_5k;
drop table t_10k;
drop table t_20k;
drop table t_40k;

!imp userid=/ commit=y buffer=65367 full=y



the tkprof is very telling as to the array size used for each:

INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "T_1BYTE" ("X")
VALUES
(:1)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.09 0.15 0 83 783 30563
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.09 0.16 0 83 783 30563


that shows we did two inserts here. A CHAR(1) takes two bytes (leading length plus the data) so that makes sense -- with the overhead, we got most of the rows on the first insert and a couple of stragglers on the second. The math is easier with 1k:

INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "T_1K" ("X")
VALUES
(:1)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 478 0.29 0.27 0 90 1612 30563
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 479 0.30 0.27 0 90 1612 30563

ops$tkyte@ORA920> select 30563/478 from dual;

30563/478
----------
63.9393305


about 64 rows/insert, using a 64k buffer -- that makes sense

INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "T_5K" ("A", "B")
VALUES
(:1, :2)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2547 1.10 1.14 0 34 5628 30563
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2548 1.11 1.15 0 34 5628 30563

there, 5k MAX rowsize

ops$tkyte@ORA920> select 30563/2547 from dual;

30563/2547
----------
11.9996074

about 12 (12*5 = 60 with 4k leftover)....

INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "T_10K" ("A", "B", "C")
VALUES
(:1, :2, :3)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 5094 2.93 2.94 0 34 36500 30563
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5095 2.94 2.95 0 34 36500 30563

ops$tkyte@ORA920> select 30563/5094 from dual;

30563/5094
----------
5.99980369

about 6 rows per insert (but see how the time is going UP UP UP -- same data, shows the efficiency of array processing)


INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "T_20K" ("A", "B", "C", "D", "E")
VALUES
(:1, :2, :3, :4, :5)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10188 5.17 5.25 0 34 41613 30563
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10189 5.17 5.25 0 34 41613 30563


ops$tkyte@ORA920> select 30563/10188 from dual;

30563/10188
-----------
2.99990185


3 rows per insert, slower still...


INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "T_40K" ("A", "B", "C", "D", "E", "F",
"G", "H", "I", "J")
VALUES
(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 30563 12.69 14.31 0 34 62097 30563
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 30564 12.69 14.31 0 34 62097 30563

1 row per insert -- even though it was one byte big!


And believe me, that 40k insert took a heck of alot longer then 14 seconds, it was 10's of minutes from IMP due to back and forth and back and forth and waiting for commits to complete (lots of log file syncs going on there)


You want to set buffer really really really big on import to achieve "faster=true".

Use commit=y at your own risk (tons of log file syncs will be in your future)