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.

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 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 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%';

ORA-12203: TNS:unable to connect to destination

Cause:	Invalid address specified or destination is not listening. This error can also occur because of underlying network or network transport problems.
Action:	Verify that the net service name you entered was correct. Verify that the ADDRESS portion of the connect descriptor which corresponds to the net service name is correct. Ensure that the destination process (for example the listener) is running at the remote node.

ORA-12203: TNS:unable to connect to destination

Cause:	Invalid address specified or destination is not listening. This error can also occur because of underlying network or network transport problems.
Action:	Verify that the net service name you entered was correct. Verify that the ADDRESS portion of the connect descriptor which corresponds to the net service name is correct. Ensure that the destination process (for example the listener) is running at the remote node.