ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 子游标过多导致大量mutex争用故障分析

子游标过多导致大量mutex争用故障分析

原创 Oracle 作者：jason_yehua 时间：2023-12-25 14:14:49 0 删除 编辑

问题处理

背景：

2023 / 04/02 日中 午11 点 30 左右 ，接到 jzdb3 库 节点 1 数据库 性能差， awr 中出现 严重 的 cursor: mutex S\ cursor: mutex X 争用 。

SR 3-14602160331 :   A Large number of child cursor caused the 'cursor: mutex S' and 'cursor: mutex X'

SSC 通过现场对 DB 、 RAC 、 OS 、 SQLHC 、 SQL version_rpt 等数据做了详细的原因分析。

救援 过程

1 、 AWR 报告分析

现场 抓取了 今天 一整天的AWR 日志， 从 AWR 追溯到从凌晨 1 点 TOP Events 中 开始出现cursor: mutex S 、 cursor: mutex X 争用：  

下一个半个小时 的awr 开始 持续 处于 event 的前列：

分析 造成 该 event 冲高的 SQL为 下面两条 SQL导致 的：

SQL ID:  au0v7hv343q45\

具体 的 SQL TEXT:

SQL ID : au0v7hv343q45:

select  to_char(account_id),

       acctbk_id,

       to_char(last_balance),

       to_char(current_balance),

       status,

       to_char(start_date, 'yyyymmddhh24miss' ),

       to_char(end_date, 'yyyymmddhh24miss' ),

       unit,

       to_char(expire_date, 'yyyymmddhh24miss' ),

       to_char(join_id),

       to_char( version )

   from  acctbk

  where  account_id = to_number(:v_account_id)

    and  acctbk_id = :v_acctbk_id

 

SQL ID:

select  to_char(account_id),

       acctbk_id,

       to_char(last_balance),

       to_char(current_balance),

       status,

       to_char(start_date, 'yyyymmddhh24miss' ),

       to_char(end_date, 'yyyymmddhh24miss' ),

       unit,

       to_char(expire_date, 'yyyymmddhh24miss' ),

       to_char(join_id),

       to_char( version )

   from  acctbk

  where  account_id = to_number(:v_account_id)

    and  acctbk_id = :v_acctbk_id

    for   update   nowait

 

  均为 对acctbk 表 的 查询 操作 。

2 、 Version Count Report 分析

分析 SQL C ount ：

au0v7hv343q45 ：

 

发现造成子游标不能共享的原因主要有 LANGUAGE_MISMATCH 、 ROLL_INVALID_MISMATCH 子游标 数目比较高，而且一直没有age out 出去 。

于是 我们使用下面命令收集统计信息：

exec dbms_stats.gather_table_stats(ownname => 'accounting',tabname => 'acctbk',estimate_percent => 5,degree => 4,cascade => true, no_invalidate => false )

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:07:47.44

该命令 执行了 7 分 多钟， no_invalidate 参数 设置为false ， 目的 是 让表 acctbk 对应 的 SQL 的 所有子游标 立即 失效，并age out 出 share pool 。

执行 完该 命令 之后，再取awr 报告， c ursor: mutex S 、 cursor: mutex X 消失 ，系统恢复正常。

分析过程

该故障分析 的焦点问题为：

1、  大量 LANGUAGE_MISMATCH 、 ROLL_INVALID_MISMATCH 、 BIND_MISMATCH 如何产生 的。

2、  为什么产生 了这么多的 mismatch 的子游标又不能及时 的 被age out 出去 。

针对第一个 问题，我们逐个做了分析：

1、  产生 原因

【 LANGUAGE_MISMATCH 】

LANGUAGE_MISMATCH 与 环境设置 NLS_* 有关系 ， 比如 客户端session 中 使用 了不同的NLS_SORT 等。  

TEST CASE:

a lter system flush shared_pool;

create table X as select * from dba_tables;

set time on pages 1000 lin 200

col FIRST_LOAD_TIME format a20

col last_load_time format a20

