eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« Oracle 11g R2 For Linux发布 - 2009-08-31 | Blog首页 | 11gR2新特性之一-列式压缩(Columnar Compression) »

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

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-



历史上的今天...
    >> 2013-09-02文章:
    >> 2010-09-02文章:
    >> 2008-09-02文章:
    >> 2007-09-02文章:
    >> 2006-09-02文章:
    >> 2005-09-02文章:

By eygle on 2009-09-02 08:20 | Comments (1) | Case | 2381 |

1 Comment

无意中我的RAC两点数据库数据出现这个等待,即发出
SELECT * FROM gv$session_wait WHERE wait_class<>'Idle' 会提示另一个节点有这个等待。
如果换到另一个节点去查询,即会提示刚才那个节点有这个等待。
 看了您的这篇,懂了一些:)


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com