When using parallelization with higher values for S_A_S, keep in mind that *each* PQ
slave will
potentially use that much memory for sorting. Should this sorting use disk, it would
cause even
more direct i/o to temp tablespace. Parallelization can very easily paralyze you
system ;)
Also, S_A_S and S_A_R_S can be set at session level. No need to bounce the instance.
- Kirti
- zhu chao <[EMAIL PROTECTED]> wrote:
> 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: Kirtikumar Deshpande
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 - 09:51:03 CDT
