【MOS】'library cache lock' 等待事件 原因和解决方案 (Doc ID 2896611.1)

2024-04-15 11:02:17 浏览数 (2)

简介

'library cache lock' Waits: Causes and Solutions (Doc ID 1952395.1)

'library cache lock' 等待事件: 原因和解决方案 (Doc ID 2896611.1)

适用于:

Oracle Database - Standard Edition - 版本 8.1.7.4 和更高版本 Oracle Database - Enterprise Edition - 版本 8.1.7.4 和更高版本 Oracle Database - Personal Edition - 版本 8.1.7.4 和更高版本 本文档所含信息适用于所有平台

用途

疑难解答 'library cache lock' 等待事件。

注意：本文中的信息来自 Oracle Performance Diagnostic Guide (OPDG)。

Document 390374.1 Oracle Performance Diagnostic Guide (OPDG)

文档还包含其他类型的等待事件的诊断。

library cache lock 等待事件

library cache lock通过获取对象句柄上的锁来控制 library cache 客户端之间的并发性，为了：

  • 一个客户端可以阻止其他客户端访问同一个对象
  • 客户端可以长期保持依赖关系 (没有其他客户端可以更改对象).

该锁也是在library cache中定位对象操作的一部分（获取library cache子锁以扫描句柄列表，然后在找到对象后将锁放置在句柄上）。

等待事件发生导致出现问题时的确认方法:

  • TKProf:
  • non-recursive 以及 recursive statements 的 Overall wait 中显示较多的 library cache lock 等待。
  • AWR 或者 statspack:
  • 显示较多的 library cache lock 等待。

原因 : 使用常量（Literals）导致SQL没有被共享

如果在 SQL 语句的条件规范中使用常量而不是绑定变量，则 SQL 语句将不会被共享，并且需要进行硬解析。

确认方法

TKProf :

  • 按照解析时间进行排序（elapsed parse time）。
  • 确认 Top 的语句并确定它们是否被硬解析, 如果发生硬解析，"Misses in the library cache" 的值将接近 Parse 的回数。
  • 检查是否在发生硬解析 SQL 语句的条件范式中使用了常量(Literals)。
解决方案: 以使用绑定变量的方式重写SQL

通过使用绑定变量的方式重写SQL语句，会将仅有条件值不同的SQL语句视为相同的，并且可以共享。

这是提升 Library cache 中SQL语句共享的最佳方式。

努力细节 : 中或者高; 应用程序端将需要重写 SQL 语句以将常量（Literals）部分更改为绑定变量。** 风险细节** : 中; 使用绑定变量重写SQL之后，某些SQL可能无法选择最优的执行计划。您应该对重写后的SQL进行测试，以判断是否有性能损失。

解决方案实施

参考如下文档：

故障排除

Document 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention Document 296377.1 Troubleshooting: High Version Count Issues

文档

7.3.1.3 SQL Sharing Criteria

** 实施验证： **

检查性能是否有所改善。如果没有看到性能改善，请检查以下内容：

  • 查看其他可能的原因
  • 验证数据收集是否正确完整
  • 仔细检查有问题的 SQL 语句

如果您想提出一个服务请求，提供一份测试用例将非常有帮助。

解决方案: 使用初始化参数 CURSOR_SHARING

通过设置 CURSOR_SHARING 参数，将SQL语句中的常量（Literals）部分自动替换为绑定变量。可以为参数设置以下值：

  • EXACT: 常量（Literals）部分按原来的方式执行。（默认值）
  • FORCE: 将所有定量（Literals）部分替换为绑定变量。（尽可能）
  • SIMILAR: 仅当执行计划无法更改时，才用绑定变量替换常量（Literals）部分。（例如：安全的常量替换）

通常,经常使用等价条件的 OLTP 应用程序的执行计划变化不大，但这些参数的效果应该在应用程序中测试。 此参数可以在会话级别设置，建议使用以最大程度地减少影响。

