FAQ
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..?

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 14, '05 at 9:49a
activeAug 14, '05 at 9:49a
posts1
users1
websiteoracle.com

1 user in discussion

Sami Seerangan: 1 post

People

Translate

site design / logo © 2022 Grokbase