Blog

Waiting for “PX Deq Credit: send blkd”?

Waiting for “PX Deq Credit: send blkd”?

Occasionally I hear someone talking about how to reduce the waiting time of the event “PX Deq Credit: send blkd”. And it’s not uncommon to also realize that people try various artifices to reduce the time of this event, in most cases without properly understand the concept behind it. Or read that it is considered Idle event and do not give enough attention to a possible problem.

 

To start, let’s review the concept of SQL query in parallel

Whenever a SQL query is executed in parallel, is created a process called Query Coordinator (QC) and various other processes Queries Slaves, called Pn, where n varies with the number of parallelism, for example P1, P2, P3 and so on.

The Query Coordinator is responsible, among other things, coordinate the implementation of Queries Slaves, gather the information received and submit the result set to the user who submitted the SQL query. Queries Slaves are the true responsible for fetching the data from the SQL query itself. When Queries Slaves finish reading the data, send that data to the Query Coordinator via buffer messages. These messages can have their size specified by the parameter parallel_execution_message_size, and are exchanged in a specific area of the buffer.

The parallel_automatic_tuning parameter sets the pool of buffer messaging. If the parameter is enabled, the buffer will be in the large pool or will be on shared pool whether the parameter is disabled. So if it is expected that your database run many parallel queries, consider fit correctly the pool size.

The figure below illustrates an example of a parallel query over EMP table. For this example, assume that the DOP (Degree Of Parallelism) of the table is 4, and that the command executed was a simple:

SELECT *
  FROM emp
 ORDER BY ename;

imagem

 

Cool, but where the wait event “PX Deq Credit: send blkd” fits into this story?

Imagine the following scenario:

  1. The Query Slave P3 just deliver a message of 4KB to Query Coordinator.

  2. The Query Coordinator received the message from P3 and more than 200 from other Queries Slaves.

  3. The Query Slave P3 is ready to send another message to the Query Coordinator, but the Query Coordinator has not finished processing the message sent earlier by the Query Slave P3.

  4. The Query Slave P3 is waiting the Query Coordinator finish processing your previous message in order to receive the new message, and while wait generating the event “PX Deq Credit: send blkd”.

In summary, this wait event occurs when a Query Slave is ready to post a message, but must wait until the Query Coordinator has finished processing a message sent earlier so send the next message.

imagem

 

How to identify if my database is expecting too much for “PX Deq Credit: send blkd”?

With Mandela this can be readily identified. Through the chart “Wait Event Breakdown“, we can see the wait events occurred over a given period. In the next picture, we see an example of the day May 25 (in the range delimited) where there was a significant peak in the waited time. Visually identified that most of the time that band was expected due the event “PX Deq Credit: send blkd”.

Clicking the right mouse button on the peak we can easily identify the SQL statement that most waited for this event (available only for 10g and 11g), or we can list the commands that took longer to perform, as a starting point for research the problem.

 

What can I do to reduce the time of the event “PX Deq Credit: send blkd”?

In note 271767.1 (metalink) is written clearly that isn’t there a general formula to reduce the waiting time of this event and presents the way to find out which Queries Slaves are waiting longer this event.

If you query the V$SESSION_WAIT at the time of issue, you can use the parameters P1 (sleeptime/senderid), P2 (passes) and P3 (qref) to run the script as the note quoted above.

sleeptime/senderid

You can decode the senderid with following SQL block

SET SERVEROUTPUT ON
undef p1
DECLARE
  inst   VARCHAR(20);
  sender VARCHAR(20);
BEGIN
  SELECT BITAND(&&p1, 16711680) - 65535 AS SNDRINST,
         DECODE(BITAND(&&p1, 65535),65535, 'QC', 'P'||TO_CHAR(BITAND(&&p1, 65535),'fm000') ) AS SNDR
    INTO inst , sender
    FROM dual
   WHERE BITAND(&&p1, 268435456) = 268435456;
  DBMS_OUTPUT.put_line('Instance = '||inst);
  DBMS_OUTPUT.put_line('Sender = '||sender );
END;
/

If you get no rows back than p1 is a sleeptime.
As example senderid/sleeptime = 268501004. Than the script will give
Instance = 1
Sender = P012
This means we have to investigate why slave P012 is not fast enough to dequeue the messages and send the blocks.

passes

Number times we have looped through waiting for a message so far.

qref

The identifier of the table queue from which we need the buffer.

However, understanding the concept behind this event we can use logical to identify the root of the problem and then reduce the waiting time:

  • Look for the SQL statements that are using parallelism
  • Analyze the value of parallelism of SQL statements is suitable
  • Look for tables and indexes with DOP inappropriate
  • Verify that the message size is well defined
  • Make sure the size of its buffer pool that is messaging is well scaled

A common case of this problem is SQL statements with the PARALLEL hint specifying values absurdly high for the available hardware. A simple adjustment of the value of parallelism is an easy solution and the result can be impressive.

3


Discussion

  1. Manoj  October 17, 2012

    very good nice work

    (reply)
  2. data recovery Leeuwarden  April 24, 2013

    Vol met bruikbare waarheden. Leest gemakkelijk. Ben er blij mee.

    (reply)
  3. Mario  July 9, 2015

    What is &amp in the query?

    (reply)

Add a Comment