Oracle: commits



        

YouTube Video



Setup

create table toto (dummy number);

(8 cpu machine)
8 sessions 
8000 inserts (8000 loops of 1 insert or 1 loop of plsql 8000 inserts)
0 sleep time


Commit Every Insert

insert into toto values ('1');
commit;

Commit Every 50

declare
insert_count integer:=0;
begin
  for i in 1..8000 loop
     insert into foo  values ('1');
     insert_count:=insert_count +1;
     if insert_count > 50 then
        commit;
        insert_count:=0;
      end if;
  end loop;
  commit;
end; 
/


Insert  Bulk

declare
TYPE SS_NUM IS TABLE OF Integer INDEX BY BINARY_INTEGER;
B_SS_NUM SS_NUM;
BEGIN
for i in 1..8000 loop
   B_SS_NUM(i):=1; 
end loop;
FORALL indx IN B_SS_NUM.FIRST .. B_SS_NUM.LAST
INSERT INTO toto ( dummy )
VALUES ( B_SS_NUM(indx) );
COMMIT;
end;
/





Comments