Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re: max parallel query

Re: Re: max parallel query

From: Ryan <rgaffuri_at_cox.net>
Date: Thu, 18 Sep 2003 15:29:52 -0800
Message-ID: <F001.005D06A6.20030918152952@fatcity.com>


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).
Received on Thu Sep 18 2003 - 18:29:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US