努力细节 : 低；需要修改 init.ora/spfile，最坏的情况需要一个LOGON触发器来设置会话级参数。 风险细节 : 中；替换为绑定变量可能会影响某些 SQL 选择最佳执行计划。使用 SIMILAR 代替 FORCE 可以降低这种风险，但 SQL 语句共享的效果会打折扣。

解决方案实施

参考如下文档：

参考

Reference: CURSOR_SHARING Parameter

Document 94036.1 Init.ora Parameter "CURSOR_SHARING" Reference Note

故障排除

CURSOR_SHARING for Existing Applications

Document 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention Document 296377.1 Troubleshooting: High Version Count Issues

文档

7.3.1.3 SQL Sharing Criteria

** 实施验证： **

检查性能是否有所改善。如果没有看到性能改善，请检查以下内容：

  • 查看其他可能的原因
  • 验证数据收集是否正确完整
  • 仔细检查有问题的 SQL 语句

如果您想提出一个服务请求，提供一份测试用例将非常有帮助。

原因: 共享的SQL语句过期

如果共享池太小，共享游标将从library cache中移除，下次使用时需要重新加载。重新加载时需要硬解析，这会导致CPU资源消耗和锁的竞争。

确认方法

TKProf:

  • 按照解析时间进行排序（elapsed parse time）。
  • 确认 Top 的语句并确定它们是否被硬解析, 如果发生硬解析，"Misses in the library cache" 的值将接近 Parse 的回数。
  • 如果发生硬解析的SQL语句中并没有使用常量(Literals)，则可能由于从 library cache 中移除了本来可以共享的SQL语句。(这并不是绝对的，因为也可能有使用绑定变量但不会再次执行的SQL语句)。

AWR 或者 statspack 报告:

  • Library Cache statistics 部分显示 reloads 很高（每小时几千次）,并且invalidations很少。
  • "% SQL with executions>1" 超过60%,SQL语句的共享率比较高。
解决方案: 增加共享池的大小

增加共享池可以让共享游标被共享而不是被排除。

努力细节 : 低; 需要修改 init.ora / spfile. 风险细节 : 低; 增加共享池的大小没有风险，除非:

  • 由于使用了常量（Literals）导致很多不能共享的SQL被执行： Document 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention
  • 物理内存不足swap交换过多： Document 17094.1 TECH: Unix Virtual Memory, Paging & Swapping explained

在更改共享池的大小之前需验证以上几点。

解决方案实施

参考如下文档：

文档

Admin: Using Manual Shared Memory Management, see Specifying the Shared Pool Size Reference: SHARED_POOL_SIZE Parameter Reference: SHARED_POOL_SIZE and Automatic Storage Management

实施验证

检查性能是否有所改善。如果没有看到性能改善，请检查以下内容：

  • 查看其他可能的原因
  • 验证数据收集是否正确完整
  • 仔细检查有问题的 SQL 语句

如果您想提出一个服务请求，提供一份测试用例将非常有帮助。

解决方案: 10g : 使用自动共享内存管理器 (ASMM) 调整共享池大小

ASMM 将自动调整共享池的内存大小，以确保可用的最佳大小。您需要为 SGA_MAX_SIZE 和 SGA_TARGET 设置一个合理的值来启用 ASMM。

努力细节 : 低; 需要修改 init.ora/spfile。 风险细节: 低; ASMM 将确保有足够的内存可用。

解决方案实施

参考如下文档：

在线文档

Concepts: Memory Architecture Concepts: Automatic Shared Memory Management Admin: Using Automatic Shared Memory Management Performance Tuning: Tuning the Shared Pool and the Large Pool

MOS文档

Document 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention Document 257643.1 Oracle Database Automated SGA Memory Tuning

How-To

Document 295626.1 How To Use Automatic Shared Memory Management (ASMM) In Oracle 10g & 11g Document 270935.1 Shared pool sizing

实施验证：

