| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Index Rebuild Tuning
Some thoughts:
1.) NOLOGGING is sure to speed things up significantly. 2.) Without nologging, you're sure to generate tons of redo. If you're = in archive log mode, are you sure you're not filling the archive log = area? If you do, the rebuild will silently hang, and take even longer. 3.) Look at V$SESSION_WAIT. What's the rebuild waiting on? If it's = not waiting on direct path read/write, then you're not sorting to disk, = so increasing SORT_AREA_SIZE will not do any good.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Fuad Arshad
Sent: Thursday, August 19, 2004 9:07 AM
To: oracle-l_at_freelists.org
Subject: Index Rebuild Tuning=20
Scenario
1.8 Billion rows table non partitioned.
index key 30 bytes=20
parallel 8 on index parallel 2 on table=20
Oracle 9.2.0.4=20
Workarea_size set to manual sort_area_size set to 100M
=20
Tablespace locally managed non assm
=20
The table is not partitioned because of vendor requirement=20
the rebuild is taking 12 hrs and is still runing .
=20
=20
need help tuning this rebuidl
it is not an online rebuild.
we are doing migration testing on a test box. even though production is =
2wice as faster=20
thisis not acceptable with the users.
need advise.
the index size is around 57G
=20
=20
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Aug 19 2004 - 09:46:21 CDT
 |
 |