问题处理
背景：
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; |
