Re: Re: max parallel query

Netapp is NAS afaik, that means a bunch of disks used over fast network. SAN is a bunch of disks used over SCSI or Fibre interface (EMC Clariion for example). I tend to trust and appreciate SAN more than NAS, but NAS can be more cost effective in small-to medium environments.

What I meant, is that if you have let say 16 disks striped & mirrored with SAME, it doesn't improve your performance if you split your stripe to two paritions (mount point) instead of just one partition.

Async IO basically enables Oracle processes to deal with other useful stuff when waiting on writing or reading data to/from disk. Also, operating system kernel can often make some IO optimizations in case of async IO.

Read more about it:
http://www.ixora.com.au/tips/use_asynchronous_io.htm

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, September 19, 2003 2:29 AM

> i dont manage the netapp and am not a hardware person. could you explain a
> little better? Is netapp similiar to SAN?
>
> what is asynch I/O?
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, September 18, 2003 6:24 PM
>
>
> > Ryan
> > You are probably bottlenecking on the NetApp. Probably your network
> link
> > to it. If you have some "regular" (a.k.a. direct attached) disk
available,
> > consider using it for your redo logs.
> >
> > Dennis Williams
> > DBA, 80%OCP, 100% DBA
> > Lifetouch, Inc.
> > dwilliams_at_lifetouch.com
> >
> >
> > -----Original Message-----
> > Sent: Thursday, September 18, 2003 4:45 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > hmmm... when i run statspack during a big load. most of my waits are
from
> > redo log waits and read from a staging datafile. we have all of our
> > datafiles on the same I/O mount. We are using a Network Appliance back
end
> > with asynch I/O.
> >
> > are you telling me that putting these files on seperate mount points
will
> > have no effect? I dont quite follow asynch I/O. I dont have much of a
hard
> > ware OS background.
> >
> > I cant find any docs or articles on how to tune Parallel Operations. All
I
> > see are basic syntax. Do you know of any?
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Thursday, September 18, 2003 5:24 PM
> >
> >
> > > If you want rules of thumb, then take CPU_COUNT+1 up to CPU_COUNT *2.
> > >
> > > I think LIO & PIO ratio is irrelevant, the most important is whether
you
> > are
> > > able to construct optimal parallel execution plan, e.g. avoid
excessive
> > > parallel slave messaging & waiting. This is mostly design and SQL
issue.
> > >
> > > The number mount points is irrelevant novadays as well IMHO,
especially
> > when
> > > you're using async IO, SANs & SAME like architecture. The only thing
> what
> > > comes into my mind where splitting into mount points (thus different
> file
> > > systems) helps performance wise, is that with more file systems you
got
> > more
> > > file system locks and you can spread contention for these locks that
> way.
> > >
> > > Tanel.
> > >
> > > ----- Original Message -----
> > > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > > Sent: Thursday, September 18, 2003 9:34 PM
> > >
> > >
> > > > i know there are no magic formulas, but im hoping for something
better
> > > than trial and error. i would assume that parallel query helps most
> when:
> > > >
> > > > 1. are doing work off of multiple mount points.
> > > > 2. Have alot more LIOs to perform than PIOs(such as sorts).
> > > >
> > > > am i close on this?
> > > > >
> > > > > From: "M Rafiq" <rafiq9857_at_hotmail.com>
> > > > > Date: 2003/09/18 Thu PM 01:34:44 EDT
> > > > > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > > > > Subject: Re: max parallel query
> > > > >
> > > > > Tanel,
> > > > > You are right. There is a param parallel_automatic(I don't
remember
> > > exact
> > > > > name as I don't have access to database) which should be set to
true
> > and
> > > let
> > > > > system decide how many pq slave to be used. For
max_parallel_server
> > > default
> > > > > is 5 and can be set to higher number which memory and cpu can
> > handle.
> > > It
> > > > > is faster because it uses all system resources at a given time.
> > > > >
> > > > > Rarallel process can be used for large batch jobs during off-peak
> > time.
> > > > >
> > > > > Regards
> > > > > Rafiq
> > > > >
> > > > >
> > > > > Reply-To: ORACLE-L_at_fatcity.com
> > > > > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > > > > Date: Thu, 18 Sep 2003 08:39:44 -0800
> > > > >
> > > > > There is no simple magic formula how many parallel slaves you
should
> > > have.
> > > > > It all depends on your CPU and IO utilization. Yes, utilization,
> > because
> > > if
> > > > > your CPUs or IO are running at their limits already, you won't
> benefit
> > > from
> > > > > parallel execution at all. In fact PX may make the situation even
> > worse,
> > > > > because it's designed to give you the results the fastest way, not
> the
> > > most
> > > > > efficient way.
> > > > >
> > > > > Tanel.
> > > > >
> > > > > ----- Original Message -----
> > > > > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > > > > Sent: Thursday, September 18, 2003 6:15 PM
> > > > >
> > > > >
> > > > > > We run multiple instances on the same server. My understanding
is
> > > > > max_parallel_servers should be set to 4 x CPU. We have 4 CPUs
which
> > > means
> > > > > 16.
> > > > > >
> > > > > > however, does this take into account multiple instances on the
> same
> > > > > server?
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > > > > --
> > > > > > Author: <rgaffuri_at_cox.net
> > > > > > INET: rgaffuri_at_cox.net
> > > > > >
> > > > > > 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: Tanel Poder
> > > > > INET: tanel.poder.003_at_mail.ee
> > > > >
> > > > > 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).
> > > > >
> > > > > _________________________________________________________________
> > > > > Send and receive larger attachments with Hotmail Extra Storage.
> > > > > http://join.msn.com/?PAGE=features/es
> > > > >
> > > > > --
> > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > > > --
> > > > > Author: M Rafiq
> > > > > INET: rafiq9857_at_hotmail.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: <rgaffuri_at_cox.net
> > > > INET: rgaffuri_at_cox.net
> > > >
> > > > 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: Tanel Poder
> > > INET: tanel.poder.003_at_mail.ee
> > >
> > > 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: Ryan
> > INET: rgaffuri_at_cox.net
> >
> > 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: DENNIS WILLIAMS
> > INET: DWILLIAMS_at_LIFETOUCH.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: Ryan
> INET: rgaffuri_at_cox.net
>
> 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: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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