排查PX Deq Credit: send blkd 事件过高

 

Tips to Reduce Waits for "PX DEQ CREDIT SEND BLKD" at Database Level [ID 738464.1]
		修改时间 15-JAN-2009     类型 BULLETIN     状态 PUBLISHED

In this Document
  
  
  

---

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

 
 

Report for the Degree of Parallelism on Tables and Indexes [ID 270837.1]
		修改时间 28-APR-2010     类型 SCRIPT     状态 PUBLISHED

***Checked for relevance on 28-April-2010*** 


PURPOSE
-------

Provide some scripts for a DBA to check the degree of parallelism
on tables and indexes.


SCOPE & APPLICATION
-------------------

DBA's, developer and Analysts.


Check Script
-------------


Rem  How many CPU does the system have?
Rem  Default degree of parallelism is
Rem  Default = parallel_threads_per_cpu * cpu_count
Rem  -------------------------------------------------;  
select substr(name,1,30) Name , substr(value,1,5) Value 
from v$parameter 
where  name in  ('parallel_threads_per_cpu' , 'cpu_count' ); 


Rem  Normally   DOP := degree * Instances
Rem  See the following Note for the excat formula.  
Rem  Note:260845.1  Old and new Syntax for setting Degree of Parallelism  
Rem  How many tables a user have with different DOP's
Rem  -------------------------------------------------------;
select * from (
select  substr(owner,1,15)  Owner  , ltrim(degree) Degree, 
        ltrim(instances)  Instances, 
        count(*)   "Num Tables"  , 'Parallel' 
from all_tables
where ( trim(degree) != '1' and trim(degree) != '0' ) or  
      ( trim(instances) != '1' and trim(instances) != '0' )
group by  owner, degree , instances
union
select  substr(owner,1,15) owner  ,  '1' , '1' , 
         count(*)  , 'Serial' 
from all_tables
where ( trim(degree) = '1' or trim(degree) != '0' ) and  
      ( trim(instances) != '1' or trim(instances) != '0' )
group by  owner
)
order by owner;


Rem  How many indexes a user have with different DOP's
Rem  ---------------------------------------------------;
select * from (
select  substr(owner,1,15) Owner  ,
        substr(trim(degree),1,7) Degree , 
        substr(trim(instances),1,9) Instances ,
        count(*)   "Num Indexes",
        'Parallel' 
from all_indexes
where ( trim(degree) != '1' and trim(degree) != '0' ) or  
      ( trim(instances) != '1' and trim(instances) != '0' )
group by  owner, degree , instances
union
select  substr(owner,1,15) owner  ,  '1' , '1' , 
         count(*)  , 'Serial' 
from all_indexes
where ( trim(degree) = '1' or trim(degree) != '0' ) and  
      ( trim(instances) != '1' or trim(instances) != '0' )
group by  owner
)
order by owner;


Rem Tables that have Indexes with not the same DOP
Rem !!!!! This command can take some time to execute !!!
Rem ---------------------------------------------------;
set lines 150
select  substr(t.owner,1,15) Owner  ,
        t.table_name ,
        substr(trim(t.degree),1,7) Degree , 
        substr(trim(t.instances),1,9) Instances, 
        i.index_name ,
        substr(trim(i.degree),1,7) Degree , 
        substr(trim(i.instances),1,9) Instances 
from all_indexes  i,
     all_tables   t
where  ( trim(i.degree) !=  trim(t.degree)  or
         trim(i.instances) !=  trim(t.instances)  ) and
         i.owner = t.owner  and
         i.table_name = t.table_name;
           





RELATED DOCUMENTS
-----------------
Note:260845.1  Old and new Syntax for setting Degree of Parallelism