Index Rebuilding on Huge Tables

user585870

Dear All,

1. we are in process of rebuilding an index on table A

2. Insert operation on this table A is huge. 24*7 insertion is going on.

3. During the rebuild process the insertion process in very slow and gives some ORA-7445 Error. So right now we are doing the index rebuild by stopping the insert operation.

4. We want to perform the index rebuild process with out stopping the batch jobs.

if anywork around is available,kindly share.

Thanks in Advance

MsJ

Online index features:

ALTER INDEX REBUILD ONLINE;

DMLs are allowed on the base table
It is comparatively Slow
Base table is referred for the new index
Base table is locked in shared mode and DDLs are not possible
Intermediate table stores the data changes in the base table, during the index rebuild to update the new index later

Offline Index Rebuild Features:

ALTER INDEX REBUILD; (Default)

Does not refer the base table and the base table is exclusively locked
New index is created from the old index
No DML and DDL possible on the base table
Comparatively faster

user585870

Hi,

Thank you for the reply.

We tried the Alter Rebuild index Online, the same issues were happening. we are forced to stop the batch job.

Number of rows in the table is 982764476

MsJ

Use parallel to run faster.

Alter index scott.IDX_DTT_EMP rebuild online PARALLEL 10;

Pavan Kumar

Hi,

ORA-7445 - refers to so many things, which we need to consider. Try to provide the exact error informaiton. AFAIK, usually it will be OS issues, bugs and parameters sizing Issues. As you stated that Index re-build is carried out online,did you crossed check temporary tabelspace size and sga sizing is appropriate or not. Which impacts the performance of re-building.

HTH

- Pavan Kumar N
- ORACLE OCP - 9i/10g
https://www.oracleinternals.blogspot.com

Rajesh Lathwal

What is the Oracle version here ?

also please paste the lines from alert file stating the ORA 7445 error..

There is also a bug related to this... and this is for 10G database.

Bug 6767655 Dump [kauxs_do_journal] from concurrent DML while rebuilding index ONLINE
ORA-7445 (kauxs_do_journal) [ID 756799.1]


Regards
Rajesh

user585870

Hi All,


Version Info:
Oracle 10.2.0.3 - 64 bit 2 node RAC system on HP-UX B.11.31 U ia64

Even after using the parallel cluase also the same issue is happening.

Right Now we dont have the exact error information. This happened one year back and after that we are rebuiliding after stopping the batch jobs. this will works fine.

Rajesh Lathwal

Hi,

Without error info, it will be difficult to troubleshoot.

Rgds
Rajesh

user585870

Hi Rajesh,

I am searching in my archives. will update you soon