Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
/
share|improve this question

1 Answer 1

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:

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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