Re: Parallel select, excessive IO and parallel_execution_message_siz

From: Greg Rahn <>
Date: Tue, 24 Apr 2012 17:51:29 -0700
Message-ID: <>

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, <> 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

Greg Rahn  |  blog <>  |  twitter <>  |
 linkedin <>

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

Original text of this message