12 Hour index rebuild??
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: 12 Hour index rebuild??

  1. #1
    Join Date
    Jan 2001
    Posts
    3,131
    Oracle 8.1.7.0.0
    OS Unix HP 11.00

    Hey Oracle peeps;

    I am in the process of rebuilding one index on a table that has about 50 million records. The index is a primary key and only has one column assigned to it. I have created a seperate tablespace for the index but when I look at longops in DBA Studio it is telling me that it will be another 9 1/2 hours!!??

    Any reccomendations? I have allocated extra space in the temp tablespace but this is just dripping slow.

    MH
    I remember when this place was cool.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    have you set sort_area_size to a big size, for example 64MB?
    have you used nolog and parallel option?

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Are you rebuilding in parallel? Using nologging?

    It takes me about 3 1/2 hours to rebuild an index on a table with 140M rows using parallel 4.
    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."

  4. #4
    Join Date
    Jan 2001
    Posts
    3,131
    I had logging set, so I just cancelled it and re-ran with nologging. I can not edit the sort area size due to politics, that would require a re-boot as well wouldn't it?

    If this takes too long I'll try the parallel option, never used it before. Any tips?

    MH
    I remember when this place was cool.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you dont have to reboot to change sort_area_size, you simply do

    alter session set sort_area_size=64M;

    if that was the politics and if i was you I would really have a serious talk with your senior dbas

    use parallel if you have more than one CPUs, I found the adequate degree is 2*no of CPUs

  6. #6
    Join Date
    Jan 2001
    Posts
    3,131

    Thumbs up

    Thanks Guys!!

    Pando, I looked it up right after I posted, I changed my sort_area_size, set nologging, and used a paralle clause.

    Let's see what happens.

    Thanks again!

    Jeff is still my hero!!

    MH
    I remember when this place was cool.

  7. #7
    Join Date
    Mar 2002
    Posts
    301
    Hi,

    You can also use NOSORT option.

    Vijay.
    Say No To Plastics

  8. #8
    Join Date
    Apr 2002
    Posts
    14
    The suggestions others have made should make the rebuild faster. Just be aware of a gotcha with the parallel clause.

    1. After the rebuild do an alter index noparallel, to prevent the CBO using parallel options whenever this index is used in SQL (unless you want it to of course :-) ). If you don't you might find all your SQL execution plans on this table going crazy. If you use RBO then you MUST do this or things get very messy (been there, worn the Tshirt, caught the flak :-) ).

    2. To get the best from parallel query option (PQO), you need to keep the reading and writing off the same disks. Ideally both should be on striped disk, or at the very least multiple concatenated disk. If not the the disk holding the read or write data (or both) becomes saturated long before the CPU. On a RAID-5 disk array I've seen PQO make no difference ( I'm guessing that your table is probably big enough that it's on multiple disks so this won't be an issue).

    PQO is very powerful. I've seen a 55 million row table PK index build take just 5 minutes !!! on a 12 disk stripe on Sequent NumaQ with 12 procs.

    Note the PQO also helps the sorting as this happens in smaller chunks that are then merged - much easier than sorting all the data.


    Andy

  9. #9
    Join Date
    Jan 2001
    Posts
    3,131
    Guys;
    Thanks for all the responses to this, it has been a learning experience. Yesterday I altered the session an increased the sort area size and created the indexes with the parallel clause. It took about 20 minutes.

    I noticed that the storage for the indexes is listed as parallel, the user rep for the application said that it is still slow. So, I just changed the parallel clause back to none, I am also re-analyzing, I assume this is a good idea after altering the index? Should I re-analyze the table as well?

    Any other suggestions?
    Thanks again.
    MH
    I remember when this place was cool.

  10. #10
    Join Date
    Jan 2001
    Posts
    3,131

    Question

    Here is another question.
    I heard a rumor that when you re-build an index with parallel it can cause fragmentation, is this trrue?

    I just ran an analyze on the PK_INDEX that I re-built yesterday and it has a blevel of 3, why is that?

    MH
    I remember when this place was cool.

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