检查性能是否有所改善。如果您没有看到性能改善，请检查以下内容：

  • 查看其他可能的原因
  • 验证数据收集是否正确完整
  • 仔细检查有问题的 SQL 语句

如果您想提出一个服务请求，提供一份测试用例将非常有帮助。

解决方案: 将频繁使用的较大的PL/SQL对象或者游标保持在共享池中（Pin）

可以使用 DBMS_SHARED_POOL.KEEP() procedure 将较大的且经常使用的 PL/SQL 对象和 SQL 语句游标保持在共享池中，并防止它们过期。可以消除重复重新加载相同对象的需求并减少共享池的碎片。

努力细节 : 中; 需要定位要保持的对象并执行procedure来保持它们。 风险细节: 中; 保持太多对象并且不定期检查将会增加发生ORA-4031的风险。

解决方案实施

参考如下文档：

在线文档

Concepts: Memory ArchitecturePerformance Tuning: Keeping Large Objects to Prevent AgingPL/SQL DBMS_SHARED_POOL

How-To

Document 305529.1 How To Use SYS.DBMS_SHARED_POOL In a PL/SQL Stored procedure To Pin objects in Oracle's Shared Pool. Document 101627.1 How to Automate Pinning Objects in Shared Pool at Database Startup Document 305529.1 How To Use SYS.DBMS_SHARED_POOL In a PL/SQL Stored procedure To Pin objects in Oracle's Shared Pool.

MOS文档

Document 61760.1 Using the Oracle DBMS_SHARED_POOL Package Document 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention

实施验证：

检查性能是否有所改善。如果没有看到性能改善，请检查以下内容：

  • 查看其他可能的原因
  • 验证数据收集是否正确完整
  • 仔细检查有问题的 SQL 语句

如果您想提出一个服务请求，提供一份测试用例将非常有帮助。

原因: Library cache object 失效

当对对象（如表或视图）进行DDL 或收集统计信息时，依赖于它们的游标将失效。这将导致游标在下一次执行时被硬解析，并会影响 CPU 和发生锁竞争。

确认方法

TKProf:

  • 分析解析时间进行的排序信息。
  • 确认 Top 的语句并确定它们是否被硬解析, 如果发生硬解析，"Misses in the library cache" 的值将接近 Parse 的回数。
  • 如果发生硬解析的SQL语句中并没有使用常量(Literals)，则可能由于从 library cache 中移除了本来可以共享的SQL语句。(这并不是绝对的，因为也可能有使用绑定变量但不会再次执行的SQL语句)。

AWR 或者 statspack 报告:

  • Library Cache statistics 部分显示 reloads 数很高并且 (每小时几千次) invalidations 也很高。
  • "% SQL with executions>1" 超过 60%, 表明SQL语句共享率较高。
  • 确认 Dictionary Statistics 部分中的“Modification Requests”的值是否为0，这意味着一些对象上有DDL在执行。
解决方案: 不要在数据库繁忙的时间段执行DDL

DDL 语句使库缓存对象失效，并会涉及到许多依赖对象，比如游标。对象失效后需要同时进行多次硬解析，对库缓存、共享池、字典缓存、CPU使用产生很大影响。

努力细节 : 低; 在非高峰时段执行DDL。 风险细节: 低; 应用端可能需要一些停止时间。

解决方案实施

不适用，只需要在维护期间或者低负荷时间段执行DDL即可。

实施验证

检查性能是否有所改善。如果没有看到性能改善，请检查以下内容：

  • 查看其他可能的原因
  • 验证数据收集是否正确完整
  • 仔细检查有问题的 SQL 语句

如果您想提出一个服务请求，附带测试用例将非常有帮助。

解决方案: 不要在数据库繁忙的时间段收集统计信息

收集统计信息（ANALYZE或者DBMS_STATS）会使库缓存对象失效，并会涉及到许多依赖对象，比如游标。对象失效后需要同时进行多次硬解析，对库缓存、共享池、字典缓存、CPU使用产生很大影响。

