CODEWITHSUNDEEP
oracle-databaseoracle11g
Ali Arda Orhan
Ali Arda Orhan

Reputation: 784

Why increasing parallel degree increases query time

I have a table with 8 million rows.

I created exactly same table without any index,pk,fk and tried to insert all records from table activity to temp_activity.

alter session enable parallel dml;
alter table activity_temp nologging

insert into  temp_activity m
  select /*+ APPEND PARALLEL (i) */
   t.*
    from activity t

My results are

i=1 -> 168 sec
i=2 -> 197 sec
i=3 -> 208 sec
i=10 -> 268 sec
without  /*APPEND PARALEL*/ hint -> +10 min

Question is self explanatory: What is the problem of compiler?

Upvotes: 0

Views: 138

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

select /*+ APPEND PARALLEL (i) */

Makes no sense to me. Why would yo have an APPEND hint in the SELECT?

I think you want to do like this:

alter session enable parallel dml;
insert /*+ append parallel(i) */ into temp_activity select * from activity;

Also, note that parallel is always a direct path, if you go parallel, you will be appending. And that a parallel insert will take an exclusive lock on the table. Because, you cannot read/modify an object after modifying it in parallel.

A good read for you https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:1211797200346279484

And regarding Types of Parallelism, everything is documented. Read http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel003.htm#i1006712

Upvotes: 2

Related Questions