Question Can we optimize the time consumption for full index rebuild?

[pankajpatel23]

Hi ALL,

System Configuration:
IBM AIX 5.3.0.0 64bit, 8 CPU machine
Memory: 58GB
Progress: 10.1C
Database size: 47.9 GB [by dbanalysis report]
152 GB [by promon -> 5. Activity]

Challange: After the D&L , when I'm executing full index rebuild on the database, it is taking 5 hrs to complete this rebuild process. So I was wondering if we can optimize this time ?

I'm executing below command for index rebuild:

proutil $DBNAME -C idxbuild all -TB 31 -TM 32 -B 10000 -SG 64 -threadnum 4 -SS $DLCWRK/indexbuild.srt

Appreciate your valuable inputs on this.

Thanks & Regards,
Pankaj

 

[RealHeavyDude]

OE10.1c is pretty old ( the last patch came out 5 years ago ). You should upgrade in order to benefit from better performance for the index rebuild in general.

Nevertheless there are some things you can do to speed up the index rebuild:

1. Use -T and point it to the fastest, distinct file system you have access to. The i/o for index sorting that needs temporary sort files is one big bottleneck of the index rebuild.
2. You should have you database physically designed so that indexes are in their own storage areas and they should be storage area of type II.

Heavy Regards, RealHeavyDude.

 

[TomBascom]

Upgrade.

There were *major* improvements, order of magnitude type "major", to idxbuild in 10.2B08.

The things that you can do, beyond what you already have, in 10.1C are inconsequential.

 

[Rob Fitzpatrick]

I agree. With a lot of testing and tweaking you can improve a little bit on 5 hours. But given that hardware and that DB and 10.1C, not a lot. All that time spent testing would be better spent on upgrading to 10.2B08 or 11.3.1. Not only will your idxbuild run much faster, your applications will benefit as well from running on a more modern release.