I set up a test to compare
* Conventional path loading
* Conventional path loading With Error logging
* Direct path loading
* Direct path loading With Error logging
* Plsql Bulk binding with SAVE Exceptions
and how each would go. I did it with three levels of constraints:
* no constraints (beyond NOT NULL)
* a check constraint
* a primary key constraint
and in two modes:
* direct path for the inserts (conventional of course for the row by row)
* conventional path all around.
With data source
* One that is physically loaded in sorted order ( Clustered table )
* One that is randomly sorted ( non-clustered table )
Physically loaded in Sorted order
create table big_table_clust
nologging
as
select rownum as cust_id,
object_name as cust_name,
abs(dbms_random.random) as txn_amount,
to_date('01/01/1900','mm/dd/yyyy')+rownum as dob
from all_objects;
declare
l_max_id number;
begin
for i in 1..2
loop
select max(cust_id)
into l_max_id
from big_table_clust;
insert /*+ append */ into big_table_clust (cust_id,cust_name,txn_amount,dob)
select l_max_id+rownum, cust_name,txn_amount,dob
from big_table_clust;
commit;
end loop;
end;
/
create table t1(
x number constraint t1_pk primary key,
y varchar2(30),
z number constraint t1_chk check (z is not null),
dt date
);
create table t2(
x number constraint t2_pk primary key,
y varchar2(30),
z number constraint t2_chk check (z is not null),
dt date
);
create table t3(
x number constraint t3_pk primary key,
y varchar2(30),
z number constraint t3_chk check (z is not null),
dt date
);
create table t4(
x number constraint t4_pk primary key,
y varchar2(30),
z number constraint t4_chk check (z is not null),
dt date
);
create table t5(
x number constraint t5_pk primary key,
y varchar2(30),
z number constraint t5_chk check (z is not null),
dt date
);
exec dbms_errlog.create_error_log(dml_table_name=>'T2');
exec dbms_errlog.create_error_log(dml_table_name=>'T4');
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';
insert into t1 (x,y,z,dt)
select cust_id, cust_name,txn_amount,dob
from big_table_clust;
commit;
insert into t2 (x,y,z,dt)
select cust_id, cust_name,txn_amount,dob
from big_table_clust
log errors reject limit unlimited;
commit;
insert /*+ append */ into t3 (x,y,z,dt)
select cust_id, cust_name,txn_amount,dob
from big_table_clust;
commit;
insert /*+ append */ into t4 (x,y,z,dt)
select cust_id, cust_name,txn_amount,dob
from big_table_clust
log errors reject limit unlimited;
commit;
declare
cursor c1 is select * from big_table_clust;
type t1 is table of c1%rowtype index by pls_integer;
r1 t1;
l_err_cnt number;
begin
open c1;
loop
fetch c1 bulk collect into r1 limit 1000;
begin
exit when r1.count = 0;
forall i in 1..r1.count save exceptions
insert /* plsql_bulk */ into t5 values r1(i);
exception
when others then
l_err_cnt := l_err_cnt + sql%bulk_exceptions.count;
end;
r1.delete;
end loop;
close c1;
end;
/
commit;
Randomly Sorted Order
create table big_table_non_clust
nologging
as
select rownum as cust_id,
object_name as cust_name,
abs(dbms_random.random) as txn_amount,
to_date('01/01/1900','mm/dd/yyyy')+rownum as dob
from all_objects
order by dbms_random.random;
declare
l_max_id number;
begin
for i in 1..2
loop
select max(cust_id)
into l_max_id
from big_table_non_clust;
insert /*+ append */ into big_table_non_clust (cust_id,cust_name,txn_amount,dob)
select l_max_id+rownum, cust_name,txn_amount,dob
from big_table_non_clust
order by dbms_random.random;
commit;
end loop;
end;
/
create table t1(
x number constraint t1_pk primary key,
y varchar2(30),
z number constraint t1_chk check (z is not null),
dt date
);
create table t2(
x number constraint t2_pk primary key,
y varchar2(30),
z number constraint t2_chk check (z is not null),
dt date
);
create table t3(
x number constraint t3_pk primary key,
y varchar2(30),
z number constraint t3_chk check (z is not null),
dt date
);
create table t4(
x number constraint t4_pk primary key,
y varchar2(30),
z number constraint t4_chk check (z is not null),
dt date
);
create table t5(
x number constraint t5_pk primary key,
y varchar2(30),
z number constraint t5_chk check (z is not null),
dt date
);
exec dbms_errlog.create_error_log(dml_table_name=>'T2');
exec dbms_errlog.create_error_log(dml_table_name=>'T4');
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';
insert into t1 (x,y,z,dt)
select cust_id, cust_name,txn_amount,dob
from big_table_non_clust;
commit;
insert into t2 (x,y,z,dt)
select cust_id, cust_name,txn_amount,dob
from big_table_non_clust
log errors reject limit unlimited;
commit;
insert /*+ append */ into t3 (x,y,z,dt)
select cust_id, cust_name,txn_amount,dob
from big_table_non_clust;
commit;
insert /*+ append */ into t4 (x,y,z,dt)
select cust_id, cust_name,txn_amount,dob
from big_table_non_clust
log errors reject limit unlimited;
commit;
declare
cursor c1 is select * from big_table_non_clust;
type t1 is table of c1%rowtype index by pls_integer;
r1 t1;
l_err_cnt number;
begin
open c1;
loop
fetch c1 bulk collect into r1 limit 1000;
begin
exit when r1.count = 0;
forall i in 1..r1.count save exceptions
insert /* plsql_bulk */ into t5 values r1(i);
exception
when others then
l_err_cnt := l_err_cnt + sql%bulk_exceptions.count;
end;
r1.delete;
end loop;
close c1;
end;
/
commit;
In short – direct path, LOG ERRORS rocks. details below.
| Clusterd Table | CPU | Elapsed | Physical IO | Logical IO | Rows |
| Conventional path loading | 1.21 | 3.55 | 404 | 32306 | 224756 |
| Conventional path loading With Error logging | 6.43 | 28.18 | 13 | 706516 | 224756 |
| Direct path loading | 0.67 | 2.62 | 0 | 15978 | 224756 |
| Direct path loading With Error logging | 1.17 | 14.79 | 0 | 16415 | 224756 |
| Plsql Bulk binding with SAVE Exceptions | 1.48 | 17.09 | 0 | 31714 | 224756 |
| NON - Clusterd Table | CPU | Elapsed | Physical IO | Logical IO | Rows |
| Conventional path loading | 3.75 | 6.68 | 451 | 471078 | 224760 |
| Conventional path loading With Error logging | 6.85 | 34.63 | 4 | 710078 | 224760 |
| Direct path loading | 1.51 | 3.61 | 0 | 15960 | 224760 |
| Direct path loading With Error logging | 1.57 | 3.11 | 0 | 15966 | 224760 |
| Plsql Bulk binding with SAVE Exceptions | 4.1 | 26.37 | 0 | 469459 | 224760 |
No comments:
Post a Comment