Dml Error logging in 10gR2

Taking a deeper look at the LOG ERRORS option for DML in 10g Release 2. Ok, so I’ve done the insert component of the testing and found something Interesting.

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