Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Index Rebuild Tuning

RE: Index Rebuild Tuning

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Thu, 19 Aug 2004 10:39:27 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09660D38@bosmail00.bos.il.pqe>


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



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
-----------------------------------------------------------------
----------------------------------------------------------------
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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US