var b1 varchar2(128);

var b2 varchar2(128);

exec :b1 := '0';         

exec :b2 := '0';

nls_sort

alter session set = 'SCHINESE_RADICAL_M';

select count(*) from X where table_name=:b1 and OWNER=:b2;

--- 修改 nls_sort

alter session set nls_sort = 'SCHINESE_STROKE_M';

select count(*) from X where table_name=:b1 and OWNER=:b2;

-- 修改 nls_sort

alter session set nls_sort = 'SCHINESE_PINYIN_M';

select count(*) from X where table_name=:b1 and OWNER=:b2;

-- 查询 SQL id ：

09:12:41 SQL> select sql_id from v$sql where sql_text='select count(*) from X where table_name=:b1 and OWNER=:b2';

SQL_ID

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

1uk1h0wuzcuaf

 

09:14:53 SQL> select sql_id,child_address,language_mismatch,roll_invalid_mismatch,BIND_MISMATCH from v$sql_shared_cursor where sql_id='1uk1h0wuzcuaf';

SQL_ID        CHILD_ADDRESS    L R B

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

1uk1h0wuzcuaf 00000000750EDBB0 N N N

1uk1h0wuzcuaf 0000000079F0C748 Y N N

1uk1h0wuzcuaf 0000000075F5F6A0 Y N N

  【 ROLL_INVALID_MISMATCH 】

在统计信息收集的时候，默认情况下 dbms_stats 的 no_invalidate 参数为 auto_invalidate ，意思 是 收集 完统计信息之后， 不 立即失效 统计 对象 相关 的cursor ，而是 采用滚动 失效 的方式 。这种做法 是为了 避免 统计信息收集之后产生大量的硬解析 。数据库会随机 的取一个小于_optimizer_invalidation_period 值来判断子游标的 INVALIDATE 时间，默认情况下是 18000 秒，即 5 个小时。

18000s 并不是从统计信息收集开始（下图 中的 T0 ），而是从统计信息收集之后该 SQL 的第一次解析时间开始（ T1 ）， 然后随机的取一个时间，该时间小于_optimizer_invalidation_period 设定 的时间 ，我们 可以定义为下图的 T max 。

下次 执行的时候 （ T2 ）， 如果 T2 小于 T max ，就会复用以前的子游标，如果 T2 大于 T max ， 则 会执行硬解析 。

 

TEST CASE:

alter system flush shared_pool;

create table X as select * from dba_tables;

set time on pages 1000 lin 200

col FIRST_LOAD_TIME format a20

col last_load_time format a20

alter system set "_optimizer_invalidation_period"=120;

09:38:56 SQL> select child_number,parse_calls,executions,first_load_time, last_load_time,last_active_time from v$sql where sql_id='95rckg79jgshh';

CHILD_NUMBER PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME      LAST_LOAD_TIME       LAST_ACTIVE_TIME

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

0           3          3 2023-04-06/09:35:47  2023-04-06/09:35:47  06/04/2023 09:38:55

09:39:00 SQL>  exec dbms_stats.gather_table_stats(null,'X');

PL/SQL procedure successfully completed.

09:39:23 SQL> select last_analyzed from dba_tables where table_name='X';

LAST_ANALYZED

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

06/04/2023 09:39:23

09:39:38 SQL> select count(*) from X;

COUNT(*)

----------

2341

 

09:43:58 SQL> select child_number,parse_calls,executions,first_load_time, last_load_time,last_active_time from v$sql where sql_id='95rckg79jgshh';

CHILD_NUMBER PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME      LAST_LOAD_TIME       LAST_ACTIVE_TIME

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

0           4          4 2023-04-06/09:35:47  2023-04-06/09:35:47  06/04/2023 09:39:46

1           1          1 2023-04-06/09:35:47  2023-04-06/09:43:57  06/04/2023 09:43:57

 

09:44:01 SQL> select sql_id,child_address,language_mismatch,roll_invalid_mismatch,BIND_MISMATCH from v$sql_shared_cursor where sql_id='95rckg79jgshh';

