One large insert Vs multiple smaller inserts

From: Sami Seerangan <dba.orcl_at_gmail.com>
Date: Sun, 14 Aug 2005 10:47:03 -0400
Message-ID: <f09dd6280508140747311cb0ca@mail.gmail.com>

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

--

http://www.freelists.org/webpage/oracle-l Received on Sun Aug 14 2005 - 09:49:04 CDT