I didn't notice the original message, so I'll add to Craig's comments. I
would look at the amount of physical RAM available to yourself. If the
machine has plenty of RAM then allocate it to the sort_area_size. We often
set sort_area_size to 500M for building large indexes (our machine has 6GB
so we can afford to give 500M to a single sort without any major concern
normally). Given that your index is several GB in size it will still have
to move the sort out to disk, but at least it can go further before it hits
the disk.
I guess the other things to look at might be some I/O contention. Is your
temp tablespace on the same disk as the table? Can you move it away if so?
Regards,
Mark.
Craig Munday
<[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
net.au> cc:
Sent by: Subject: Re: tunning an index built
[EMAIL PROTECTED]
.com
19/09/2003 00:39
Please respond to
ORACLE-L
Hi,
How many CPUs do you have on your machine? I assume not 32 - perhaps
reducing the degree of parallelism might help (less is sometimes more).
How much memory do you have on your machine? I suggest fixing your virtual
memory paging problem first - do this by resizing areas like the buffer
cache and sort_area_size (did you set sort_area_size in the init.ora?
remember that every dedicated server will allocate the sort_area_size in
the PGA - perhaps this might have something to do with the virtual memory
paging).
Also, if on the off chance that you know the rows are already sorted within
the table - that is, you have just completed a load or something similar,
you could use the NOSORT option to avoid Oracle doing the sort and using
the temporary segment. The documentation says that an error will be thrown
if the tables are not sorted on the indexed column. I haven't tried this
myself but would be interested in hearing any feedback if you can take
advantage of it.
Also if you change your sort_area_size you also need to ensure that your
extent sizes in your temporary segments are a multiple of the
sort_area_size you specified.
Have you allocated enough space for your index prior to doing the build?
Regards,
Craig.
At 01:29 PM 8/07/2003 -0800, Gurelei wrote:
>Hi.
>
>I'm trying to tune an index build. The table currently
>has about 65mil rows and I'm building a unique index,
>which takes about 55min to finish. The table size is
>about 3.4G, index is about the same size. I have tried
>different degrees of parallelism (up to 32), nologging
>is set in the create index script as well as on the
>tablespace. I noticed a lot of i/o waits during the
>buid and a lot of paging to and from filesystem, the
>paging area however appears to be unused. when I do
>lsps -a, it only shows 1% usage. What should be my
>next move? What should I look at? i have increased
>db_cache to 800M, sort area to 50M
>
>thanks
>
>Gene
>
>__________________________________
>Do you Yahoo!?
>SBC Yahoo! DSL - Now only $29.95 per month!
>http://sbc.yahoo.com
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Gurelei
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Craig Munday
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for delivery of
the message to such person), you may not copy or deliver this message to anyone.
In such a case, you should destroy this message and kindly notify the sender by reply
e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999.
Please advise immediately if you or your employer does not consent to Internet e-mail
for messages of this kind.
Opinions, conclusions and other information in this message that do not relate to the
official business of Transurban Infrastructure Developments Limited and CityLink
Melbourne Limited shall be understood as neither given nor endorsed by them.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mark Richard
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
