Re: tunning an index built

From: Mladen Gogala <mgogala_at_adelphia.net>
Date: Tue, 08 Jul 2003 15:15:53 -0700
Message-ID: <F001.005C3E81.20030708150932@fatcity.com>

DB Cache doesn't help you. IO waits mean that oracle processes are waiting to complete I/O requests. The I/O waits usually come with arguments like P1TEXT,P1,P2TEXT,P2,P3TEXT and P3 which can help you in locating file and block that the oracle processes are waiting for. Move those files and blocks to separate I/O devices, so that I/O can be processed in parallel. When creating so big indexes you need not only CPU power, you need I/O throughput. Paging is to be expected in order for oracle processes to allocate that big sort_area_size. Try tuning you memory and disk drives by using sar, iostat, vmstat, top and/or glance (Glance is the best monitor I've seen so far). You may need to move your temporary tablespace to alternative location. Also, be sure to fix you OS paging/swapping parameters. If possible, use direct IO to prevent double buffering and if not, limit OS buffer cache to a very small portion of memory. Paging and caching is usually a consequence of dynamic buffer cache. Parameter name should be something like NBUF.

On 2003.07.08 17:29, 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).
--

Mladen Gogala
Oracle DBA
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Mladen Gogala
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