并行查询的 PX Deq: reap credit 等待

PX Deq: reap credit在并行查询里被认为是一个空闲等待，Deq = DEQUEUE，这是一个关于出队的等待，reap credit指在等待传输一个认证信息或这说响应信息，以确认并行通道的畅通性。

Oracle文档这样描述这个等待：
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).


然而在某些环境下，这个等待却的确意味着异常，在以下一个小时的采样中，数据库的并行等待出现在第一位，此时数据库已经认为这个RAC节点出现了异常，不再尝试连接这个节点：

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
PX Deq: reap credit                          ############         727    20.52
latch free                                          3,924         694    19.59
CPU time                                                          661    18.66
async disk IO                                      58,388         373    10.52
IPC send completion sync                              724         300     8.47
          -------------------------------------------------------------

服务受到了影响，经过诊断，发现是由于一个对于GV$SESSION的频繁查询引发了这一等待，导致数据库在频繁的校验Channel信息时出现异常，数据库将负载自动balance到另外实例。

跟踪一下对于gv$session的查询，可以获得如下输出，输出显示"PX Deq: reap credit"以及"PX Deq: Join ACK"等待交替出现：

PARSING IN CURSOR #1 len=31 dep=0 uid=0 oct=3 lid=0 tim=6801278325606 hv=3192025662 ad='41773c00'
select count(*) from gv$session
END OF STMT
PARSE #1:c=10000,e=14597,p=0,cr=11,cu=3,mis=1,r=0,dep=0,og=4,tim=6801278325605
BINDS #1:
WAIT #1: nam='PX Deq: reap credit' ela= 26 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 220 p1=268500992 p2=1 p3=504403163461461976
WAIT #1: nam='PX Deq: reap credit' ela= 5 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 76 p1=268500992 p2=2 p3=504403163461461976
WAIT #1: nam='PX Deq: reap credit' ela= 4 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 21 p1=268566528 p2=1 p3=504403163461461336
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 758 p1=268566528 p2=2 p3=504403163461461336
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 92 p1=268566528 p2=1 p3=504403163461461336
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 78 p1=268566528 p2=2 p3=504403163461461336
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Parse Reply' ela= 234 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Parse Reply' ela= 16675 p1=200 p2=2 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 5 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Parse Reply' ela= 12601 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 4 p1=0 p2=0 p3=0
EXEC #1:c=0,e=31484,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=6801278357141
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 4 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Execute Reply' ela= 51 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Execute Reply' ela= 511 p1=200 p2=2 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Execute Reply' ela= 155 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 4 p1=0 p2=0 p3=0
WAIT #1: nam='DFS lock handle' ela= 303 p1=1128857605 p2=9 p3=1
WAIT #1: nam='DFS lock handle' ela= 237 p1=1128857605 p2=9 p3=3
WAIT #1: nam='DFS lock handle' ela= 92 p1=1128857605 p2=9 p3=2
WAIT #1: nam='DFS lock handle' ela= 462 p1=1128857605 p2=9 p3=2
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 19 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Signal ACK' ela= 3 p1=0 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Signal ACK' ela= 134 p1=10 p2=2 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 1 p1=0 p2=0 p3=0
FETCH #1:c=0,e=2454,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=6801278359638
WAIT #1: nam='DFS lock handle' ela= 222 p1=1128857605 p2=9 p3=1
WAIT #1: nam='DFS lock handle' ela= 254 p1=1128857605 p2=9 p3=3
WAIT #1: nam='DFS lock handle' ela= 74 p1=1128857605 p2=9 p3=2
WAIT #1: nam='DFS lock handle' ela= 476 p1=1128857605 p2=9 p3=2
WAIT #1: nam='PX Deq: reap credit' ela= 4 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 17 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 1 p1=0 p2=0 p3=0
WAIT #1: nam='enqueue' ela= 128 p1=1347616774 p2=2 p3=0
WAIT #1: nam='enqueue' ela= 5070 p1=1347616774 p2=2 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 149 p1=1650815232 p2=1 p3=0
FETCH #1:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=6801278366394
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
*** 2009-09-01 10:55:19.637
WAIT #1: nam='SQL*Net message from client' ela= 10040665 p1=1650815232 p2=1 p3=0
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE '
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=0 op='VIEW  '
STAT #1 id=3 cnt=0 pid=2 pos=1 obj=16 op='FIXED TABLE FULL X$KSUSE '

在正常情况下，这些查询并不意味着什么，但是一旦系统出现负荷升高或性能波动，则易引起数据库问题。

所以我们应该获得的经验是：在RAC环境中，应当避免频繁查询GV$全局视图。而Metalink上可以找到几则关于访问GV$SESSION视图的Bug。

-The End-