2

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;
/

1 Answer 1

7

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.