【MOS】诊断 ’library cache mutex X’ 等待 (Doc ID 2331144.1)

诊断 ’library cache: mutex X’ 等待 (Doc ID 2331144.1)

Troubleshooting 'library cache: mutex X' Waits. (Doc ID 1357946.1)

适用于:

Oracle Database Cloud Schema Service - 版本 N/A 和更高版本 Oracle Database Exadata Cloud Machine - 版本 N/A 和更高版本 Oracle Database Exadata Express Cloud Service - 版本 N/A 和更高版本 Oracle Cloud Infrastructure - Database Service - 版本 N/A 和更高版本 Oracle Database Backup Service - 版本 N/A 和更高版本 本文档所含信息适用于所有平台

用途

本文档旨在说明 library cache: mutex X 等待诊断思路。

排错步骤

我们先要了解什么是 library cache: mutex X？

该机制是用于保护内存结构，在 library cache 中有许多内存结构需要 library cache: mutex X 的保护。

library cache 用来保存解析过的 cursor 相关的内存结构。

等待 library cache: mutex X 与之前版本的 latch:library cache 等待相同。library cache: mutex X 可以被很多因素引起，例如：（包括应用问题，执行计划不能共享导致的高版本的游标等），本质上都是某个进程持有 library cache: mutex X 太长时间，导致后续的进程必须等待该资源。如果在 library cache 的 latch 或者 mutex 上有等待，说明解析时有很大的压力，解析 SQL 的时间变长（由于 library cache 的 latch 或者 mutex 的等待）会使整个数据库的性能下降。

由于引起 library cache: mutex X 的原因多种多样，因此找到引起问题的根本原因很重要，才能使用正确的解决方案。

引起 library cache: mutex X 等待的原因主要有哪些呢?

• 大量的硬解析：过于频繁的硬解析，会导致该等待。
• 高版本的游标：当发生 High version count 时，大量的子游标需要检索，从而会引起该等待。
• 游标失效：游标失效是指，保存在 library cache 中的游标由于不可用，而从 library cache 中删除。游标失效是指某些改变导致内存中的游标不再有效。例如：游标相关对象的统计信息搜集；游标关联表，视图等对象的修改等。发生游标失效会导致接下来的进程需要重新载入该游标。当游标失效过多时，会导致 'library cache: mutex X' 等待。
• 游标重新载入：游标重新载入是指本来已经存在于 library cache 中，但是当再次查找时已经被移出 library cache（例如：由于内存压力），这时就需要重新解析并且载入该游标。游标重新载入操作不是一件好事，它表明您正在做一件本来不需要做的事情，如果您设置的 library cache 大小适当，是可以避免游标重新载入的。游标重新载入的时候是不可以被进程使用的，这种情况会导致 library cache: mutex X 等待。
• 已知的 Bug。

12C 及更高版本等待事件命名

12c 以后该等待又被细分为如下：

* library cache: mutex X – 用于保护 handle。

* library cache: bucket mutex X – 用于保护 library cache 中的 hash buckets。

* library cache: dependency mutex X – 用于保护依赖。

如何诊断 library cache: mutex X 等待？

1. 确认是否存在一些改变： a. 负载是否增长？ b. 是否有应用、操作系统、中间件的改变？
2. 该等待的出现的趋势： a. 确认该等待是否在每天的固定时刻产生？ b. 是否做了一些操作触发该等待？
3. 生成问题发生时刻的 AWR 和 ADDM 报告，与基线或者正常时间段的 AWR 和 ADDM 报告比较，是否有负载，参数等的改变和不同。

使用如下脚本生成问题发生时的半小时到一小时区间的 AWR 和 ADDM 报告：

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql

参考：

Document 1680075.1 How to Generate and Check an ADDM report Document 1903158.1 How to Collect Standard Diagnostic Information Using AWR Reports for Performance Issues

1. 有时 systemstate dump 是必须的，可以用来匹配已知的问题，例如：在 AWR 中没有发现明显的 SQL 时、通过 systemstate dump 捕获阻塞进程和被阻塞进程的信息，可帮助发现潜在的问题。在发生问题时使用如下命令取得systemstate dump：

(a) Non-Rac


sqlplus "/ as sysdba"

oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266
wait 90 seconds
oradebug dump systemstate 266
wait 90 seconds
oradebug dump systemstate 266
quit

(b) RAC

$ sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug setinst all
oradebug -g all hanganalyze 4
oradebug -g all dump systemstate 266
quit

1. 当已经找到阻塞进程和被阻塞进程，可以使用 Errorstack 取得相关进程信息，从而得到类似 systemstate dump 信息，来定位已知的问题。

