Re: Parallel select, excessive IO and parallel_execution_message_siz
Date: Wed, 25 Apr 2012 09:40:27 +0300
Message-ID: <OFB5912C3A.099C03C1-ONC22579EA.00243067-C22579EB.0024AA0E_at_seb.lt>
>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_at_evdbt.com>
To: Laimutis.Nedzinskas_at_seb.lt, oracle-l_at_freelists.org
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_at_EvDBT.com 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_at_seb.lt [mailto:Laimutis.Nedzinskas_at_seb.lt]
Sent:: Monday, April 23, 2012 08:23 AM
To: oracle-l_at_freelists.org
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 -- http://www.freelists.org/webpage/oracle-l
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 25 2012 - 01:40:27 CDT