原 客户某套Oracle rac业务库出现严重的library cache等待排查

发布日期 · 已更新  

0    294    2

Tags：

👉 本文共约5455个字，系统预计阅读时间或需21分钟。

简介

数据库版本：Oracle rac 19.9 EE

出问题时间段： 2024.04.07 16:48 到 17:00

核心数据库出现大量的enq: TX - row lock contention、library cache lock、library cache: mutex X、cursor: mutex S、cursor: mutex X等待。

分析方法

由于系统在17点已恢复正常，所以我们可以通过AWR、ASH、ADDM及一些系统视图来进行分析发生问题的时间段的数据库性能。

对应的AWR 快照范围为：29323 to 29324 ，对应16点到17点。

排查分析过程

排查1 ASH视图排查

查询等待事件统计：

会话阻塞情况：

排查2 ADDM报告分析

Top SQL Statements占用百分比最高，其中占用最高的SQL_ID "cn9fqrd5w0841" ，占用最高的等待事件为library cache lock和 "library cache: mutex X"，其SQL语句如下（已做脱敏）：

ADDM结论：

1、 热表为ABT_CDD_ILTO，经查，该表为分区表，有分区索引，为一个大表，大量并发做for update引起enq: TX - row lock contention等待

2、 UPDATE操作SQL_ID "cn9fqrd5w0841" ，占用最高的等待事件为library cache lock和 "library cache: mutex X"，需要通过AWR等其它手段继续排查

排查3 AWR和ASH报告

ASH部分：

对于library cache lock等待事件，这里的p3值是5373954，16进制为00520002，前面的4位0052代表namespace,后面的4位0002代表mode，而0052的10进制为82，82对应的NAMESPACE为SQL AREA BUILD，通常是由于大量解析导致。

等待事件主要和库缓存及SQL解析相关，所以AWR主要分析library和parse部分：

查看Library Cache Activity部分，发现Invali- dations的值特别高，达到了26497，猜测和游标失效有关，需要确认在发生问题的时间段对热表是否有 DDL 操作，例如: truncate, drop, grants, dbms_stats，alter 等操作。

Reloads值也很高，可以考虑增大shared pool的大小。

如果在 SQL AREA 上的重新加载次数很高，那么需要检查游标是否被有效共享（重新加载的次数是指被缓存在 shared pool 中，但是使用时已经不在 shared pool 中）。如果游标已经有效共享，那么需要确认 shared pool 和 sga_target 是否足够大，如果 shared pool 有压力而没有足够的空间，那么有些缓存的游标会被从 shared pool 中清除。如果游标共享不充分，shared pool 会被这些不能被重用的游标占满，从而把那些可以重用的游标挤出 shared pool，进而引起在这些 SQL 重新执行时需要重新加载。游标共享充分，但由于 shared pool 空间过小也会引起可重用的游标被清除从而引发硬解析。不过最常见的情况还是游标无法共享。

继续查看游标的Version Count值：

如果version count大于200，则执行次数Executions列默认不收集（Why "Executions" and "Elap per Exec(s)" are 'Blank' in AWR for Some SQL Statements (Doc ID 1522547.1)）

由于SQL的子游标过多引起SQL解析时遍历library cache object handle链表需要很长时间，造成了library cache: mutex x等待。

子游标不共享导致出现很多的Version Count，但是子游标不共享的原因有很多，在MOS 438755.1中，Oracle提供了一个专门的脚本程序version_rpt.sql，用于协助诊断High Version Count问题。 运行完脚本后，可以使用如下的SQL分析：

其中为YES的都是发生了BIND_MISMATCH和BIND_LENGTH_UPGRADEABLE导致的不能共享。

这里做简单说明：

● BIND_MISMATCH :4873 ==> 绑定变量的类型、长度、精度等属性发生变化导致绑定变量分级

● ROLL_INVALID_MISMATCH :1528 收集统计信息导致，_optimizer_invalidation_period默认5小时（18000s）或其它DDL操作导致统计信息失效

● BIND_LENGTH_UPGRADEABLE :1745 ==> 绑定变量的长度问题导致绑定变量分级

● PURGED_CURSOR :55 ==> 被标记为清除的游标。该游标已被标记为使用dbms_shared_pool.purge进行了清除。

那为什么子游标会出现这么多呢，而之前的11g就从没出现过这么多呢？

从上边的报告输出，可以看到子游标过大，也涉及一个参数_cursor_obsolete_threshold = 8192，作用是其作用是当SQL版本超过这个参数设定后，直接舍弃这个游标，重新解析，从头开始。如果子游标的数量超过了这个阈值，那么父游标就会被废弃，并且同时重新创建一个新的父游标。

该隐含参数从12.2数据库版本开始增加到8192。这会导致父游标不会被废弃，因此父级下的子游标会扩展到1024以上（这是12.1中的默认值），从而导致cursor mutex的并发问题。

从12.2开始，_cursor_obsolete_threshold的默认值大幅增加（从1024开始为8192）以便支持4096个PDB（而12.1只有252个PDB）。 此参数值是在多租户环境中废弃父游标的最大限制，并且不能超过8192。

但这个设置并不适用于非CDB环境，因此对于那些数据库，此参数应手动设置为12.1的默认值，即1024. 默认值1024适用于非CDB环境，并且如果出现问题，可以调整相同的参数，应视具体情况而定。

_cursor_obsolete_threshold首先在11.2.0.3中引入，默认值为100，然后在11.2.0.4中增加到1024

11.2.0.3: _cursor_obsolete_threshold=100

11.2.0.4: _cursor_obsolete_threshold=1024

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
AiDBA后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！
验证码：
获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签：

Avatar photo

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复