合 【MOS】'library cache lock' 等待事件 原因和解决方案 (Doc ID 2896611.1) library cache lock' Waits: Causes and Solutions (Doc ID 1952395.1)

简介

'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 Architecture
Performance Tuning: Keeping Large Objects to Prevent Aging
PL/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 和发生锁竞争。

确认方法

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。