$ sqlplus
SQL> oradebug setospid <p.spid from above>
oradebug dump errorstack 3
<< wait 1min>>
oradebug dump errorstack 3
<< wait 1min>>
oradebug dump errorstack 3
exit

得到的 trace 可以用来定位已知问题。 systemstate dump 和 errorstack 不容易理解，可以创建 SR 请技术支持工程师协助分析。

1. 有时 systemstate dump 是不适合收集的，因为它消耗资源较多。这时定期执行如下 SQL，来确定哪些进程和 SQL 在等待 library cache: mutex X。

select s.sid, t.sql_text
from v$session s, v$sql t
where s.event like '%mutex%'
and t.sql_id = s.sql_id

1. 11G RAC 中有比 systemstate dump 使用更少资源的其它的工具可以使用搜集信息：

Document 459694.1 Procwatcher: Script to Monitor and Examine Oracle DB and Clusterware Processes

如何查看取得的诊断信息呢？

1. 正常情况下，我们可以从 AWR 中看到 library cache: mutex X 是 TOP 事件：

image-20240412174548786

1. 定位出硬解析和高版本的 SQL，点击“Main Report”下的“SQL Statistics”链接

image-20240412174559935

之后点击“SQL ordered by Parse Calls”和“SQL ordered by Version Count”

image-20240412174611922

定位解析比较高的 SQL：

注意比较高的解析比例的 SQL，理想情况下解析和执行的比例应该很低，如果该比例很高说明应用中没有很好的使用游标，游标解析并且打开之后应该保持打开状态，与开发人员确认如何保持游标打开，避免下次执行该 SQL 时重复解析。

下一步检查 SQL 高版本：

通过如上的列表中找到 SQL 版本较高的 SQL，可以通过 V$SQL_SHARED_CURSOR 检查引起 SQL 高版本的原因。

Document 438755.1 Formated V$SQL_SHARED_CURSOR Report by SQLID or Hash ValueDocument 296377.1 Troubleshooting: High Version Count Issues

可能的解决方案：

1. 检查是否存在较高的硬解析，因为硬解析会引起 SQL AREA 的重新装载，通过 load profile 确定硬解析的数量。

image-20240412174654241

该信息表明每秒会有26.3次的硬解析，这表明硬解析很高。需要检查应用是否正确使用了绑定变量，此外可以参考如下文档的“Over Parsing”部分。

Document 33089.1 TROUBLESHOOTING: Possible Causes of Poor SQL Performance

对于 SQL AREA 的重新加载也要进行检查：

image-20240412174703178

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

Document 62143.1 Understanding and Tuning the Shared Pool

1. 在“Library Cache Activity”下检查 invalidations，如果 invalidations 过高，需要确认是否有大量的 DDL 操作，例如: truncate, drop, grants, dbms_stats 等。
2. 根据如下文档定位相关的 Bug。

Document 727400.1 WAITEVENT: "library cache: mutex X"

1. 对于 11G，确认 cursor_sharing 不是 similar，因为该值已经不建议使用，并且会引起 mutex X 等待。

Document 1169017.1 ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting

1. 如果数据库从 10G 升级到 11G 后，遇到 mutex 的问题，请考虑升级到 11.2.0.2.2 以上的 PSU 来修复未发布的 Bug12431716，很多关于 mutex 的修复已经包含在该 Bug 中。

诊断 11G 之后的 library cache: mutex X 问题诊断，参照如下文档：

Document 2051456.1 Troubleshooting Databases Hang Due to Heavy Contention for 'library cache: mutex X' Waits (Oracle 11.2 and Later)

参考

NOTE:1291879.1 - Oracle Database Patch Set Update 11.2.0.2.2 Known Issues NOTE:1169017.1 - ANNOUNCEMENT: Deprecating the Cursor_Sharing = 'SIMILAR' Setting NOTE:296377.1 - Troubleshooting: High Version Count Issues NOTE:33089.1 - * TROUBLESHOOTING: Possible Causes of Poor SQL Performance NOTE:459694.1 - Procwatcher: Script to Monitor and Examine Oracle DB and Clusterware Processes

NOTE:62143.1 - Troubleshooting: Understanding and Tuning the Shared Pool NOTE:727400.1 - WAITEVENT: "library cache: mutex X" NOTE:438755.1 - High SQL Version Counts - Script to determine reason(s) NOTE:2051456.1 - Troubleshooting Databases Hang Due to Heavy Contention for 'library cache: mutex X' Waits (Oracle 11.2 and Later)