PX DEQ CREDIT SEND BLKD

Tips to Reduce Waits for "PX DEQ CREDIT SEND BLKD" at Database Level [ID 738464.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 9.0.1.0 to 11.1.0.7
Information in this document applies to any platform.

Purpose

This article give some idea's what can be check to reduce the wait for "PX Deq Credit: need buffer" and "PX Deq Credit: send blkd"  wait event at database level. 

Scope and Application

For dba's

Tips to Reduce Waits for "PX DEQ CREDIT SEND BLKD" at Database Level

The wait events  "PX Deq Credit: need buffer"  and  "PX Deq Credit: send blkd" are occur when data or  messages are exchanged between process that are part of a px query..
There are at least 3 different main area's that can cause this waits.

1. We see high waits if a lot of data and message are exchanged between parallel processes. The cause can be that the execution plan is bad or there are problem with the parallel execution setup.
2. There is a problem with the resource like the CPU or the interconnect. As example with a CPU utilization around 100% the process are limited by the CPU and can not send the data fast enough.
3. If parallel queries are hang where one process waits for "PX Deq Credit: need buffer" as example.

In this article we do not discuss Point 3  and 2.
1.) Parallel Degree settings
At database level you should check your parallel execution setup. Are there objects that  should not have a degree setting. As example a "alter index <indexname> rebuild parallel 4;" would cause a degree of 4 on that index, although the intention was to rebuild the index with parallel 4 , but do not change the degree.
The best is to run the SQL command from:
Note.270837.1  Report for the Degree of Parallelism on Tables and Indexes 
The fourth command  from this script would show a mismatch between DOP of a index and the table. Here an example output:

OWNER  TABLE_NAME   DEGREE  INSTANCES INDEX_NAME   DEGREE  INSTANCES
------ ------------ ------- --------- ------------ ------- ---------
SCOTT  DEPT         1       1         PK_DEPT      4    1
SCOTT  EMP          1       1         PK_EMP       DEFAULT DEFAULT

We see that the index PK_DEPT and PK_EMP have a parallel degree , but the base tables not.  Here you should consider to change the index setting to no parallel

alter index SCOTT.PK_DEPT noparallel;

And the second script can be helpful , to get an overview over the DOP distribution in your schema. Here is an example output

OWNER  DEGREE     INSTANCES  Num Tables  'PARALLE
------ ---------- ---------- ----------  --------
OSS    1          1                  126 Serial
OSS    8          1                   1  Parallel

We see that there is only 1 table with a degree of 8 in the schema OSS. Maybe it was not planned to have a table with a DOP 8.. You should consider to find the  table and set it no parallel. You can use as example for the OSS schema

select table_name from all_tables
where ( trim(degree) != '1' and trim(degree) != '0' ) or  
      ( trim(instances) != '1' and trim(instances) != '0' )
      and owner = 'OSS';

and the result is here

TABLE_NAME
------------------------------
OSS_EMP

To change the table to no parallel you can run

alter table OSS.OSS_EMP noparallel;

All this would reduce the number of parallel execution queries and so also the data that needs to be transfered.
It can also helpful to check if the degree on the objects(tables/indexes) is not to high. As example in most situation the performance is good when tables/indexes with a size less than 200 MB, do not have a parallel degree.
Sometimes it helps to increase PARALLEL_EXECUTION_MESSAGE_SIZE = 8k or 16K, but this cause a larger  "PX msg pool". This pool can we monitored via

select * from v$sgastat where upper(name)  like 'PX%';