绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
dblink不稳定造成 cursor: pin s wait on x等待事件
2021-05-28 16:40:52

该系统刚刚上线,需要监控运行情况,发现每天晚上DBTime都会超过阀值,经过分析发现大量的cursor: pin s wait on x,和dblink等待,发生cursor: pin s wait on x等待的SQL无法捕获,我们从分析应该是执行的SQL有DBlink,而问题时段告警日志大量的Dblink故障告警,我们模拟该问题,从而验证我们的分析。

每错误执行一次,告警日志报错如下


Fatal NI connect error 12541, connecting to:

 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test2)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=prod)(CID=(PROGRAM=oracle)(HOST=test1)(USER=oracle))))


  VERSION INFORMATION:

        TNS for Linux: Version 12.1.0.2.0 - Production

        Oracle Bequeath NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production

        TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production

  Time: 22-MAY-2021 02:27:25

  Tracing not turned on.

  Tns error struct:

    ns main err code: 12541

    

TNS-12541: TNS:no listener

    ns secondary err code: 12560

    nt main err code: 511

    

TNS-00511: No listener

    nt secondary err code: 111

    nt OS err code: 0


捕获语句,可以不关注

select sql_id,event,count(*) from v$active_session_history group by sql_id,event order by count(*);

频繁执行的并发操作,并发执行如下

SQL> declare

  2   v_sql varchar2(4000);

  3  

  4  begin

  5  for i in 1 .. 100000

  6  loop

  7  v_sql :='select * from emp@scottlink where rownum<20';

  8  execute immediate v_sql;

  9  end loop;

 10  end;

 11  /

declare

*

ERROR at line 1:

ORA-12543: TNS:destination host unreachable

ORA-06512: at line 8

等待事件

SQL> /


EVENT                            COUNT(*)

------------------------------ ----------

cursor: pin S wait on X                 2


并发执行如下,反复执行


SQL> declare

  2   v_sql varchar2(4000);

  3  

  4  begin

  5  for i in 1 .. 100000

  6  loop

  7  v_sql :='select * from emp@scottlink where rownum<10';

  8  execute immediate v_sql;

  9  end loop;

 10  end;

 11  /

declare

*

ERROR at line 1:

ORA-12543: TNS:destination host unreachable

ORA-06512: at line 8


继续查等待事件等待事件增加



SQL> /


EVENT                            COUNT(*)

------------------------------ ----------

cursor: pin S wait on X                 8


SQL> select sql_id,event,count(*) from v$active_session_history where event like 'cursor%'  group by sql_id,event order by count(*);


SQL_ID                     EVENT                            COUNT(*)

-------------------------- ------------------------------ ----------

gf49q7pt8kz72              cursor: pin S wait on X                 2

4p16kbxk5f274              cursor: pin S wait on X                 6


SQL> l

  1* select sql_id,event,blocking_session ,count(*) from v$active_session_history where event like 'cursor%'  group by sql_id,event,blocking_session  order by count(*)

SQL> /


SQL_ID                     EVENT                          BLOCKING_SESSION   COUNT(*)

-------------------------- ------------------------------ ---------------- ----------

4p16kbxk5f274              cursor: pin S wait on X                      58          2

gf49q7pt8kz72              cursor: pin S wait on X                      58          2

4p16kbxk5f274              cursor: pin S wait on X                      59          4

而这些BLOCKING会话就是反复执行相同查询的会话 



针对SQL我们查询

SQL> select sql_id,event,count(*) from v$active_session_history where event like 'cursor%'  group by sql_id,event order by count(*);


SQL_ID                     EVENT                            COUNT(*)

-------------------------- ------------------------------ ----------

gf49q7pt8kz72              cursor: pin S wait on X                 2

4p16kbxk5f274              cursor: pin S wait on X                 6


SQL> select sql_text from v$sql where sql_id='gf49q7pt8kz72';


no rows selected


SQL> select sql_text from v$sql where sql_id='4p16kbxk5f274';


no rows selected


开启DBlink

SQL> select * from emp@scottlink where rownum<10;

SQL> select * from emp@scottlink where rownum<20;


SQL> select sql_id ,sql_text from v$sql where sql_text like 'select *%';


SQL_ID                     SQL_TEXT

-------------------------- --------------------------------------------------

4p16kbxk5f274              select * from emp@scottlink where rownum<10

gf49q7pt8kz72              select * from emp@scottlink where rownum<20


我们看到dblink异常时刻被阻塞的SQL,就是当dblink正常时查询的SQL。这也验证了我们的判断

即: dblink异常时刻,sql做解析,但是由于无法获得对象信息,无法成功,但是依然需要持有一个内存块预放置该SQL对象的解析信息,此时会持有X锁,当其他会话

发起同样的查询,会先尝试判断是否存在造成S锁等待,这就是DBlink问题造成的cursor: pin s wait on x等待事件的原因。

问题如何解决: 保持稳定的dblink连接。



分享好友

分享这个小栈给你的朋友们,一起进步吧。

Oracle运维新鲜事-技术与管理各占半边天
创建时间:2020-08-04 11:34:57
本技术栈旨在分享技术心得,运维趣事,故障处理经验,调优案例,故障处理涉及集群,DG,OGG,大家生产中遇到的问题基本都会囊括了,我会发布生产库遇到的故障,希望在交流中互助互益,共同提高,也希望大家讨论,如果您有生产中遇到的集群问题,也可以在这里提出来,一起讨论,现实中也帮助不少同学解决了生产库的故障。
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

栈主、嘉宾

查看更多
  • Abraham林老师
    栈主
  • 小雨滴
    嘉宾
  • hawkliu
    嘉宾
  • u_97a59a25246404
    嘉宾

小栈成员

查看更多
  • 栈栈
  • dapan
  • 小菜鸟___
  • hwayw
戳我,来吐槽~