Re:RE: Parallel select, excessive IO and parallel_execution_message_siz
From: <Laimutis.Nedzinskas_at_seb.lt>
Date: Tue, 1 May 2012 10:11:34 +0300
Message-ID: <OF8EE1DDBB.AC4B08D3-ONC22579F1.0026EC4E-C22579F1.00278357_at_seb.lt>
I've got a confirmation message size parameter has nothing to do with the problem. The stop/start (db bounce or kill the query-run in again) was responsible for return to normal execution. Curently I am just making sure the bind variables passed into the query are the same in both runs.
All else seems to be exactly the same - same plan, same plan hash value, same optimizer environment (judging from v$sql), same workarea usage. I've got a confirmation from my collegues they experienced a similar problem before with PX. Kill/restart usually helped. Wondering if anyone else had such experience ?
Date: Tue, 1 May 2012 10:11:34 +0300
Message-ID: <OF8EE1DDBB.AC4B08D3-ONC22579F1.0026EC4E-C22579F1.00278357_at_seb.lt>
I've got a confirmation message size parameter has nothing to do with the problem. The stop/start (db bounce or kill the query-run in again) was responsible for return to normal execution. Curently I am just making sure the bind variables passed into the query are the same in both runs.
All else seems to be exactly the same - same plan, same plan hash value, same optimizer environment (judging from v$sql), same workarea usage. I've got a confirmation from my collegues they experienced a similar problem before with PX. Kill/restart usually helped. Wondering if anyone else had such experience ?
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
---------------------------------------------------------------------------------
Please consider the environment before printing this e-mail
--
http://www.freelists.org/webpage/oracle-l
Received on Tue May 01 2012 - 02:11:34 CDT