Re: Parallel select, excessive IO and parallel_execution_message_siz
Date: Mon, 23 Apr 2012 15:04:12 +0000
Message-ID: <W927504342210391335193452_at_webmail22>
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 readsmany 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 recordstable)After the parallel_execution_message_size was bumped to maximum (64k) andoracle server bounced the select completed within expected time and IOlimits.Can parallel_execution_message_size affect IO (and time) of parallel selectso 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-l
Received on Mon Apr 23 2012 - 10:04:12 CDT