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: parallel execution on partitioned tables

Re: parallel execution on partitioned tables

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Tue, 3 Aug 2004 20:39:56 +0300
Message-ID: <030901c47980$e506cf20$9c969fd9@porgand>


I think the point (similar which Cary also made) is that Oracle's Parallel Query and PX jobs are inefficient by nature, mostly because coordination, message and sometimes even data passing is required between different PX processes.

If you don't have enough spare resources, then parallel execution will definitely make your querys response time and databases overall performance worse.

If you have enough spare resources then parallel execution *may* make your query's response time better by forcing Oracle to grab as much resources as possible, despite degraded overall efficiency.

If designed badly, parallel execution performance may be extremely low because all processes are broadcasting messages & data to each other, also they are waiting for messages from each other, etc..

With badly designed/tuned parallel query it is even possible to see a situation where there are no obvious bottleneck (CPU or disk IO), instead most of time spent is waiting/sleeping on parallel execution messages.

Tanel.

> We haven't seen any negative results from using parallel. The db is a =
> datawarehouse with no more than 4 concurrent users connecting through =
> Cognos during the day and no activity at night outside the Informatica =
> ETL process.=20
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Cary Millsap
> Sent: Tuesday, August 03, 2004 12:09 PM
> To: oracle-l_at_freelists.org
> Subject: RE: Oracle 9.2.0.5 "10046" Tracing Causes Server Crash on
> Windows 2000?
>
>
> Bugs and other undocumented behaviors notwithstanding, using parallel
> features produces one specific and very important risk that you should
> understand.
>
> When you N-way parallelize a process, you convert a single process into =
> =3D
> N
> distinct processes (at least N--in Oracle, it's actually 2N+1). Each of
> these processes now competes for CPU time, memory busses, latches, =3D
> locks,
> and everything else that a process competes for. What I see so =3D
> frequently is
> the following...
>
> What the user wants: My user action runs in 80 seconds, so I'll 4-way
> parallelize it in attempt to make it run in 20 seconds.
>



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Tue Aug 03 2004 - 12:36:02 CDT

Original text of this message

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