在一些数据库版本中，DBMS_STATS 允许在收集统计信息时不立即使依赖对象无效（no_invalidate 选项）。

努力细节 : 低; 只需将统计信息收集更改到非高峰时段运行。在 10g 及更高版本中，您可以选择在收集统计信息后不立即使依赖对象失效。 风险细节: 低; 只需将统计信息收集更改到非高峰时段运行。

解决方案实施

依赖对象不因统计信息收集而失效的信息，请参阅以下文档。

文档

GATHER_TABLE_STATS Procedure, see the "no_invalidate" option

实施验证

检查性能是否有所改善。如果您没有看到性能改善，请检查以下内容：

  • 查看其他可能的原因
  • 验证数据收集是否正确完整
  • 仔细检查有问题的 SQL 语句

如果您想提出一个服务请求，附带测试用例将非常有帮助。

解决方案: 不要在数据库繁忙的时间段执行 TRUNCATE 操作

参考文档Document 123214.1

努力细节 : 低; 在非高峰时段执行DDL。 风险细节: 低; 应用端可能需要一些停止时间。

解决方案实施

参考如下文档：

MOS文档

Document 123214.1 Truncate - Causes Invalidations in the LIBRARY CACHE

实施验证

检查性能是否有所改善。如果没有看到性能改善，请检查以下内容：

  • 查看其他可能的原因
  • 验证数据收集是否正确完整
  • 仔细检查有问题的 SQL 语句

如果您想提出一个服务请求，提供一份测试用例将非常有帮助。

原因: 跨越多个会话进行对象编译

一个或者多个会话在编译对象（通常时PL/SQL）的同时，其他会话为了执行或者编译同一个对象，pin住了它，那么这些会话将会以共享模式（执行）或者独占模式（编译或者更改对象）下等待library cache pin。

确认方法

TKProf:

  • 发现较高 library cache pin 等待 and/or library cache pin 等待
  • 有编译的SQL语句或者正在执行 PL/SQL
解决方案: 避免在数据库繁忙的时间段或者多个会话同时编译对象

避免同时从多个会话或者业务高峰期编译有依赖关系的对象。

Hanganalyze 命令对于识别阻塞或者等待会话很有用 (参考 "Hang / Locking tab > Issue Identification > Data Collection" 获取更多信息)。

努力细节 : 低; 需要考虑如何以及何时重新编译对象。 风险细节: 低;

解决方案实施

考虑提前安排计划和顺序执行重新编译以避免冲突。

实施验证

检查性能是否有所改善。如果没有看到性能改善，请检查以下内容

  • 查看其他可能的原因
  • 验证数据收集是否正确完整
  • 仔细检查有问题的 SQL 语句

如果您想提出一个服务请求，附带测试用例将非常有帮助。

原因: 审计被启用

审计由于需要申请 library cache lock 可能会导致产生冲突。尤其是在RAC环境中，library cache lock 是跨所有实例对整个数据库进行的，影响更大。

确认方法

AWR / Statspack:

  • 发生 library cache lock 等待
  • audit_trail 参数被设置为了 none 以外的值
解决方案: 评估审计的必要性

如果不必要，考虑禁用审计

努力细节 : 低; 初始化参数修改。 风险细节: 低;

解决方案实施

参考如下文档：

文档

Keeping Audited Information Manageable

实施验证

检查性能是否有所改善。如果您没有看到性能改善，请检查以下内容：

  • 查看其他可能的原因
  • 验证数据收集是否正确完整
  • 仔细检查有问题的 SQL 语句

如果您想提出一个服务请求，提供一份测试用例将非常有帮助。

原因: RAC环境中的非共享SQL

RAC环境中的非共享SQL语句容易导致 Library cache lock 等待。在单实例中，非共享SQL更容易发生 library cache 或者 shared pooll latch 的竞争，而在RAC环境中，竞争主要发生在 Library cache lock。

确认方法

RAC 环境

