PX DEQ CREDIT SEND BLKD(1)--Tips to Reduce Waits for "PX DEQ CREDIT SEND

Tips to Reduce Waits for "PX DEQ CREDIT SEND BLKD" at Database Level (文档 ID 738464.1)

最近碰到了PX Deq Credit: send blkd事件，具体如下面AWR report截图：

 

 

mos上相关的论述：

Applies to:

Oracle Database - Enterprise Edition - Version 9.0.1.0 and later
Information in this document applies to any platform.
***Checked for relevance on 11-DEC-2011***

Purpose

This article describes what the "PX DEQ CREDIT SEND BLKD" wait is, and gives some idea's what can be checked to reduce the wait for "PX Deq Credit: need buffer" and "PX Deq Credit: send blkd" wait event at database level.

Details

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