1 Reply Latest reply: Jan 28, 2013 4:59 AM by hbaruas RSS

    PX DEQ CREDIT SEND BLKD on GV$

    hbaruas
      Hi All,

      We experience "PX DEQ CREDIT SEND BLKD" wait , the pink spike on OEM, when there is query on GV$ in 10G(10.2.0.5), esp on
      SELECT event, sql_id, sql_plan_hash_value, sql_opcode, session_id, session_serial#,
      module, action, client_id, DECODE(wait_time, 0, 'W', 'C'), 1, time_waited, 
      service_hash, user_id, program, sample_time, p1, p2, p3, current_file#, 
      current_obj#, current_block#, qc_session_id, qc_instance_id, INST_ID
      FROM gv$active_session_history WHERE sample_time >= :1 AND sample_time <= :2
      By DBSNMP user.

      Even tested just
      Select * from gv$active_session_history;
      The results is same waits on PX DEQ CREDIT SEND BLKD.

      Since the issue we have increased

      parallel_execution_message_size to 4k from 2K

      Still its showing same waits.

      I have some of my findings and reading on it though:

      "PX Deq Credit: Send Blked" - there are two different scenarios where it can appear - one as an "idle" event and one as a performance threat.

      When PX slaves feed a query co-ordinator (QC), only one can supply data at a time and the others go into the "PX Deq Credit: Send blkd" with a timeout of 2 seconds.
      The end user doesn't see the result set appearing any more slowly because of this.

      When one layer of PX slaves is passing data up the tree to the next layer, then there is competition for the PX table queues (virtual tables) with PX slaves blocked and unable to write into the virtual tables. Waits at this point are time-wasting events.

      PX Deq Credit: send blkd indicate that a producer wants to send data to a consumer, but the consumer is still busy with previous requests so isn’t ready to receive it. i.e.
      it’s falling behind. Reducing the DOP would reduce the number of times this happens and how long for. But we are not setting DOP on the query as its auto run by DBSNMP user.

      I would be testing it with 8K soon. But I would like to know if anyone has any ideas or suggestions on the issue and if anyone else has encountered it.

      Thanks