Re: Parallel select, excessive IO and parallel_execution_message_siz
- From: Laimutis.Nedzinskas@xxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 25 Apr 2012 09:40:27 +0300
>If this seems a likely explanation, then it might be worthwhile to test
using sort-merge join instead of a hash join?
at this point I want to test again with smaller
parallel_execution_message_size to make sure the heavy IO is caused by this
parameter.
The cause
---------------------------------------------------------------------------------
Please consider the environment before printing this e-mail
From: "Tim Gorman" <tim@xxxxxxxxx>
To: Laimutis.Nedzinskas@xxxxxx, oracle-l@xxxxxxxxxxxxx
Date: 2012.04.23 18:04
Subject: Re: Parallel select, excessive IO and
parallel_execution_message_siz
Laimutis,
I think you stated that this is a join of three tables, one of which is
very small (i.e. 2 rows)? Assuming that the other two are very large...
With a hash-join, the "inner" table of the join is read into a hash table
in the PGA (i.e. memory and/or temp tablespace). Building a hash table
requires the entire inner table to be scanned when neither table is
partitioned, so that accounts for the increase in PIO. The parallel
execution on the outer (a.k.a. driving) table can be partitioned according
to the DOP, so there is no redundant PIOs in that step, but if you have two
large tables being hash-joined then you have a large hash table in each
parallel worker's PGA to populate, so then you might see the effect you are
describing.
If this seems a likely explanation, then it might be worthwhile to test
using sort-merge join instead of a hash join?
Hope this helps...
Tim Gorman
consultant => Evergreen Database Technologies, Inc.
postal => PO Box 352151, Westminster CO 80035
email => Tim@xxxxxxxxx
mobile => +1-303-885-4526
fax => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...
-----Original Message-----
From: Laimutis.Nedzinskas@xxxxxx [mailto:Laimutis.Nedzinskas@xxxxxx]
Sent:: Monday, April 23, 2012 08:23 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Parallel select, excessive IO and parallel_execution_message_siz
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
---------------------------------------------------------------------------------
Please consider the environment before printing this e-mail --
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