SQL_ID        CHILD_ADDRESS    L R B

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

95rckg79jgshh 00000000748609A0 N N N

95rckg79jgshh 00000000751A7820 N Y N

【 BIND_MISMATCH 】

一般是由于bind value 的长度不同导致 bind buffer 无法重用，最终导致 cursor 无法重用。

2 、 为什么不被清理

经过分析 和多轮测试发现：

ROLL_INVALID_MISMATCH 为 Y 的子 游标， 在统计信息 收集之后， 如果使用 默认的NO_INVALIDATE=   AUTO_INVALIDATE ， 过 一段时间 之后，会被置 为 invalidations ， 并有 特 定 的 机制保 证 它及时的age out 出去。 在 12 c 中 ，未发现无法清理 的bug 存在 ，假设 有，那么acctbk 表所 涉及到的所有 SQL 都应该会 出现该 问题 ，从 记账库 3 查询的情况来看，是可以排除该可能性的。

、 BIND_MISMATCH 为 客户端 编码 导致的， 当 一个 子 游标的LANGUAGE_MISMATCH/ BIND_MISMATCH 被标记成 Y 之后， 在 的object_status 就 一直为valid 状态 ， 数据库 认为他还是有可能被重新 复用 的， 只有当该对象在 统计信息收集的时候 ， 使用NO_INVALIDATE=>false 参数，该 状态才能变成INVALID_UNAUTH ， 在下次 SQL 被 执行之后 才能 age  out 出去 。

另外 从 目前记账库上 查询的情况来看 ，大部分子 cursor 的 ROLL_INVALID_MISMATCH 、 LANGUAGE_MISMATCH 栏位 同时为 Y ， V $SQL 的 object_status 为 valid ， 这 就能解释了 ROLL_INVALID_MISMATCH 不能 被age out 的 原因 ：

SQL>select sql_id,child_address,language_mismatch,roll_invalid_mismatch,BIND_MISMATCH from v$sql_shared_cursor where sql_id='6uvmkxmcvbc6t';

    SQL_ID            CHILD_ADDRESS        LANGUAGE_MISMATCH ROLL_INVALID_MISMATCH BIND_MISMATCH    

    6uvmkxmcvbc6t     C000002357553CE8            Y            N                     Y    

    6uvmkxmcvbc6t     C00000235749AC10            Y            N                     Y    

    6uvmkxmcvbc6t     C000002353906DA0            Y            N                     Y    

    6uvmkxmcvbc6t     C000002354557798            Y            N                     Y    

    6uvmkxmcvbc6t     C0000021BFEDCC50            Y            Y                     N    

    6uvmkxmcvbc6t     C0000021BF661238            Y            Y                     N    

    6uvmkxmcvbc6t     C0000021BE4973D8            Y            Y                     Y    

    6uvmkxmcvbc6t     C0000021BDF0F488            Y            Y                     N    

    6uvmkxmcvbc6t     C0000020DBE75628            Y            Y                     N    

    6uvmkxmcvbc6t     C0000020DB9FA1A8            Y            Y                     N    

    6uvmkxmcvbc6t     C0000020DB9F2FC8            Y            Y                     N    

    6uvmkxmcvbc6t     C0000020DB7E7C60            Y            Y                     N    

    6uvmkxmcvbc6t     C00000226B498B18            Y            Y                     N    

    6uvmkxmcvbc6t     C000001FEC10BE90            Y            Y                     N    

    6uvmkxmcvbc6t     C000002269971508            Y            Y                     N    

    6uvmkxmcvbc6t     C000002265912D38            Y            Y                     N    

    6uvmkxmcvbc6t     C0000020D32857D8            Y            Y                     N    

    6uvmkxmcvbc6t     C0000020D3C625B8            Y            Y                     Y    

    6uvmkxmcvbc6t     C0000020D01DF1D0            Y            Y                     N    

    6uvmkxmcvbc6t     C0000020CFE39790            Y            Y                     N    

    6uvmkxmcvbc6t     C00000226A5A8D80            Y            Y                     N    

    6uvmkxmcvbc6t     C00000226B529B90            Y            Y                     N    

    6uvmkxmcvbc6t     C00000226B53B440            Y            Y                     N    

    6uvmkxmcvbc6t     C000002268732578            Y            Y                     N    

    6uvmkxmcvbc6t     C0000020CD03B6C8            Y            Y                     Y    

    6uvmkxmcvbc6t     C0000020C6D79578            Y            Y                     N    

    6uvmkxmcvbc6t     C000002351AFBB78            Y            Y                     Y    

    6uvmkxmcvbc6t     C0000020C4E37F08            Y            Y                     N

 

