Re: Table locked while rebuilding index

"MK" <hello_at_foo.com> wrote in message
news:b8ro7o$coq11$1_at_ID-174077.news.dfncis.de...
> "MK" <hello_at_foo.com> wrote
>
> [...]
>
> War report, #2
>
> I had to import 1 billion rows into a table. I put INDEXES=Y
> into the IMP par file, however it was difficult to see what was
> Oracle doing. I had several server session making full
> table scan of that table (after the message "1 billion rows
> imported"), presumably to rebuild the index? But why?
> I expected that INDEXES=Y just imports indexes, without
> rebuilding/reprocessing anything? BTW that export file was
> of course also made with INDEXES=Y.
>
> I simply killed off all those sessions and ran a CREATE INDEX...
> script. With PARALLEL option on, but it still takes ages.
> BTW I have PARALLEL 2, but both processors on my
> machine show only 50% CPU usage. Should I put PARALLEL
> 4 or 8 next time?
>
>
>
>

A few remarks:
Oracle doesn't export the actual indexes. Oracle exports the index definitions.
Indexes are always rebuilt during import (which is one of the reasons to use export/import for database reorganizations and the major reason to put indexes in separate tablespaces)

Rather than specifying parallel=2 you would better have specified nologging. Right now your indexes rebuilds are being logged in the redolog. You don't want that because an index rebuild is repeteable.

Personally I'm more or less convinced running parallel query or whatever parallelization with only 2 processors doesn't buy you anything, especially if you didn't stripe your data. There is always one process necessary to coordinate the query slaves: in the end this appears to be slower than without parallelization.

If you want to monitor index progress, you should check for temporary segments being created, especially in the destination tablespace for the index. The index is initially created as a temporary segment, and the temporary segment is renamed as the last step in index creation. You should also bump up sort_area_size.

Regards Received on Thu May 01 2003 - 14:01:06 CDT