| 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: tunning an index built
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
<cmunday_at_bigpond. To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
net.au> cc:
Sent by: Subject: Re: tunning an index built
ml-errors_at_fatcity
.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: gurelei_at_yahoo.com
>
>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: ListGuru_at_fatcity.com (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: cmunday_at_bigpond.net.au 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: ListGuru_at_fatcity.com (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: mrichard_at_transurban.com.au 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: ListGuru_at_fatcity.com (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).Received on Thu Sep 18 2003 - 19:49:44 CDT
 |
 |