Re: Parallel select, excessive IO and parallel_execution_message_siz

From: Greg Rahn <greg_at_structureddata.org>
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-l

Received on Tue Apr 24 2012 - 19:51:29 CDT