Parallel Query Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 266

Output from the query illustrates that there are two parallel processing running, one on instance 1 and the
other on instance 4. The parallel execution servers are confined to run only on one instance because the parameter
PARALLEL_FORCE_LOCAL has been enabled. Most of the parallel execution servers are currently waiting on PX Deq:
Execution Msg . Parallel execution servers transfer data back and forth between the QC using messages. The amount
of message that could be transferred between instances is controlled using the parameter PARALLEL_EXECUTION_
MESSAGE_SIZE . And the current size of this parameter in this specific example is 16. Based on the total time waited for
the execution message, the value of this parameter may have to be increased.
01:54:37 SQL> show parameter parallel_execution
NAME TYPE VALUE
------------------------------------ ----------- --------------
parallel_execution_message_size integer 16384
INT Username QC/Slave Slave SID QC SID Req Actual QC Wait Event
Set DOP DOP Inst
---- ------------ ---------- ------ ------ ------ ------ ------- ------- ---------------------------
1 DWH QC 251 251 SQL*Net message from client
1 - p023 (Slave) 2 22 251 12 12 1 PX Deq: Execution Msg
1 - p022 (Slave) 2 743 251 12 12 1 PX Deq: Execution Msg
1 - p021 (Slave) 2 709 251 12 12 1 PX Deq: Execution Msg
1 - p020 (Slave) 2 658 251 12 12 1 PX Deq: Execution Msg
1 - p019 (Slave) 2 593 251 12 12 1 PX Deq: Execution Msg
1 - p018 (Slave) 2 557 251 12 12 1 PX Deq: Execution Msg
1 - p017 (Slave) 2 407 251 12 12 1 PX Deq: Execution Msg
1 - p016 (Slave) 2 353 251 12 12 1 PX Deq: Execution Msg
1 - p015 (Slave) 2 310 251 12 12 1 PX Deq: Execution Msg
1 - p014 (Slave) 2 258 251 12 12 1 PX Deq: Execution Msg
1 - p013 (Slave) 2 208 251 12 12 1 PX Deq: Execution Msg
1 - p012 (Slave) 2 163 251 12 12 1 PX Deq: Execution Msg
1 - p011 (Slave) 1 109 251 12 12 1 PX Deq Credit: send blkd
1 - p010 (Slave) 1 56 251 12 12 1 PX Deq Credit: send blkd
1 - p009 (Slave) 1 18 251 12 12 1 PX Deq Credit: send blkd
1 - p008 (Slave) 1 741 251 12 12 1 PX Deq Credit: send blkd
1 - p007 (Slave) 1 698 251 12 12 1 PX Deq: Execution Msg
1 - p006 (Slave) 1 655 251 12 12 1 PX Deq: Execution Msg
1 - p005 (Slave) 1 607 251 12 12 1 PX Deq: Execution Msg
1 - p004 (Slave) 1 548 251 12 12 1 PX Deq: Execution Msg
1 - p003 (Slave) 1 514 251 12 12 1 PX Deq: Execution Msg
1 - p002 (Slave) 1 465 251 12 12 1 PX Deq: Execution Msg
1 - p001 (Slave) 1 398 251 12 12 1 PX Deq: Execution Msg
1 - p000 (Slave) 1 348 251 12 12 1 PX Deq: Execution Msg
4 DWH QC 300 300 SQL*Net message from client
4 - p007 (Slave) 1 148 300 6 6 4 PX Deq Credit: send blkd
4 - p005 (Slave) 1 60 300 6 6 4 PX Deq Credit: send blkd
4 - p003 (Slave) 1 742 300 6 6 4 PX Deq Credit: send blkd
4 - p002 (Slave) 1 647 300 6 6 4 PX Deq Credit: send blkd
4 - p001 (Slave) 1 606 300 6 6 4 PX Deq Credit: send blkd
4 - p000 (Slave) 1 548 300 6 6 4 PX Deq Credit: send blkd