One large insert Vs multiple smaller inserts
- From: Sami Seerangan <dba.orcl@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 14 Aug 2005 10:47:03 -0400
We had this issue in production during huge data migration.
Environment:-
Oracle 8.1.7.3 <http://8.1.7.3> Sun 2.6 - 2 Node OPS. 12 CPU on each box.
The table in question here involved in M-M Oracle replication.
There is no other activity on both "12Million_Rec_table" and
"1Million_Rec_table
" other than my process.
Process-1
=========
exec dbms_reputil.replication_off();
commit;
insert /*+ append parallel(t,12) */ into 12Million_Rec_table t
select /*+ parallel */ * from 1Million_Rec_table;
Process-2
=========
exec dbms_reputil.replication_off();
commit;
--for loop with diff predicate value
loop
insert /*+ append parallel(t,12) */ into 12Million_Rec_table
select /*+ parallel */ * from 1Million_Rec_table
where predicate=&1; -- there is an Index on Predicate column
end loop
12Million_Rec_table has only one primary key index. There is no other
constraints or LOB columns on this table. For some reason it didn't use
direct-path insert, don't know why.
Process-2 executed N times in a sequence.Basically Process-1 is one large
transaction compared to Process-2 (which has N number of transactions in a
loop).
In terms of execution time the Process-1 took longer than Process-2.
Process-1
was running for more than
2 hrs without completion so we stopped it. Process-2 with smaller chunk of
records completed very fast.
When Process-1 started the USED_UBLK value in v$rollstat increased rapidly
then
slowed down gradually. I have seen
this behaviour many times while running Process-1.
My questions are
1)Why Process-1 is slower than Process-2. [We didn't have any rollback
segment
issue while running both Process-1 and Process-2]
2)Is there any performnce issue if the rollback segment goes beyond some
size,
say 200M or something..?
Other related posts:
- » One large insert Vs multiple smaller inserts