TKProf:

  • 许多SQL语句发生硬解析
  • library cache lock 等待作为硬解析的一部分。

AWR / Statspack:

  • 发生 library cache lock 等待
  • "% SQL with executions>1" 百分比低 (小于 60%)
  • 软解析比率低于 80%
解决方案: 用绑定变量重写SQL

通过用绑定变量重写 SQL 语句，会将仅条件值不同的 SQL 语句视为相同和可以共享的，这是促使在 library cache 中 SQL 语句共享的最佳方式。

努力细节 : 中或者高; 应用程序端需要重写 SQL 语句以将常量（Literals）部分改为绑定变量。 风险细节: 中; 改为使用绑定变量后可能会导致一些SQL语句执行计划变差，修改后的语句需要经过彻底的测试以避免性能下降。

解决方案实施

参考如下文档：

故障排除

Document 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention Document 296377.1 Troubleshooting: High Version Count Issues

文档

SQL Sharing Criteria

实施验证

检查性能是否有所改善。如果没有看到性能改善，请检查以下内容：

  • 查看其他可能的原因
  • 验证数据收集是否正确完整
  • 仔细检查有问题的 SQL 语句

如果您想提出一个服务请求，提供一份测试用例将非常有帮助。

解决方案: 使用 CURSOR_SHARING 初始化参数

通过设置 CURSOR_SHARING 参数，将SQL语句中的常量（Literals）部分自动替换为绑定变量。可以为参数设置以下值：

  • EXACT: 常量（Literals）部分按原来的方式执行。（默认值）
  • FORCE: 将所有常量（Literals）部分替换为绑定变量。（尽可能）
  • SIMILAR: 仅当执行计划无法更改时，才用绑定变量替换常量（Literals）部分。（例如：安全的常量替换）

通常,经常使用等价条件的 OLTP 应用程序的执行计划变化不大，但这些参数的效果应该在应用程序中测试。

此参数可以在会话级别设置，建议使用以最大程度地减少影响。

努力细节 : 低; 需要修改 init.ora/spfile，最坏的情况需要一个LOGON触发器来设置会话级参数。 风险细节: 中；替换为绑定变量可能会影响某些 SQL 选择最佳执行计划。使用 SIMILAR 代替 FORCE 可以降低这种风险，但 SQL 语句共享的效果会打折扣。

解决方案实施

参考如下文档：

参考

Reference: CURSOR_SHARING Parameter

Document 94036.1 Init.ora Parameter "CURSOR_SHARING" Reference Note

故障排除

CURSOR_SHARING for Existing Applications

Document 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention Document 296377.1 Troubleshooting: High Version Count Issues

文档

7.3.1.3 SQL Sharing Criteria

实施验证

检查性能是否有所改善。如果没有看到性能改善，请检查以下内容：

  • 查看其他可能的原因
  • 验证数据收集是否正确完整
  • 仔细检查有问题的 SQL 语句

如果您想提出一个服务请求，提供一份测试用例将非常有帮助。

原因: 大量使用行触发器

频繁的触发行触发器会导致比正常情况更多的 Library cache 活动,原因是需要检查是否正在读取发生修改的表。在触发器处理的过程中，可能会引用发生修改的表，即由触发器SQL修改的表。这会让数据库处于不一致的状态，导致ORA-4091的错误。为了检查这一点，每一次查找这些表都会获取 Library cache lock。

是否发生取决于触发了多少行触发器，而不是定义了多少行触发器。拥有一个触发 10000 次的触发器比拥有 100 个仅触发一次的触发器更有可能导致这个问题。

确认方法

TKProf:

  • 许多SQL语句发生硬解析
  • 发生 library cache lock 等待
  • 发现由行触发器的存在 (可能是一些与触发器相关的递归 SQL)
解决方案: 评估行触发器存在的必要性

有些情况下不需要行触发器完成该功能。考虑是否有替代方案。

