This content has been marked as final. Show 7 replies
Why are you rebuilding indexes in the first place? Normally, indexes in Oracle do not require rebuilds.
Assuming that the rebuild is necessary in the first place, do you have an AWR/ Statspack report or a session trace that tells you what the top wait events are?
1. In 10g there is no need to compute statistics for indexes since this is done automatically with creation.
2. As Justin already mentioned tracing might help you to find the reason
3. I experienced situations in which parallel processing slowed down DB activity massively, so try sequential processing.
Finally: In my 10g DB index creation is extremely fast: My table has 10 rows, my server has solid state devices and 16 CPUs. I can't watch as fast as the DB rebuilds the index. Do you notice what I want to say? :-)
If not, you'll find some hints here http://forums.oracle.com/forums/ann.jspa?annID=718
trace on that session to find out the problem...
Perhaps your table and index busy and you wait.
*** you can choose instead to coalesce the index. Coalescing an index is an online operation.
Anyway You don't need to use "compute statistics"... , that can make you have to use more time...
I am using a syntax like:alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME online compute statistics parallel;
1. Why did are rebuilding the index.
2. You could have used
alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME online parallel N; ---> where n is the CPU_COUNT parameter
3. Whats more going on the database.Check if any lock on the table whose index it is.
Anand, AFAIK the parallel option should leave at least one CPU for coordination of the parallel slave processes. Nevertheless, the DB is also capable to find out the possible degree of parallelism.
Edited by: erka on 14.03.2009 21:17
Yes, i forget to write :)
N= CPU_COUNT -1
user8294047 wrote:In addition to the advices you've already got regarding index rebuilding in general:
We are trying to build indexes in Oracle 10g and its extremely slow. Any pointers to find out what the problem is and then fixing it?
I am using a syntax like:
alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME online compute statistics parallel;
Should online and parallel together be an issue? (I don't necessarily need to do online, I just removed online and started again, but not sure how it will go)
If you're using the ONLINE rebuild option it might take a while if there are active transactions on the table, since it waits until no transactions are active to start the rebuild process, the same applies to the completion of the ONLINE operation.
Of course, you should get an "ORA-00054 resource busy" error when this is the case when using the normal (offline) rebuild option without the ONLINE keyword.
Oracle related stuff blog:
SQLTools++ for Oracle (Open source Oracle GUI for Windows):