Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle Index Rebuild Performence Issue

Posted on 2011-05-10
5
Medium Priority
?
529 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:n78298
  • 2
  • 2
5 Comments
 
LVL 7

Expert Comment

by:MrNed
ID: 35735134
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
 

Accepted Solution

by:
n78298 earned 0 total points
ID: 35735224
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
 
LVL 7

Expert Comment

by:MrNed
ID: 35735317
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
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 35740770
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
 

Author Closing Comment

by:n78298
ID: 35829985
Closing
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Why Shell Scripting? Shell scripting is a powerful method of accessing UNIX systems and it is very flexible. Shell scripts are required when we want to execute a sequence of commands in Unix flavored operating systems. “Shell” is the command line i…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month11 days, 13 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question