高IO下的library cache lock 和cursor pin S wait on X

今天，客户端某数据库遭遇大量library cache lock 和cursor pin S wait on X的问题，经诊断发现是由于他们底层的ASM存储是多个库共享的，故障时间段别的库占用大量的IO，导致paitsm库的IO响应变慢，db file sequential read从平时的20多毫秒，到了故障时的400多毫秒。
且这个库大量的sql使用了SPM，库中有近5000条baseline，连select 1 from dual这样的简单SQL也使用了SPM。

在这2个因素的共同作用下，导致了大量library cache lock 和cursor pin S wait on X。

分析过程如下：

（一）
9:23 数据库中出现大量library cache lock，大约有1400个session处于该等待。去War Room之前，做了 hangeanalyze，从hanganalyze的文件分析，存在多种堵塞链条：

可以看到，大部分的阻塞链还是’db file sequential read’<='read by other session'<='library cache lock'。也就是说，db file sequential导致了library cache lock。看上去还是非常像IO慢的问题导致library cache lock的。 如： sample_id 140056627时, sid 3654(select 1 from dual，library cache lock) is blocked by => sid 1171(select 1 from dual，read by other session, obj为SQLOBJ$_PKEY) is blocked by
=> sid 1018(SELECT NVL(MAX(MYDATE), TO_DATE(‘2007-09-01’, ‘YYYY-MM-DD’)) FROM TAB_ABC_99 WHERE AKSBBCCD_ID = :B1，db file sequential read, obj为SQLOBJ$_PKEY)

等待library cache lock的sql，和阻塞library cache lock的sql是一样的，都是select 1 from dual，但是最终的holder的sql，是另外的SQL。

注：在MOS中，虽然没有关于SQLOBJ$PKEY的详细说明，但是能查询的大部分资料都和spm有关

（二）
dba kill library cache lock

（三）
10:08 ，发现再次出现大量cursor: pin S wait on X，final blocking session是sid为2475的session，处于db file scattered read。
在做process dump之后，kill os process。
我们通过process dump，我们可以看到的阻塞链是：db file scattered read<= library cache lock，即dump的时候，该session处于db file scattered read。 在process dump中，我们看到，2745的session大约堵塞了35个session，其中一个session是3032，处于library cache lock，请求44ab09e98的handle，而往下查时，我们可以看到，2745的该session是以X模式hold住了44ab09e98的handle，所以3032的session就无法获得，只能等2745的session释放。 process dump:

虽然我们从process dump中看到是被db file scattered read堵塞，但是这可能是在dump的时候，session的等待已经改变，从ash中，我们看到阻塞链其实是db file sequential read<=library cache lock<=cursor: pin S wait on X 如： sample_id 140059277时, sid 2515 (sql_id 535a6y4mx7gn1，cursor: pin S wait on X) is blocked by => sid 3032 (sql_id 535a6y4mx7gn1，library cache lock) is blocked by
=> sid 2745 (sql_id 535a6y4mx7gn1，db file sequential read, obj也还是SQLOBJ$_PKEY)
注：最后所读的对象，还是SQLOBJ$_PKEY。

我们在process dump中，也可以看到，在db file scattered read之前，确实处于db file sequential read：

（四）
11:29 再次去War Room，此时再次做 hanganalyze，看到的阻塞链为：

如：
sample_id 140063747时, sid 32 (sql_id 535a6y4mx7gn1，cursor: pin S wait on X) is blocked by
=> sid 1968 (sql_id 535a6y4mx7gn1，library cache lock) is blocked by
=> sid 1172 (sql_id 535a6y4mx7gn1，resmgr:cpu quantum)

综上4个时间点，我们可以看出，阻塞链为：
‘db file sequential read ‘<='library cache lock'<='cursor: pin S wait on X'或者 'resmgr:cpu quantum'<='library cache lock'<='cursor: pin S wait on X' Db file sequential read 的对象都是SQLOBJ$_PKEY。 由于resource manager的作用，导致处理排队，从而引发library cache lock比较好理解，但是IO慢为何导致了library cache lock？ 我们看到library cache handle的holder持有了比较长的时间，一直在处于读SQLOBJ$_PKEY，这是因为SQL在做解析时，需要持有handle，而且需要等解析完，才会释放handle。而由于我们的sql是有SPM固定的，在解析的时候，需要去访问SQLOBJ$_PKEY，而SQLOBJ$_PKEY的对象，由于IO差，访问的慢，或者read by other session正在被别的session访问，而别的session也遭受着IO差的问题。因此，耗费了较多的时间在访问SQLOBJ$_PKEY上。再对于SQLOBJ$_PKEY的访问没有结束前，解析不算完成，因此也就不能释放handle。 因此别的session就处于library cache lock了。 另外，cursor pin S wait on X被library cache lock堵塞，我们用3个session来说明原因： Session A，由于IO慢，hold住了handle，释放的慢。 Session B，需要获得handle，但是被session A hold着，所以处于library cache lock等待。而这个session，在执行select 1 from dual的时候，在解析时会生成一个child cursor，并以exclusive模式pin住这个child cursor。 Session C，由于和session B执行的是一个sql，需要以shared模式共享上面的child cursor，但是由于session B还没有完成解析，还没生成完整的child cursor，所以也无法共享这个child cursor。所以处于cursor pin Swait on X的等待。

所以，解决该数据库的问题，一方面需要我们加快IO，加速SQLOBJ$_PKEY的访问，另一方面，也可以减少数据库中SPM的数量，不要让大量的sql走SPM，从而减少对SQLOBJ$_PKEY的访问。