高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的访问。

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据