Oracle11.2.0.4查询表一直卡住cursor:pin s on x

梦里梦外; 2023-08-17 16:39 66阅读 0赞

现场反馈:查询一张几千条数据的表，一直卡住，然后重启了数据库，还是这样。  
1.获取了数据库报告，发现排在第一位的是cursor:pin s on x等待事件。  
Top 10 Foreground Events by Total Wait Time  
Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class  
cursor: pin S wait on X 220 16K 72891 83.3 Concurrency  
SQL\*Net message from dblink 1,231 1114 905 5.8 Network  
DB CPU 948.6 4.9  
db file sequential read 166,701 859.5 5 4.5 User I/O  
log file sync 23,317 192.5 8 1.0 Commit  
db file parallel read 857 163.4 191 .8 User I/O

2.先要明白这个等待事件的意思：  
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，通常发生在解析SQL的时候。

3.由于现在的问题是可以重现的，所以问题很好解决。  
a.先开一个窗口1  
–记录下当前窗口的实例号和sid  
select inst\_id,sid from gv$mystat where rownum=1;  
–查询会被堵塞的表  
select \* from table\_name  
b.再开一个窗口2，查找谁堵塞的上面窗口的会话，s.INST\_ID= and s.sid=填窗口1查出来的信息

select s.BLOCKING_INSTANCE,s.BLOCKING_SESSION from gv$session s where s.INST_ID= and s.sid=
    
    现在查出来的 s.BLOCKING_INSTANCE,s.BLOCKING_SESSION就是制造堵塞的会话，
    不过要想查出始作俑者，需要再次查找这个制造堵塞者还有没有s.BLOCKING_INSTANCE,s.BLOCKING_SESSION，如果没有，则说明它就是堵塞的源头；如果有，还需要往上查。
    c.先不要急着kill session，需要通过gv$session里面的信息找到功能是什么触发的。因为如果不找到触发的原因，kill session只是治标的办法，问题还是会出。幸运的是，本次操作是开发执行脚本，dblink查询导致的问题。
    d.kill session,如果kill不了就需要从操作系统上kill 线程。

4.如果问题不能重现，还想找到问题，怎么办？需要查询dba\_hist\_active\_sess\_history，这张表是每10s，数据库会收集一轮会话的信息。  
select \*  
from dba\_hist\_active\_sess\_history  
where event =‘cursor: pin S wait on X’  
and sample\_time between  
to\_date(‘2019-09-24 00:00:00’, ‘yyyy-MM-dd HH24:mi:ss’) and  
to\_date(‘2019-09-24 12:00:00’, ‘yyyy-MM-dd HH24:mi:ss’);  
查询字段中有P2，是堵塞这的sid，也有BLOCKING\_INSTANCE,BLOCKING\_SESSION，应该可以找到问题。