Re: Parallel_execution_message_size

["Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>]

I believe the Parallel_execution_message_size
is the buffer size used on parallel_to_parallel
execution - it represents the packet size on
the parallel 'table queues'.

The benefit of increasing it is that you get
fewer slave to slave communications, and
therefore reduce the scope for collision
(PX Deq: Send Blocked).

It is a bit of a surprise that this should
improve the speed of sorting - but possibly
if you have a slave doing a
    select / partial sort
before sending data to the next layer for
sort completion, you get a better chance
of more sorted data in a packet, and therefore
need to spend less CPU on sorting at the higher
level.

It would be interesting though to run a stats check
with the different sizes to see if the time saving was
on reduced contention (smaller packets), or reduced
CPU (larger packets).  I did not observe any benefit
on the occasions I have fiddled with the parameter in
the past.


Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated Sept 19th





----- Original Message ----- 
From: "Wolfson Larry - lwolfs" <lawrence.wolfson@xxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, October 12, 2004 2:15 AM
Subject: Parallel_execution_message_size


Does anyone have the skinny on this?  I ran into one of our DBAs from
another office and he was telling me about huge improvements in sorts, using
this.

I found the stuff below et. al., but I didn't a clear picture of how you
decide how big it should be and what all it affects.

I did see some HP example on 10G with it set to 16K.

TIA
Larry


Jeff Marsh
Optimizing Parallel Query in Oracle 8i
http://www.evdbt.com/PX%202003.pps



http://www.phptr.com/articles/article.asp?p=169538&seqNum=6


From 10G DataGuard:
Set PARALLEL_EXECUTION_MESSAGE_SIZE = 4096

When using parallel media recovery or parallel standby recovery, increasing
the PARALLEL_EXECUTION_MESSAGE_SIZE database parameter to 4K (4096) can
improve parallel recovery by as much as 20 percent. Set this parameter on
both the primary and standby databases in preparation for switchover
operations. Increasing this parameter requires more memory from the shared
pool by each parallel execution slave process.

The PARALLEL_EXECUTION_MESSAGE_SIZE parameter is also used by parallel query
operations and should be tested with any parallel query operations to ensure
there is sufficient memory on the system. A large number of parallel query
slaves on a 32-bit installation may reach memory limits and prohibit
increasing the PARALLEL_EXECUTION_MESSAGE_SIZE from the default 2K (2048) to
4K.


**********************************************************************
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination,
distribution, or copying of this communication is strictly
prohibited.
If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

• References:
  • Parallel_execution_message_size
    • From: Wolfson Larry - lwolfs

Other related posts:

Parallel_execution_message_size

Re: Parallel_execution_message_size