| 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
Ah. Then unload the thing in the order you need to index it, reload it in
order, and skip all the drama (or at least a lot of it) using that option on
the index creation. Of course this only works for 1 or more indexes sharing
the order purely.
What's yer block size? For *that* many rows index depth probably is an issue, plus, if most all those rows are mature, you might want to reload them as dense as possible. If everything is mature except maybe the most recent million or so, you might want to load all but those dense and ordered, make the index(es), change your pctfree to accommodate row length change, and then load the immature rows. Extra density of mature rows pays off forever, and you've got a nearly free opportunity to gain that advantage (unless you have no such thing as a mature row, in which case ignore all this, but don't forget - a row for this purpose is considered mature when it stops changing in length, so some rows are born mature.) If your application engages in hot waves of updates in the same order as the most significant index(es) order, you'll have to figure out whether there is a hot block downside to "densification."
Real physical i/o considerations are probably important at your size. How many times will you have to do this, and over how long a period? An excellent time to run a trial on, hmm, 120 G of SSD for the index and temp space.
How many cpus yas got? Probably if your parallel degree ends up more than 2 times the number of cpus, you're just trying to teach a pig to sing.
With no idea of your disk farm, I've got no idea whether you might want to try to isolate real i/o dependencies (anti-SAME) and try to make the temp and index destinations effectively single user operations. If this table overruns your read cache, but you have enough independent units of i/o capacity, then build multiple indexes into different tablespaces on independent i/o thusly:
The second index will then have a chance take advantage of the blocks being in the buffer that the first index caused to be read.
I wouldn't do this with more parallelism than you have independent i/o chains for to allocate multiple temps, plus remember that 2 jobs per cpu thingy unless you're on SSD where that might get tweaked up a bit. Don't watch the pot much, and try to keep the behavior of the system from each index create job as close to single user as possible.
How fast does your current methodology complete with 1.8 Million rows? 18 Million? 180 Million? (probably this all will be non-linear but it might give you an idea of time to complete if you chart a few points. You'll know when to skip or kill the next test when it gets too long. Then take a step back and pick some reasonable intermediate points against which to chart. Even if you only do the 1.8 million row test, you've got a pretty good guarantee that without significant change it will take at least 1000 times as long. (Almost certainly worse, what with n log n, depth, and whole buncha other stuff.)
Now, how does the curve look versus your allowed window? 12 hours and still running does not generate much progress information.
good luck. I bet others have a lot to add.
mwf
-----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 11:29 AM
To: oracle-l_at_freelists.org
Subject: Re: Index Rebuild Tuning
i knew this question was coming.
its not a rebuild per say. we're doing a full data migration which includes
dropping off indexes and then after completing column and data migration
recreating the index.
as far as logging database is archivelog but nologging on table level and on
the index.
Jared Still <jkstill_at_cybcon.com> wrote:
Looks like I get to be the first to ask:
Why are you rebuilding the index?
Jared
On Thu, 2004-08-19 at 06:07, Fuad Arshad wrote:
> Scenario
> 1.8 Billion rows table non partitioned.
> index key 30 bytes
> parallel 8 on index parallel 2 on table
> Oracle 9.2.0.4
> Workarea_size set to manual sort_area_size set to 100M
>
> Tablespace locally managed non assm
>
> The table is not partitioned because of vendor requirement
> the rebuild is taking 12 hrs and is still runing .
>
>
> 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
> thisis not acceptable with the users.
> need advise.
> the index size is around 57G
-- 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 - 11:25:07 CDT
 |
 |