Not seeing any benefits of parallel execution when parallel clause is used

oracleoracle-11g-r2parallelism

I have these two methods of creating a table and populating with rows. The first one uses a
parallel (degree 3) clause, so I expected the inserts to be much faster. But I timed the time taken for a) and b) and it turns out to be almost the same. There is no indication that method a) is faster, in fact in some of the runs, method b) was faster.

I thought that parallel clause would perform the inserts in parallel and hence be much faster. What am I missing here?

a)

create table TAB1
(col0 number not null,
 col1 number,
 col2 number,
 col3 varchar2(25)) parallel (degree 3)
 storage (initial 100K next 100K pctincrease 0);


begin
 for i in 1..300000 loop
  insert into TAB1 values(i-1,i,300000-i,null);
 end loop;
 commit;
end;
/

b)

create table TAB2
(col0 number not null,
 col1 number,
 col2 number,
 col3 varchar2(25))
 storage (initial 100K next 100K pctincrease 0);


begin
 for i in 1..300000 loop
  insert into TAB2 values(i-1,i,300000-i,null);
 end loop;
 commit;
end;
/

Best Answer

You're not seeing the benefits of parallel execution because both insert methods use single-row inserts, hence parallel does not kick in !

Parallel execution is only available for bulk operations. Lots of tiny operations doesn't qualify as a set operation. You're inserting rows one by one here, you should try it with a bulk operation (INSERT /*+APPEND*/ INTO SELECT, CREATE TABLE AS SELECT ...) and see if you can achieve some performance improvement.

You can tell when Oracle does a parallel execution because the explain plan will display parallel operations.

For further reading:

Related Question