大量的enq-TX: row lock contention、SQL*Net message from dblink等待事件

yehc@epsoft.com

环境介绍：
OS:Linux 5.5 64Bit
Oracle RAC + ASM

1. SQL> select * from v$version ;
   
2. BANNER
   
3. --------------------------------------------------------------------------------
   
4. Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
   
5. PL/SQL Release 11.2.0.2.0 - Production
   
6. CORE    11.2.0.2.0      Production
   
7. TNS for Linux: Version 11.2.0.2.0 - Production
   
8. NLSRTL Version 11.2.0.2.0 - Production

复制代码

现状描述：
1、业务时间段A库频繁出现enq-TX: row lock contention、SQL*Net message from dblink等待事件，
初步考虑网络问题导致，但通过简单的测试，并没有发现网络方面的异常。

逻辑关系：A库调用本地存储过程通过dblink往A库做insert或者update操作，同时在本地做同样的操作：

1.       insert into f_ab01 values lr_fab01;
   
2.       insert into f_ab01@ylbxdb values lr_fab01 ;

复制代码

而本地insert into f_ab01是发起update or insert into SBDS_USCRLOG的动作。

几个时间段的AWR已上传附件。

2、数据库中有张表出现坏块：

1. ORA-01578: ORACLE data block corrupted (file # 19, block # 820928)
   
2. ORA-01110: data file 19: '+DATADG/lqsi/datafile/us_data.294.775416033'

复制代码

对象为SBDS_USCRLOG，操作日志表。

1. SQL> Select tablespace_name,segment_type,owner,segment_name
   
2.   2  From dba_extents Where file_id=19 and 820928 between block_id and block_id+blocks-1;
   
3. TABLESPACE_NAME                SEGMENT_TYPE       OWNER                          SEGMENT_NAME
   
4. ------------------------------ ------------------ ------------------------------ ----------------------------------------
   
5. US_DATA                        TABLE              LQSI                           SBDS_USCRLOG

复制代码

疑问：
1、存储过程中insert into SBDS_USCRLOG动作是否会因为该对象有坏块导致性能急剧下降？
2、就AWR分析，是否考虑网络性能是罪魁祸首？

yehc@epsoft.com

逻辑关系：A库调用本地存储过程通过dblink往A库做insert或者update操作，同时在本地做同样的操作：
更正：
逻辑关系：A库调用本地存储过程通过dblink往B库做insert或者update操作，同时在本地做同样的操作：

dla001

os监控有吗？ 同样现象的问题我们也遇到过，看了awr还有os的监控信息确定是网络的问题，我们的网络有瞬间饱和的状况。

yehc@epsoft.com

dla001 发表于 2013-11-6 14:47
os监控有吗？ 同样现象的问题我们也遇到过，看了awr还有os的监控信息确定是网络的问题，我们的网络有瞬间饱 ...

主机上也通过一些手段分析过，但没有很明显的情况，有没有什么好的工具推荐下。。。

dla001

yehc@epsoft.com 发表于 2013-11-6 14:58
主机上也通过一些手段分析过，但没有很明显的情况，有没有什么好的工具推荐下。。。 ...

我们用的是nmon。

Maclean Liu(刘相兵

给2个非RAC的AWR 普通的AWRRPT

lunar

看现象，感觉跟网络关系比较大，尤其是dblink，这个东西在两端都可以并行，但是过网络的时候很多事穿行操作的，你可以看dbconsole的图，应该比较明显的。。。

另外，坏块导致性能下降的案例也有的，具体需要看完整的awr

yehc@epsoft.com

更新：
时间段内对主机进行了tcpdump抓包分析，发现其中有个业务终端对主机的服务网卡发起大量的ARP广播包，采取了必要的措施后，数据库没有呈现好装的趋势。

附近上传量节点的两个事件段AWR。

yehc@epsoft.com

Maclean Liu(刘相兵 发表于 2013-11-6 18:39
给2个非RAC的AWR 普通的AWRRPT

两节点分别去了同样时间段内的AWR，请刘大再看看。

lunar

老版本上有类似insert into select remote 这样的bug，建议使用 insert /*+ append */ 测试一下，然后用10046跟踪单挑语句的执行计划和统计信息

yehc@epsoft.com

看了下群里明月和女神的一些问题判断方向，我补充下前面被我忽略的信息：
1、dblink之间网络没有防火墙，只有两层路由设备，而且测试了网络的带宽及通讯质量，存在丢包的概率大致在0.01%左右。
2、数据库涉及到的参数都是默认，没有特意调整：

1. SQL> show parameter db_lost
   
2. 
   
3. NAME                                 TYPE        VALUE
   
4. ------------------------------------ ----------- ------------------------------
   
5. db_lost_write_protect                string      NONE
   
6. SQL> show parameter db_block
   
7. 
   
8. NAME                                 TYPE        VALUE
   
9. ------------------------------------ ----------- ------------------------------
   
10. db_block_buffers                     integer     0
    
11. db_block_checking                    string      FALSE
    
12. db_block_checksum                    string      TYPICAL
    
13. db_block_size                        integer     8192
    
14. SQL>

复制代码

3、19号文件的坏块包涵的数据已跟用户协商后决定放弃，并通过expdp table和impdp table修正。目前可以肯定有7行数据无法恢复（费用问题，该库并没有备份，只有逻辑备份）。

yehc@epsoft.com

lunar 发表于 2013-11-6 21:25
老版本上有类似insert into select remote 这样的bug，建议使用 insert /*+ append */ 测试一下，然后用100 ...

可以鉴用下这个方法，但我的理解bug的可能性不大，业务从2009年到13年10月1号之前一直没有出现类似情况，而且之间存储过程、程序并没有更新。
国庆期间运营商线路被大水淹过后，就频繁的发现这些问题。前后用不同的方法测试，也没有很好的定位到问题的原因。

最新的操作：
1、AC01表索引重建
2、SBDS_USCRLOG表重建（通过expdp和impdp绕过坏块）

Maclean Liu(刘相兵

2节点的TOP EVENT

Event        Waits        Time(s)        Avg wait (ms)        % DB time        Wait Class
enq: TX - row lock contention        26        7,016        269843        60.57        Application
SQL*Net message from dblink        944        3,978        4214        34.35        Network


TOP SQL:
Elapsed Time (s)        Executions        Elapsed Time per Exec (s)        %Total        %CPU        %IO         SQL Id        SQL Module        SQL Text
7,016.71        127        55.25        60.58        0.01        0.01        4yu8rhan7b7fa                 update AC01 set AAB001=:1, EAC...
3,972.72        64        62.07        34.30        0.03        0.01        98h9y8qdznwqs                 BEGIN PKG_YBJK.p_Ac01(:1, :2, ...
3,406.35        62        54.94        29.41        0.00        0.00        8rqjzu3ymdqs3                 INSERT INTO F_AC01_ERR@YLBXDB ...


Owner        Tablespace Name        Object Name        Subobject Name        Obj. Type        Row Lock Waits        % of Capture
LQSI        US_DATA        AC01                 TABLE        26        100.00


行锁主要在 LQSI.AC01                 上

这里可以看到 SQL*Net message from dblink 的平均等待是4s一次，这是很不正常的

SQL*Net message from dblink        944        3,978        4214        34.35        Network


SQL*Net message from dblink导致TX的可能较大， 建议你上传2节点当时的ASH报告 ashrpt

lunar

顺便做一下local和remote的你那个执行异常的sql的10046
在remote设置10046的方法：
1. 远端数据库：
grant alter session to <username>;
CREATE OR REPLACE PROCEDURE enable10046
as
  begin
   execute immediate  'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT
FOREVER, LEVEL 12''';
end;
/
2. 本地数据库：
alter session set events '10046 trace name context forever,level 12';
exec enable10046@dblk
执行sql语句

yehc@epsoft.com

Maclean Liu(刘相兵 发表于 2013-11-6 21:50
2节点的TOP EVENT

Event        Waits        Time(s)        Avg wait (ms)        % DB time        Wait Class

AC01行锁的问题，目前操作的过程：

表分析、索引分析及重建索引，需要再观察

yehc@epsoft.com

lunar 发表于 2013-11-6 21:56
顺便做一下local和remote的你那个执行异常的sql的10046
在remote设置10046的方法：
1. 远端数据库：

非业务高峰时间段内，两个等待事件不明显，但如果时间段内做level 12，担心用户的意见更大。
会找个合适的时间再检查。THK！！！

Maclean Liu(刘相兵

Top Sessions
'# Samples Active' shows the number of ASH samples in which the session was found waiting for that particular event. The percentage shown in this column is calculated with respect to wall clock time and not total database activity.
'XIDs' shows the number of distinct transaction IDs sampled in ASH when the session was waiting for that particular event
For sessions running Parallel Queries, this section will NOT aggregate the PQ slave activity into the session issuing the PQ. Refer to the 'Top Sessions running PQs' section for such statistics.
Sid, Serial#        % Activity        Event        % Event        User        Program        # Samples Active        XIDs
2666, 799        15.41        SQL*Net message from dblink        12.25        LQSI                 190/720 [ 26%]        2
                 enq: TX - row lock contention        3.09                          48/720 [ 7%]        2
1713, 15        13.48        enq: TX - row lock contention        6.77        LQSI                 105/720 [ 15%]        2
                 SQL*Net message from dblink        6.19                          96/720 [ 13%]        1
2377, 1245        8.90        enq: TX - row lock contention        8.90        LQSI                 138/720 [ 19%]        2
2474, 25        8.64        SQL*Net message from dblink        6.13        LQSI                 95/720 [ 13%]        1
                 enq: TX - row lock contention        2.39                          37/720 [ 5%]        1
953, 19        8.45        enq: TX - row lock contention        8.45        LQSI                 131/720 [ 18%]        1
Back to Top Sessions
Back to Top


Top Blocking Sessions
Blocking session activity percentages are calculated with respect to waits on enqueues, latches and "buffer busy" only
'% Activity' represents the load on the database caused by a particular blocking session
'# Samples Active' shows the number of ASH samples in which the blocking session was found active.
'XIDs' shows the number of distinct transaction IDs sampled in ASH when the blocking session was found active.
Blocking Sid (Inst)        % Activity        Event Caused        % Event        User        Program        # Samples Active        XIDs
2474, 25( 2)        33.59        enq: TX - row lock contention        33.59        LQSI                 134/720 [ 19%]        1
1713, 15( 2)        14.89        enq: TX - row lock contention        14.89        LQSI                 209/720 [ 29%]        6
1429, 67( 2)        8.96        enq: TX - row lock contention        8.96        LQSI                 102/720 [ 14%]        2
2666, 799( 2)        6.38        enq: TX - row lock contention        6.38        LQSI                 239/720 [ 33%]        4


上表可以看到

session 2474本身受到 SQL*Net message from dblink的影响，有以 enq: TX - row lock contention阻塞别人

同样的 session 2666、1713、1429均是如此的


你的SQL执行的顺序 可能是

update AC01 set
然后 INSERT INTO F_AC01_ERR@YLBXDB .

由于后者受到dblink影响 从而前者锁定的行没有很快释放 ，导致TX:row lock 作为第一等待事件。


你可以查看 下 SQL*Net message from dblink 等待时间的历史 等待情况， 具体脚本在我博客上有， 来确定该 SQL*Net message from dblink慢是否是偶发情况

刷到报废

老刘和女神讲的太专业了,我扯点别的.
首先先不先入为主的以为水泡过了网络质量下降,这个东西是可以测试出来的.而且ISP好像也不愿意拿福禄克这些设备做质量测试.
ARP泛洪这个不能穿越路由设备.只在那一个广播域内有危害,消除之后不是立马看到效果.
ping这个东西的丢包率一般讲0.5%以上网络就无法使用,你的万分之一不算高,可以试一下大包ping.
traceroute看一下路由选的路OK不OK,也见过走错路的.

yehc@epsoft.com

刷到报废 发表于 2013-11-6 22:09
老刘和女神讲的太专业了,我扯点别的.
首先先不先入为主的以为水泡过了网络质量下降,这个东西是可以测试出来 ...

谢谢你的建议：
你说的这些方法我都测试过了。包括网络设备的检查，，呵呵，之前我是做网络的，所以对于简单的网络检查还是比较了解的，但这里的情况特殊，常规的方法不能检查到网络的质量。
关于ARP的问题，我也只是看到两台主机上接收到的，内部网络，跟dblink网络没有太大的关系。

545459983

我怎么没分析出来，继续关注

yehc@epsoft.com

通过几天的观察，问题的显现一如既往，跟用户协商后，果断更换了运营商线路，目前数据库的SQL*Net message from dblink和 enq: TX - row lock contention基本得到缓解。

1. Top 5 Timed Foreground Events
   
2. 
   
3. Event Waits Time(s) Avg wait (ms) % DB time Wait Class
   
4. DB CPU   698   82.46   
   
5. SQL*Net message from dblink 16,168 70 4 8.23 Network
   
6. db file sequential read 21,617 66 3 7.76 User I/O
   
7. gc cr multi block request 7,517 6 1 0.75 Cluster
   
8. gc current block 2-way 14,626 5 0 0.60 Cluster

复制代码

再次感谢各位的指导