PX Deq: reap credit & GV$SESSION

在一个4节点的rac中查询全局视图时GV$SESSION时，总能出现三个相同的等待事件PX Deq: reap credit。
在Metalink的ID 250947.1文章中记录了这个等待事件。

This wait event indicates that we are doing a non-blocking test to see if any channel has returned a message.
We should see that although there is a high number of these events, the time accumulated to it should be zero

(or really low).

Wait Time:
This wait event indicates that we are doing a non-blocking test to see if any channel has returned a message. 
Systemwide Waits:
You normally do not see a high wait on this event system wide. The wait should be investigated at  the query level than at the system level.
Reducing Waits / Wait times:
There is no general advice to reduce the waitime for this event.
Depending on which process is doing this it could indicate that either the consumers are starved for data (IO bound perhaps) or the producers are waiting on the consumers to "return" the flow control credit (consumers may be compute or IO bound). You should investigate Query plans, check for data skew, IO contention (table or tmp segment) etc. , to get a deeper understanding of the symptoms.

 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> SELECT S.INST_ID  FROM GV$SESSION S GROUP BY s.inst_id ;
   INST_ID
----------
         1
         2
         4
         3
SQL> SELECT Instance_number FROM V$INSTANCE;
INSTANCE_NUMBER
-------------------------
                             1         
当前在1节点上，这个等待事件并没有在1节点上产生。                          
SQL> col event for a20;        
SQL> SELECT S.INST_ID,
  2         S.EVENT,
  3         S.SQL_ADDRESS,
  4         S.SQL_HASH_VALUE,
  5         S.SQL_ID
  6    FROM GV$SESSION S
  7   WHERE S.USERNAME IS NOT NULL
  8     AND WAIT_CLASS != 'Idle'
  9     ORDER BY s.INST_ID;

   INST_ID  EVENT                  SQL_ADDRESS             SQL_HASH_VALUE  SQL_ID
-------------  --------------------  ------------------------       ---------------------- -------------------
         2 PX  Deq: reap credit  0000000252531DD0     1779404760         23d8ba9p0z3ys
         3 PX  Deq: reap credit  0000000250B3AC88     1779404760         23d8ba9p0z3ys
         4 PX  Deq: reap credit  0000000250256420     1779404760         23d8ba9p0z3ys
        
在2节点上时，这个等待事件在其它的三个节点上发生。
SQL> SELECT Instance_number FROM V$INSTANCE;
INSTANCE_NUMBER
---------------
              2
SQL> SELECT S.INST_ID,
  2         S.EVENT,
  3         S.SQL_ADDRESS,
  4         S.SQL_HASH_VALUE,      
  5         S.SQL_ID
  6    FROM GV$SESSION S
  7   WHERE S.USERNAME IS NOT NULL
  8     AND WAIT_CLASS != 'Idle'
  9     ORDER BY s.INST_ID;

   INST_ID EVENT                SQL_ADDRESS      SQL_HASH_VALUE SQL_ID
---------- -------------------- ---------------- -------------- -------------
         1 PX Deq: reap credit  00000001A6FD42D8      827254137 6nw92awsnxtbt
         3 PX Deq: reap credit  0000000233778B80      827254137 6nw92awsnxtbt
         4 PX Deq: reap credit  0000000211147638      827254137 6nw92awsnxtbt

 

 

 

这个等待事件一般可以忽略，但也有例外情况。
见：http://www.eygle.com/archives/2009/09/px_deq_reap_credit.html

 

频繁查询个oracle内部的视图都会引起数据库问题，看来在生产库上干啥都要小心啊！！！！