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: tunning an index built

Re: tunning an index built

From: Mark Richard <mrichard_at_transurban.com.au>
Date: Thu, 18 Sep 2003 16:49:44 -0800
Message-ID: <F001.005D06AD.20030918164944@fatcity.com>

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

Original text of this message

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