This discussion is archived
7 Replies Latest reply: Mar 15, 2009 6:16 AM by Randolf Geist RSS

Index rebuilding slow in Oracle 10g

689431 Newbie
Currently Being Moderated
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)
  • 1. Re: Index rebuilding slow in Oracle 10g
    Justin Cave Oracle ACE
    Currently Being Moderated
    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?

    Justin
  • 2. Re: Index rebuilding slow in Oracle 10g
    492514 Journeyer
    Currently Being Moderated
    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
  • 3. Re: Index rebuilding slow in Oracle 10g
    Surachart Opun Oracle ACE
    Currently Being Moderated
    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...
  • 4. Re: Index rebuilding slow in Oracle 10g
    Anand... Guru
    Currently Being Moderated
    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
  • 5. Re: Index rebuilding slow in Oracle 10g
    492514 Journeyer
    Currently Being Moderated
    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
  • 6. Re: Index rebuilding slow in Oracle 10g
    Anand... Guru
    Currently Being Moderated
    Yes, i forget to write :)

    N= CPU_COUNT -1

    Anand
  • 7. Re: Index rebuilding slow in Oracle 10g
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    user8294047 wrote:
    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)
    In addition to the advices you've already got regarding index rebuilding in general:

    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.

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points