努力细节 : 中; 可能需要更改应用程序和架构。 风险细节: 中; 应用程序和架构的变化可能导致一些副作用，需要进行彻底的测试。

解决方案实施

需要了解应用程序以及如何使用行触发器，详细信息请参考如下文档。

文档

App Dev Guide: Using Triggers

实施验证

检查性能是否有所改善。如果没有看到性能改善，请检查以下内容

  • 查看其他可能的原因
  • 验证数据收集是否正确完整
  • 仔细检查有问题的 SQL 语句

如果您想提出一个服务请求，提供一份测试用例将非常有帮助。

原因: 过多的子游标

单个 SQL 语句可以生成大量子游标。在这种情况下，会在生成子游标的会话之间发生对相同资源（latches 或者 mutexes）的争用。

确认方法

AWR / Statspack 报告; 查看 "SQL ordered by Version Count" 部分. 如果有SQL语句的version数超过了500，则可能引发这个问题。或者，也可以查询 V

查询 VSQLAREA视图确认是否有versioncount大于500的SQL语句。查询VSQL_SHARED_CURSOR 视图检查SQL没有共享的原因。

解决方案: 确认是否恰当的使用 CURSOR_SHARING=SIMILAR

SIMILAR 和 FORCE 的区别在于 SIMILAR 会强制的共享相似的SQL语句，而不会使用不合适的执行计划。而CURSOR_SHARING=FORCE 会导致选择不适合的执行计划。

当 CURSOR_SHARING 为 SIMILAR 的时候，如果有不同的常量值（Literals）替换为绑定变量之后导致执行计划产生波动的情况，只有在被替换为绑定变量之前的常量值为相同值时，子游标才被共享。这可以防止使用不适合的执行计划。例如，如果指定了使用“>”的范围条件，那么指定的范围会根据指定的条件值而改变，并影响成本和执行计划，因此如果指定了不同的值，则会生成一个新的子游标。如果是一个相同的谓词，将会始终共享子游标。

可以考虑在应用程序中更改SQL语句以使用绑定变量（比如Hint,SQL Profile或者Outline），避免使用CURSOR_SHARING=SIMILAR。或者设置CURSOR_SHARING=FORCE,不过有导致使用不合适的执行计划的风险，但是不会增加子游标。

努力细节 : 取决于修改内容。将 CURSOR_SHARING 更改为 FORCE 很容易，但重写应用程序的 SQL 语句所涉及的工作量要多一些。 风险细节 : 取决于修改内容。在实例级别将 CURSOR_SHARING 设置为 FORCE 会对整个数据库造成风险，而在会话级风险较小。应用程序端对 SQL 语句的更改仅影响特定的 SQL 语句。

解决方案实施

参考如下文档：

文档

Reference: CURSOR_SHARING Parameter

Document 94036.1 Init.ora Parameter "CURSOR_SHARING" Reference Note

故障排除

Document 296377.1 Troubleshooting: High Version Count Issues

实施验证

检查性能是否有所改善。如果没有看到性能改善，请检查以下内容：

  • 查看其他可能的原因
  • 验证数据收集是否正确完整
  • 仔细检查有问题的 SQL 语句

如果您想提出一个服务请求，提供一份测试用例将非常有帮助。

如果您有更多问题，请在我们的社区咨询：

https://community.oracle.com/community/support/oracle_database/database_tuning

参考

NOTE:235444.1 - How to prepare a complete testcase for support NOTE:727863.1 - How to Create a SQL-testcase Using the DBMS_SQLDIAG Package [Video] NOTE:29012.1 - QREF: TKPROF Usage - Quick Reference NOTE:1174105.1 - How to Run SQL Testcase Builder from ADRCI [Video] NOTE:1477122.1 - Resolving Issues Where 'Library Cache Lock' or 'Latch Library Cache' Waits are Seen NOTE:444560.1 - Troubleshooting Library Cache: Lock, Pin and Load Lock

对象 解决方案 事件 性能 lock

0 人点赞