解析错误导致library cache lock+library cache: mutex X 1.查看当前数据库等待事件 ##数据库等待事件发现大量的library cache lock ##从下图中的等待事件查询,基本上可以判定是一条SQL语句导致的,大量回话被1996回话阻塞,1996被回话1354回话阻塞,1354对应的blockding session为空。且所有的回话执行的都是同一条SQL ...
The library cache mutex is acquired for similar purposes that the library cache latches were acquired in prior versions of Oracle.In 10g, mutexes were introduced for certain operations in the library cache. Starting with 11g, the library cache latches were replaced by mutexes, hence this new wa...
等待library cache: mutex X 与之前版本的 latch:library cache 等待相同。library cache: mutex X 可以被很多因素引起,例如:(包括应用问题,执行计划不能共享导致的高版本的游标等),本质上都是某个进程持有 library cache: mutex X 太长时间,导致后续的进程必须等待该资源。如果在 library cache 的 latch 或者 mu...
The library cache mutex is acquired for similar purposes that the library cache latches were acquired in prior versions of Oracle.In 10g, mutexes were introduced for certain operations in the library cache. Starting with 11g, the library cache latches were replaced by mutexes, hence this new wa...
The library cache mutexisacquiredforsimilar purposes that the library cache latches were acquiredinprior versionsofOracle.In10g,mutexes were introducedforcertain operationsinthe library cache.Startingwith11g,the library cache latches were replacedbymutexes,hence this new wait event.---摘自metalink [ID ...
11g等待事件之library cache: mutex X library cache: mutex X替代了之前的library cache latch,主要作用是在hash bucket中定位handle时使用。(比如SQL硬解析时,需要往hash bucket中新增一个cursor时,需要library cache latch)。如下图所示: 文档上面的解释如下:...
oracle解析相关的等待事件(Shared Pool latch/library cache: mutex X/cursor: pin S) Shared Pool/Library Cache Latch Contention 可能的原因 sql语句不能被重用,语句没有使用绑定变量,不合适的应用游标缓存大小,频繁的登入或登出,数据库对象上做过ddl操作,共享池太小等 ...
从ASH中分析library cache lock可以得出,多个会话等待library cache lock主要发生在SQL AREA BUILD的mutex持有争用上。Library cache: mutex X 是10.2.0.2之后library cache latch衍生出来等待。 以下是部分等待事件的含义: 此类等待事件往往都是发生在SQL解析前遍历library cache object handle链表找到shared cursor。
某数据库为Oracle 11.2.0.3.9 RACLinux64bit,一天晚上9点左右,业务系统反应缓慢,数据库曾发现有大量library cache lock等待事件,并伴随有library cache:mutex X,导致业务系统短暂无法正常提供业务处理 问题分析 当天起发现数据库有大量librarycache lock,平均等待有1775ms ,并伴随有librarycache: mutex X。
常见的library cache lock产生的原因在《高级OWI与Oracle性能调查》这本书和下面这个文档中有一般性的描述: Troubleshooting Library Cache: Lock, Pin and Load Lock (Doc ID 444560.1) 一般可以理解的是alter table或者alter package/procedure会以X模式持有library cache lock,造成阻塞。