SQL>select child_number,LAST_ACTIVE_TIME,LAST_LOAD_TIME,OBJECT_STATUS from v$sql where sql_id='6uvmkxmcvbc6t' order by LAST_ACTIVE_TIME;

      CHILD_NUMBER    LAST_ACTIVE_TIME         LAST_LOAD_TIME            OBJECT_STATUS      

      1               2023/4/1 17:04:57        2023-04-01/17:04:57            VALID      

      2               2023/4/2 9:01:35         2023-04-01/17:06:01            VALID      

      3               2023/4/2 9:02:27         2023-04-01/17:06:01            VALID      

      0               2023/4/2 11:19:38        2023-04-01/17:04:57            VALID      

      6               2023/4/3 1:53:15         2023-04-02/11:20:01            VALID      

      5               2023/4/3 1:54:21         2023-04-02/09:55:21            VALID      

      7               2023/4/3 3:48:58         2023-04-02/12:39:23            VALID      

      4               2023/4/3 6:00:42         2023-04-02/09:02:28            VALID      

      11              2023/4/3 23:16:10        2023-04-03/06:17:07            VALID      

 

故障 分析 总结

基于 上述分析， 客户端环境 变量 、 游标使用不规范导致了mismatch 的子游标过多，进而导致了 cursor: mutex S 、 cursor: mutex X 争用， 性能出现问题， 因为 客户端环境比较复杂，修改起来需要一个过程，目前 数据库 端能做的workaround 有如下 ：

1、  定期 监控游标过多的 SQL ，子游标 超过 200 的， 收集统计信息的时候 使用 no_invalidate=false 参数。

或者为有 上述 问题 的 表 特别的指 定 统计信息收集属性， 设置 no_invalidate => false ， 方法 如下 ：

SQL> SELECT DBMS_STATS.GET_PREFS('NO_INVALIDATE','SYS','X') FROM DUAL;

 

DBMS_STATS.GET_PREFS('NO_INVALIDATE','SYS','X')

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

DBMS_STATS.AUTO_INVALIDATE

 

SQL>

 

PL/SQL procedure successfully completed.

 

SQL> SELECT DBMS_STATS.GET_PREFS('NO_INVALIDATE','SYS','X') FROM DUAL;

 

DBMS_STATS.GET_PREFS('NO_INVALIDATE','SYS','X')

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

FALSE

2、  当 出现子游标过多的时候，执行如下命令将该 SQL 的所有 子游标清除出去

SQL> alter session set events '5614566 trace name context forever'  --oracle10g 需要执行  

 

SQL>select address,hash_value,executions,parse_calls from v$sql where sql_id='3yr4fwqux2buz';

 

SQL> . e('00000000749AF8B8,3050385247','C');

 

- -   参数 1 ： SQL address ；参数 2 ：  hash_value ；参数 3 ： C 表示 PURGE 的对象是 CURSOR

 

3、  修改每个 sql 允许生成子游标 的总个数 （ 12 c 版本默认值为 1024 ）

SQL>alter system set " _cursor_obsolete_threshold  "=200;

 


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31547506/viewspace-3001399/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 数据库监听夯故障分析
下一篇： ADG 实例异常终止故障分析报告
请登录后发表评论 登录
全部评论
jason_yehua

注册时间：2019-05-22

  • 博文量
    122
  • 访问量
    99626

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593