We had this issue in production during huge data migration.
Oracle 184.108.40.206 <http://220.127.116.11> 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
" other than my process.
insert /*+ append parallel(t,12) */ into 12Million_Rec_table t
select /*+ parallel */ * from 1Million_Rec_table;
--for loop with diff predicate value
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
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
In terms of execution time the Process-1 took longer than Process-2.
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
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
issue while running both Process-1 and Process-2]
2)Is there any performnce issue if the rollback segment goes beyond some
say 200M or something..?