oracle cursor: pin s wait on x （一）

cursor: pin s on x:

  今天是2013-11-11，传说中的光棍节，呵呵。之前数据库出现过一次cursor：pin s on x等待事件，距上次出现该问题已经有10天了，但是一直也懒的去总结这个事件，今天就深入研究一下：
第一：什么是cursor:pin s on x?
A session waits for this event when it is requesting a shared mutex pin and another session is holding an exclusive mutex pin on the same cursor objec（注意在10G之后library cache pin 被mutex取代）
也就是说当一个会话请求共享mutex pin的时候，另一个会话正好在同一个游标对象上持有排他pin。
第二 ：cursor:pin s on x参数？
查看该会话参数如下：
p1 = the mutex Id
This has the same definition as v$mutex_sleep_history.mutex_identifier

p2raw = holding Session Id | Ref Count
The most significant bytes always store the Holding Session Id (Holding SId).
The least significant bytes always store the Ref Count.
64 bit platforms
8 bytes are used.
Top 4 bytes hold the session id (if the mutex is held X)
Bottom 4 bytes hold the ref count (if the mutex is held S).

32 bit platforms
4 bytes are used.
Top 2 bytes hold the session id (if the mutex is held X)
Bottom 2 bytes hold the ref count (if the mutex is held S).
P3 Mutex where (an internal code locator) OR'd with Mutex Sleeps
第三：产生该等待事件的原因：
1）频繁的硬解析
简言之，shared pool 中的library cache 存放sql语句、解析树、执行计划等信息，它被free list buckets组成，每个bucket都有library cache object handle，每个handle又连接了其他的handle，在library cache handle中指向library cache object （heap 0），当我们执行一条 语句的时候首先查看该语句是否被执行过，通过计算执行sql的hash 值与library cache中的heap 0的hash值进行比较，如果一样则说明该sql只需要进行软解析，如果没有hash值，那么就需要进行英解析，在进行解析的时候需要获得library cache object handle的lock，在对对象进行解析的时候需要pin住该对象也就是library cache pin ，如果这是时候又有很多的会话需要获得该对象的s，那么其他会话就必须进行等待。在10G中library cache  pin 被 mutex取代了。

假如在某一个时间数据库突然存在很多英解析，那么首先要看的就是相关对象是否统计信息存在问题，从而影响了执行计划。
如何知道哪些sql进行了频繁的英解析，方式有很多中，可以通过addm进行诊断，可以通过ash结合awr进行诊断，获得sql之后可以更具dbms_sqltune进行相关问题的调优，还可以通过v$sql 和 v$session进行结合获得该等待事件对应的sql信息。另外还可以通过oradebug设置 oradebug dump systemstate 266进行诊断。
另外我们可以通过x$mutex_sleep视图获得相关hang住的详细信息.
如下是一个案例：

查看数据库dbtime明显负载过重：

 

查看load profile中硬解析每秒5次，16分钟也就是4800次，相当吓人了。

查看top5时间发现出现了cursor：pin s onx

通过time model statistics更加确认直观的看出本次问题是由于硬解析造成如下（hard parse elapsed time）：

通过sql orderd 不要parese calls 发现存在一条update语句并且执行次数和解析次数为1:1，天煞的。

通过segment by row lock waits等待可以看出在update这条语句的对象上已经存在了非常明显的row lock waits

经过和应用那么确认他们正在执行这条sql，却小该操作之后问题得到处理。

总结：

通过等待事件可以知道目前数据库产生了cursor：pin s on x，产生的原因有三种，第一：version count 过高，第二bug，第三硬解析：通过数据库的版本和查看version count排查了第一和第二种，在load profile和Instance Activity Statistics发现英解析消耗了大量的时间。查看SQL ordered by Parse Calls发现一条update语句parse calls=executions，进而此update语句赌赛了其他语句，进而产生了行锁等待（这是现象，不是本质）。通过最后你说的segment wait  验证了这条语句的问题所在。

 

这里我又想起之前有个案例：是执行计划存在问题，导致此等待事件的出现，处理思路是这样的。

首先也是先查看该事件的sql通过awr或是直接通过v$session，v$sesstat，v$statname视图进行组合得到改等待时间对应 的用户sid，然后在快速查看是否硬解析过大：

参考如下：

SQL> select a.sid,a.serial#,a.username,b.name,c.value from v$session a,v$statname b,v$sesstat c
  2  where a.sid=c.sid and b.statistic#=c.statistic# and name like '%parse%' and a.sid=&sid;
Enter value for sid: 1
old   2: where a.sid=c.sid and b.statistic#=c.statistic# and name like '%parse%' and a.sid=&sid
new   2: where a.sid=c.sid and b.statistic#=c.statistic# and name like '%parse%' and a.sid=1

       SID    SERIAL# USERNAME                       NAME                                                                  VALUE
---------- ---------- ------------------------------ ---------------------------------------------------------------- ----------
         1          5 SYS                            ADG parselock X get attempts                                              0
         1          5 SYS                            ADG parselock X get successes                                             0
         1          5 SYS                            parse time cpu                                                           79
         1          5 SYS                            parse time elapsed                                                      432
         1          5 SYS                            parse count (total)                                                     947
         1          5 SYS                            parse count (hard)                                                      486
         1          5 SYS                            parse count (failures)                                                    1
         1          5 SYS                            parse count (describe)                                                    0

当我们遇到sql语句的执行计划问题该如何处理呢？

思路是：首先我们要找到对象最后收集统计信息时间，查看是否存在统计信息过久问题，如果存在我们可以使用dbms_stats相关过程收集统计信息，但是如果该sql存在于shared pool中，是不会在sql执行的时候使用新的统计信息的，因此可以把存在问题的spid先kill掉，另外有时候需要使用dbms_shared_pool.purge将该sql强制在shared pool剔除，以便下次在按照新的统计信息进行硬解析。