parallel nologging is not faster.

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?

Is the parallel option had been enabled on the server?

Sam

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.

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

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

- Chris

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]

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

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.

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

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 ?

Do you have PARALLEL_AUTOMATIC_TUNING set to true in init.ora?

- Chris

Change the init.ora file as given below:
# Set up Parallel Query Parameters
optimizer_percent_parallel = 50
parallel_min_percent = 50
parallel_min_servers = 8
parallel_max_servers = 10

Shutdown and Startup the instance.

Also remember that the underlying table on which you create index must be already created with parallel degree.
I have tested many times that NOLOGGING mode on table or index speeds up creation process.

The rule of parallelism is:
Parallel Query Processes = Min of number of CPUs or the number of disks on which the datafile spreads which ever is less.

Even though you have 8 CPU, but the Index data file is NOT striped, then you don't see any improvement on parallel query.

The master speaks... http://asktom.oracle.com/pls/ask/f?p...0_P8_DISPLAYID,F4950_P8_CRITERIA:1231781710382,

Hmm, sounds similar to what I said.