Bug 7462072(Cursor:pin S wait on X)

最新推荐文章于 2022-10-18 20:35:33 发布

出埃及

最新推荐文章于 2022-10-18 20:35:33 发布

阅读量1.3k

点赞数 1

分类专栏： Oracle Troubleshooting 文章标签： cache concurrency session sql file user

本文链接：https://blog.csdn.net/Coast_lichao/article/details/7827874

版权

Oracle Troubleshooting 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

遇到的mutex的相关问题比较多了，某库出现间隔性的Cursor:pin s wait on x,先收集信息，看看如下ASH信息：

SQL_ID	      SESS_STATE EVENT	              COUNT  PERCENT    
------------  --------- ------                -----  -------
	              WAITING	cursor: pin S wait on X	812	   37%
g3176qdxahvv9  	WAITING	cursor: pin S wait on X	335	   15%
gbg04txwyfjkx  	WAITING	cursor: pin S wait on X	294	   13%
58nv3rtkuh9ub  	WAITING	cursor: pin S wait on X	88	   4%
7r4zdthc634js  	WAITING	cursor: pin S wait on X	62	   3%
00rg90cycnqqf  	WAITING	cursor: pin S wait on X	27	   1%
01gfu3mx98v0k  	WAITING	cursor: pin S wait on X	25	   1%
16hk2cjrt3b2z  	WAITING	cursor: pin S wait on X	19	   1%
2dvkr83d65jn0  	WAITING	cursor: pin S wait on X	16	   1%
5dkqjdryw2zzs  	WAITING	cursor: pin S wait on X	17	   1%
apz5004nth52s  	WAITING	cursor: pin S wait on X	12	   1%
cbp35zh8b04h9  	WAITING	cursor: pin S wait on X	23	   1%
	         WAITING	row cache lock	         24	   1%
cpq9a527phs7j  	WAITING	cursor: pin S wait on X	24	   1%
b7g2s0346wns1  	WAITING	cursor: pin S wait on X	21	   1%
5qh0g1gu0j082  	WAITING	cursor: pin S wait on X	13	   1%
5f2nd0y4utw40  	WAITING	cursor: pin S wait on X	21	   1%
2n6pau0h2qb32  	WAITING	cursor: pin S wait on X	12	   1%
01gfu3mx98v0k  	WAITING	row cache lock	         7	   0%
1yp4dt2nfjzrr  	WAITING	cursor: pin S wait on X	7	   0%
2n6pau0h2qb32  	WAITING	row cache lock	         6	   0%
32h5xzq590gkj  	WAITING	row cache lock	         6	   0%
531sc6y5xdd41  	WAITING	row cache lock	         2	   0%
4xxtybrs0krp4  	WAITING	row cache lock	         5	   0%
4snshxnd8mnmj  	WAITING	cursor: pin S wait on X	7	   0%
4gd6b1r53yt88  	WAITING	row cache lock	         7 	   0%
4d2n6d7dv46tc  	WAITING	row cache lock	         1 	   0%
3zyfp0fn59qbq  	WAITING	row cache lock	         4 	   0%
3t24qmxac71ys  	WAITING	row cache lock	         1	   0%
3k7y74k8y4rrf  	WAITING	row cache lock	         6 	   0%

接下来再看看cursor: pin S wait on X的blocker在干什么，通过此事件的p1参数，也就是idn可以找到相关的sql，
可以使用p2参数来找到blocker的sid。

SESS_ID	SQL_ID	      STATE    EVENT	               P1	         P2	       TIME_WAITED		
3905   g3176qdxahvv9   WAITING  cursor: pin S wait on X    2057858921	2.34033E+13     9788			
5927   cbp35zh8b04h9   WAITING  cursor: pin S wait on X    279974409	2.31198E+13     9778				
5500		     WAITING   cursor: pin S wait on X   2017311249	2.76596E+13     9778				
SELECT decode(trunc(&&P2/4294967296),
           0,trunc(&&P2/65536),
	     trunc(&&P2/4294967296)) SID_HOLDING_MUTEX
FROM dual;

查找blocker的session发现都在等待row cache lock，cache#为8，通过awr来看，dc_segments比较多。至于为何会出现这样的
row cache lock不得而知，这个事件是随机出现，也无法预先的去dump。
首先需要说明的是：我们内存管理是非ASSM,通过如上等待的分布来看，也并非某个sql的问题，也尝试查找相关对象，确定无
任何的ddl操作，通过查看统计信息，也没有异常的hard parse。分析来，毫无头绪，最后尝试MOS，查查相关的Bug。
如下是收集的问题点的awr片段(多份报告，都类似):

Event                     Waits    Time(s) Avg Wait(ms) % Total Call Time Wait Class 
CPU time                  1,946                      28.9   
cursor: pin S wait on X   180,418  1,765   10        26.2                 Concurrency 
db file sequential read   346,891  1,682   5         25.0                 User I/O 
db file scattered read    304,760  386     1         5.7                  User I/O 
row cache lock            39,322   299     8         4.4                  Concurrency

这个亮点主要在avg wait(ms)多份报告都显示10ms，MOS发现BUG:7462072异常的符合
Versions confirmed as being affected：10.2.0.4 10.2.0.3>我们是10.2.0.3
Description
There are certain scenarios where a session can wait for event 'cursor: pin S wait on X' even though the
mutex was never acquired in exclusive mode. This causes an unnecessary 10ms wait for this waitevent.