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