parallel nologging is not faster.
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: parallel nologging is not faster.

  1. #1
    Join Date
    Jun 2001
    Posts
    193
    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?
    guru is on the way!!!!

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Is the parallel option had been enabled on the server?

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Apr 2001
    Posts
    219
    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.
    ______________________
    Applications come and go,
    but the data remains!

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You may have underlying tuning issues that multiple processes just exagerates.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Just a minor point, but DEGREE 4 is very small. Try upping it to 20 or 40.

    - Chris

  6. #6
    Join Date
    Jun 2001
    Posts
    193
    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]
    guru is on the way!!!!

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Do you have I/O contention? My bet is that you are building the index in one tablespace that is getting pounded.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I think he/she is concerned about the index rebuild and not the insert.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    Jun 2001
    Posts
    193
    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 ?
    guru is on the way!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width