Re: Parallel_execution_message_size

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 12 Oct 2004 21:14:00 +0100
Message-ID: <003601c4b098$06b59ea0$6702a8c0@Primary>

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_at_acxiom.com> To: <oracle-l_at_freelists.org> 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

Received on Tue Oct 12 2004 - 15:11:42 CDT