Re: Parallel select, excessive IO and parallel_execution_message_siz
Date: Tue, 24 Apr 2012 17:51:29 -0700
Message-ID: <CAGXkmis5jKjy5xQ5kT2jRHvAWtu_GD+sTF8o53zVNaUMRGnV2w_at_mail.gmail.com>
Can you post the execution plan (nice fixed width font, no line wraps, including the predicates section)?
I'm going to guess here (warning!) but 3 things in this email lead me to my hypothesis:
- you are using PX - the plan had a filter (assuming that means a FILTER row source) - you said more data was read than was present in the sum of the tables
What is likely happening is that the a subquery could not be unnested & merged and what you have is basically a parallel nested loop join -- each PX server is reading rows and applying the "two row filter". This is a guess until I can see the plan.
BTW, you probably not need set parallel_execution_message_size more than 16k (which I would recommend if that is not the default on your platform). Else you are fighting a symptom, not a root cause.
On Mon, Apr 23, 2012 at 7:23 AM, <Laimutis.Nedzinskas_at_seb.lt> wrote:
> Did anyone experience this phenomena of parallel select:
>
> select /*+parallel ( 2) */ went reading one segment like crazy - disk reads
> many times over the segment(table) size.
> The finding was supported by v$sql, v$sesstat and AWR reports.
>
> The plan was a hash join of two tables plus a filter (select from 2 records
> table)
>
> After the parallel_execution_message_size was bumped to maximum (64k) and
> oracle server bounced the select completed within expected time and IO
> limits.
>
> Can parallel_execution_message_size affect IO (and time) of parallel select
> so drastically ?
>
> Thank you in advance, Laimis N
>
>
-- Regards, Greg Rahn | blog <http://bit.ly/u9N0i8> | twitter <http://bit.ly/v733dJ> | linkedin <http://linkd.in/gregrahn> -- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 24 2012 - 19:51:29 CDT