12 Hour index rebuild??

[Mr.Hanky]

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

[pando]

have you set sort_area_size to a big size, for example 64MB?
have you used nolog and parallel option?

[marist89]

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.

[Mr.Hanky]

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

[pando]

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

[Mr.Hanky]

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

[oravijay]

Hi,

You can also use NOSORT option.

Vijay.

[AndyEPS]

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

[Mr.Hanky]

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

[Mr.Hanky]

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