parallel nologging is not faster.

[beginner]

i have 8 cpu. i am the only user.
create table p ( p int);
begin
for i in 1..1000000 loop
insert into p values (i);
end loop;
end;
/

set timing on
create index myindex on p(p);

Elapsed: 00:00:43.55

drop index myindex;
create index myindex on p(p) parallel(degree 4) nologging;

Elapsed: 00:00:53.19

why?

[sambavan]

Is the parallel option had been enabled on the server?

Sam

[Zaggy]

I was thinking about this and here is a possible reason it is slower. The data most likely still resides within memory, so you take a hit for the overhead of having multiple servers trying to come together with a final results. Now, if you had a much larger set of information and it was primarily sitting on disk, it would be much faster parallel than serial. So, having multiple processes would speed up the build process by overcoming disk I/O.

[marist89]

You may have underlying tuning issues that multiple processes just exagerates.

[chrisrlong]

Just a minor point, but DEGREE 4 is very small. Try upping it to 20 or 40.

- Chris

[beginner]

thank you all for reply.

however, it didn't help me out.
1. i don't think 1000000 is small .
2. what do i need to do in order to use parallel
anything need to setupt first?
3. since i only have 8 cpu, don't you think setup degree to 20 is too big( i tried 20 degree, it didn't speed things up).



[Edited by beginner on 08-21-2001 at 09:19 PM]

[marist89]

Do you have I/O contention? My bet is that you are building the index in one tablespace that is getting pounded.

[tamilselvan]

Parallel and NOLOGGING are useful when you do bulk copy such as SQL*LOADER or IMPORT utilities.
If you are inserting a row using PL/SQL block, they won't be of much help.

[marist89]

I think he/she is concerned about the index rebuild and not the insert.

[beginner]

jeff:

i did another test today.
create index myindex on p(p) takes 39.17
create index myindex on p(p) parallel(degree 8) nologging
takes 1:01.70

while i do the test.
i opened another window
and issue iostat -xn 5
%b is less than 50 all the time.

do u have idea what's wrong ?