Oracle Index Rebuild Performence Issue

We have an application where we rebuild indexes. The underlying table is having 25 partitions and there are 8 indexes on each partition. This effectively means that script has 200 indexes to re-build.  The code which we are using is-

ALTER INDEX <Index Name> NOLOGGING;
ALTER INDEX <Index Name> REBUILD PARTITION TB_1538418432_425 PARALLEL 5;
ALTER INDEX <Index Name> LOGGING;

Open in new window


(Done for all 200 indexes sequentially)

The above script is taking around 5-6 hours to complete.  The average partition size is around 3.5-4 GB.

Could someone have a look and suggest improving the performance of above Index rebuilding process.
n78298Asked:
Who is Participating?
 
n78298Author Commented:
Functionally we need to rebuild every index.  The partitions are exchanged with Temp tables just before this step.

We have already used NOLOGGING before rebuild and used parallelism .

Are there any further suggestions whihc can be implemented.
0
 
MrNedCommented:
Logging and parallelism are the big wins. How many cores do you have available?

Do you really need to rebuild every index - can you just target some of them?
0
 
MrNedCommented:
I asked about the number of cores, wondering if you could increase from 5 to something else. Or run multiple parallel index creations at once as they sometimes don't run every available core the whole time they're running.

Give it as much PGA memory as you can so the sorting happens in memory where possible. Did you monitor the rebuild - is it filling TEMP tablespace? Is the bottleneck in reading the table or writing the index?
0
 
Franck PachotCommented:
Hi,

If your indexes are local, they should not become unusable.
If your indexes are global then you can use 'update global index'.

Regards,
Franck.
0
 
n78298Author Commented:
Closing
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.