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

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

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-

By eygle on 2009-09-02 08:20 |

Comments (1) |

Case | 2381 |

1 Comment

无意中我的RAC两点数据库数据出现这个等待，即发出

SELECT * FROM gv$session_wait WHERE wait_class<>'Idle' 会提示另一个节点有这个等待。

如果换到另一个节点去查询，即会提示刚才那个节点有这个等待。

看了您的这篇，懂了一些：)