Oracle后台专家解决library cache锁争用的终极武器
11月19日，云汝网络科技合伙人宋日杰（Roger Song）在“DBA+东北群”进行了一场关于“使用Hotcopy缓解 library cache: mutex X 的争用”的线上主题分享。小编特别整理出其中精华内容，供大家学习交流。
DBA+原创专家团成员
超过13年IT及Oracle数据库经验
历任中国联通系统工程师、维布络信息科技Oracle ERP管理顾问
2008年加入Oracle全球技术支持（GCS），专注性能分析
2012年协助建立Oracle集成系统中国支持团队（EEST），任首席技术支持工程师
现任云汝网络科技（上海）有限公司合伙人，正在转型互联网解决方案
对Oracle数据库全线产品模块有全面的深入了解，曾作为DSI讲师为Oracle国内原厂技术团队提供培训
擅长Oracle原理，性能诊断，及复杂环境下的对问题的隔离与分析
今天来给大家分享一个Oracle使用中的小技巧。 当某条SQL语句或者对象被反复访问，过多的软解析可能会造成大量的“library cache:mutex X”争用，有什么样的方法处理此类问题呢？这是个头疼的问题。 今天的话题，就是介绍如何利用hotcopy来缓解library cache中的热点争用。在oracle 11g中，“library cache:mutex X”是个有点特殊的mutex，因为它存在于几个不同的位置，需要进一步的信息进行分辨。这里还会简单描述library cache的结构，理解library cache对认识oracle是非常重要的。
解决性能问题没有特别的方法，主要靠对数据库整体结构的准确理解。了解各种等待发生的原因，以及其在整个体系中的位置，才能做出正确的判断。 所以我会先大致介绍下10G和11G对library cache这个结构的保护方式。 这样你就会理解这个问题发生的可能原因。 然后，我们来尝试通过创建对象的hotcopy来缓解这类争用。
其中个别内容是我自己摸索和猜测出来的，难免有错误和不准确的地方，欢迎大家指正。
也许不是所有的同学都清楚library cache的结构，先在这里简单描述一下。
我们知道，当编译过的SQL已经存在于library cache中的时候，oracle会进行软解析，重用这个结构。 那么oracle如何知道是否已经存在可用的SQL呢？ 是通过如图中的Hash结构。所有已经编译过的library cache object会被组织在一个hash结构中。 当进行搜索时， Oracle会对SQL语句进行hash计算，根据结果找到相应的hash bucket，比如bucket 6， 然后再搜索链接在这个bucket上的所有handle，这里你可以认为每个handle对应唯一的SQL文本。
一种情况，如果“Handle X” 正好是要找的SQL，则会向内搜索“Handle X”包含的child，寻找可用的cursor。 如果有完全符合条件的可用child，则重用，完成软解析。 如果没有，则创建一个新的child，完成一次硬解析。
另一种情况，如果没有找到符合条件的handle，说明SQL 文本是新的，则需要创建一个新的handle和第一个child。 同样完成一次硬解析。
那么为什么要在这个结构上进行锁保护呢？什么操作需要加锁？
一个很简单的例子，如果没有锁的话，假设process 1和process 2同时执行同一个SQL，当他们都没有找到匹配的Handle时，有可能重复添加针对同一SQL的Handle Y。
同样的，当两个进程同时搜索Handle X下是否有匹配的Child时，也有可能在此Handle下，添加两个相同的Child。
因此，为了避免这种情况发生，对此结构的锁是必要地。 由于这些操作都是快速的内存操作，此类锁不宜采用复杂的结构，没有共享模式和独占模式之分。 所以，只有一个进程可以对某个结构进行搜索和修改。也就是说，即使是软解析，在结构内进行搜索时，也是串行的。
在10g和11g，library cache锁的粒度是不同的。
10G的时候，保护这个结构的是 latch: library cache。 这个latch的默认数量为大于CPU Count的最小质数，而默认最小的bucket个数为509，这就意味着每个latch要保护多个Hash Buckets以及相应的handle。 也就是说，对于一组bucket和handle，每次只有一个进程可以搜索修改。因此，这个latch经常成为热点。
11G以后，此latch被替换为library cache: mutex X。锁的粒度变小，每个bucket和每个handle都被一个单独的mutex保护，大幅度减小了争用
然而，从latch名字本身，我们区分不出等待是发生在hash bucket上，还是handle上。需要通过p1来进行分辨。
如果P1是bucket的编号，通常比较小。 如果下面查询返回结果，说明争用发生在hash bucket上。 返回结果为链接在此bucket上的对象。
select KGLNAHSH, substr(KGLNAOBJ, 1, 30) Name,
KGLHDNSP Namespace, KGLNAHSV Hash
from x$kglob where KGLHDBID = &p1;
这种情况下，锁是在bucket上，即对handle进行访问操作时发生的。
实际上，这种情况并不常见，因为每个bucket上链接到的handle通常不会很多。 为了提高hash表的效率，当bucket上平均handle数量超过2时，oracle会将bucket的个数翻倍，重建hash表，用空间交换时间。 在链不长的情况下，一般handle上会先遇到瓶颈。
更常见的争用位置，是在 handle上，这说明某个特定cursor或者object被搜索时发生争用。
如果下面查询返回结果，则说明是这种等待。返回结果为handle所对应的对象。 可能是cursor，也可能是object
select kglnaown, kglnaobj
from x$kglob
where kglnahsh = &p1;
明白了大致的原理之后，就可以想象到这个mutex争用的可能原因。
首先，它有可能是硬解析造成的。 当发生硬解析时，无论要创建一个新的handle，还是要创建一个新的Child，都需要额外大量的时间来完成，相应地，对这个mutex的持有时间会明显变长，造成争用。
由于硬解析会向shared pool申请大量的内存，因此这种情况会伴随一系列其他等待如 latch: shared pool, library cache load lock。 解决办法就是分析硬解析过多，或者version count过高的问题。
还有一种情况，就是OS资源不足，特别是CPU资源紧张。 这种情况下，oracle进程无法获取足够的资源去完成相应的工作，无法及时释放此mutex，造成请求的堆积。
再有，就是某个持有者长时间不释放此mutex，造成请求的积压。这是不正常的， 需要对mutex持有者的当前状态进行分析。 Errorstack, process dump。 如果持有者被其他进程阻塞，则通过hanganalyze等工具继续查找最终持有者。
另外，个别Oracle bug也会造成此类问题。
如果，以上原因都被排除掉，那么，对某热点SQL或热点object的频繁访问，也会造成此问题。 也就是我们之前说过的，哪怕是软解析，也会在查找library cache结构的时候串行。 这类问题比较让人头疼，一方面，SQL语句来自业务，我们不能人为减少SQL的执行次数和效率。 另一方面，如果SQL的文本相同，就一定会被hash到一个特定的handle上，这个是无法改变的。从前遇到这类问题非常让人头疼，一般可能的解决办法见下一页。
那么接下来该怎么办呢？
首先，可以查下是否有连接风暴。 连接风暴可能造成某些PLSQL对象的频繁执行，是造成此类问题的常见原因之一。
Version count如果很高，也会引发这个问题，因为找到handle以后， 对child的搜索是用遍历的方式，如果version很高child很多，则每次搜索的时间很可能会变长。会造成对handle上的锁，持有的时间也变长。所以，这也是一个可能的原因。
另外，和开发联系，研究SQL或某个对象如此高的并发是否正常的。 如果SQL来自不同的模块，则可以试着修改来自不同位置的SQL文本（比如加空格，或无用注释），使他们hash到不同的handle上。
增加session_cache_cursors是个可能的尝试，如果cursor被cache住，child的位置会被保存在pga中，那么查找child的速度会明显加快，对Mutex的持有时间也会变小。
如果以上方法均不适用，我们最后可以尝试创建hotcopy来改善此问题。
对于资源争用的问题，解决办法只有两个，要么减小需求，要么增加资源数量。而hotcopy的原理，实际就是在hash SQL文本的时候，加上PID的部分。 这样，哪怕执行相同的SQL文本，不同进程就有可能会被hash到不同的handle上。 这实际增加了资源的数量。当然，copy的数量是可控的。
制作了hotcopy以后，相同的对象和child可能多次出现在library cache的不同位置，造成了一部分空间浪费，但却可以解决library cache中热点对象或热点cursor的争用。也是一种用空间换时间的办法。
hotcopy这个功能是通过fix 9239863和fix 9282521引入的。 11.2.0.2以上版本可以直接使用。 低版本数据库需要安装相应地补丁。 针对11.2之前版本的数据库，需要设置两个隐含参数。 slide里有详细的描述。针对cursor 和 object对应不同的方法。
11.2之后，可以通过标准API进行设置。
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_shared_pool.htm#ARPLS68085下面我们用一个简单的实验，查看到底发生了什么。 在试验中，我模拟了一个场景，大量session反复执行某一个SQL。
从查询结果来看，内存中ASH捕捉到的等待事件， library cache: mutex X 占据了很高的比例。
AWR报告反应了类似的内容。等待进入了top 1。
经查询，library cache: mutex X等待的P1非常集中。
经过验证，P1的值指向该条cursor的handle。 其实你看这个数字的长度，就知道不可能是hash bucket。 这个handle所在的bucket是14503。
接下来， 我们用之前介绍的方法来激活基于此cursor的hotcopy。
由于我的实验机器CPU太少，所以 我提前设置了隐含参数”_kgl_hot_object_copies”=4来指定copy的个数。
在这里我们使用了11.2提供的API进行hotcopy的实施，针对cursor，需要完整的hash，可以从X$KGLOB.KGLNAHSV中获取。
然后再次提交同样的负载。
这一次，我们看到，ASH捕捉到的mutex等待开始分散到多个P1上，总数量在变少（忽略32127143，这是第一次试验遗留的结果）。 通过进一步验证能够确认，每个handle的内容都是这个cursor。
这里我也注意到14503这个hash bucket上的争用增加，如果你还记得之前的输出，这其实是修改前那个cursor所在的hash bucket。 我猜测即使做了hotcopy，请求仍旧会经由这个bucket跳转。由于handle上的请求处理速度变快了， bucket上的争用开始显现，从library cache的结构来看，这些是合理的。
从library cache dump 中验证，的确增加额外4个handle，相同的对象名。
在新生成的AWR报告中， library cache: mutex X 等待时间下降了50%， 从1882秒，下降到942秒. 由此可以验证，针对纯粹的library cache的热点争用，hotcopy是可以进行缓解的。
最后做一个简单地小结。
首先，从library cache的结构看， 即使是软解析，对child和handle的搜索，也是串行的。 虽然这个操作速度很快，但仍会造成热点争用。
另外，造成library cache: mutex X争用的原因很多，只有纯粹的热点争用才需要用hotcopy来解决。
最后，以我的经验来看，解决数据库性能问题，没有固定的套路。 Oracle是个极其庞大复杂的体系，几乎不可能通过几个简单的等待事件推测出根本原因，不会有这样的“神”。 正确的分析方法是，尽可能多的收集证据，然后利用自己的知识和对整体结构的理解，推导出一个“故事”把所有证据串联起来，有了“故事”以后，再收集更多的证据，反过来来验证故事的正确性。
“DBA+社群”将陆续在各大城市群进行线上专题分享活动，以后每周一、周三晚上为【DBA+专业群】的固定时间，每周二、周四晚上为【DBA+各城市群】的固定时间，每周五晚上为【DAMS架构师精英群】的固定时间，欢迎大家积极加入我们。无论是内容还是形式，有好的建议我们都会积极采纳。
想入群的小伙伴们请关注DBA+社群微信公众号：dbaplus，回复“加群”即可。
小编精心为大家挑选了近日最受欢迎的几篇热文，回复001，看丁俊的《【重磅干货】看了此文，Oracle SQL优化文章不必再看！》；
回复002，看《灾备故障上了红头文件，容灾技术到底哪家强？》；
回复003，看吕海波的《去不去O，谁说了算？》；
回复004，看胡怡文《PG，一道横跨oltp到olap的梦想之桥》；
回复005，看付新《达梦专家解读：国产数据库也疯狂》。
DBA+社群，是一个以连接全中国DBA为使命的公益性组织，致力于成为DBA界的UBER，充分利用DBA的业余时间，为广大DBA搭建一个学习交流、价值实现、人脉搭建、跨界合作的平台。目前，DBA+社群已于北上广深等全国15大城市成立DBA+城市群，100+业界专家成为联合发起人，6000+跨界DBA加入队伍，每周4次线上技术分享活动同步直播。
扫码关注
DBAplus社群
超越DBA圈子，连接的不仅仅是DBA