Re: tunning an index built

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