Re: tunning an index built

From: zhu chao <chao_ping_at_vip.163.com>
Date: Wed, 09 Jul 2003 01:01:02 -0700
Message-ID: <F001.005C3F0E.20030709005425@fatcity.com>

Hi,

        First of all, you should use  nologging. This helps a lot.
        Second, when using parallel clause, the sort_area_size of the

ora_pxxx size is not the sort_area_size of your session, it is the instance's default size, when it is started. I have verified it.

        So if you want to make parallel works better, you can increase the sort_area_size of your instance and bounce the instance.

        Also try to increase multiblock_read_count, pay attention to your cpu number and max ora_pxxx number.

Regards
zhu chao
msn:[EMAIL PROTECTED]
www.cnoug.org
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, July 09, 2003 7:09 AM

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: zhu chao
  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).

Received on Wed Jul 09 2003 - 03:01:02 CDT