1. 故障概述
7:15,二节点出现大量的“cursor: pin S wait on X”等待事件,数据库性能下降,持续到7:19分恢复正常,持续时间4分钟左右。
下面是详细的故障分析诊断过程。
2. 故障分析
2.1. 故障现象
7:15,系统出现大量“cursor: pin S wait on X”等待事件,DBA未做任何操作,数据库恢复正常。
2.2. 故障分析
2.2.1. 故障现象
从AWR报告7点-8点:15数据库awr报告。
7:15点-7:19点 |
7:15点-7:19点分的awr报告可以看大量的cursor: pin S wait on X 等待
2.2.2. 什么是cursor: pin S wait on X
Shared pool中的Hash Bucket 管理的是Object handle, 也就是元数据。其上存放了对象的name、 namespace及相关信息(对象是否只读,是本机的还是远端的等),也存放了当前正在lock和pin以及正在等待lock和pin该对象的用户的列表等。
如果object handle存在,但是相关的object heap已经被刷出内存,此时object heap就要被重新reload(v$librarycache.reloads);如果相关object的定义已经被更改(v$librarycache.invalidations),此时就要重新解析相关对象。
cursor: pin S wait on X表示会话试图以S 模式 Pin 某个 Cursor ,但是某个会话已经以 X 模式 Pinned,正在执行 Loading,也就是 Parsing。 通常cursor: pin S wait on X 不是故障的原因,它只是受害者。
2.2.3. cursor: pin S wait on X故障原因
· 内存抖动
但内存抖动会加剧shared pool的latch争用,会导致出现cursor: pin S wait on X,library cache相关等待,严重可能导致数据库hang死或者宕机
· 频繁硬解析
硬解析较多也会导致 cursor: pin相关等待增多
· 高版本
当一个sql的版本过多,也就是子游标过多,当sql软解析去扫描父游标下面的子游标,链路太长也会导致大量的cursor: pin S wait on X等待。可以通过oracle提供的version_rpt3_21.sql去分析高版本的原因。
Cursor Obsolescence游标废弃是一种SQL Cursor游标管理方面的增强特性,该特性启用后若parent cursor父游标名下的子游标child cursor总数超过一定的数目,则该父游标parent cursor将被废弃,同时一个新的父游标将被开始。 这样做有2点好处:
l 避免进程去扫描长长的子游标列表child cursor list以找到一个合适的子游标child cursor
l 废弃的游标将在一定时间内被age out,其占用的内存可以被重新利用
实际在版本10g中就引入了该Cursor Obsolescence游标废弃特性,当时child cursor 的总数阀值是1024, 但是这个阀值在11g中被移除了,这导致出现一个父游标下大量child cursor即high version count的发生;由此引发了一系列的版本11.2.0.3之前的cursor sharing 性能问题,主要症状是版本11.2.0.1和11.2.0.2上出现大量的Cursor: Mutex S 和library cache lock等待事件。
通过如下参数通知子游标的版本数量。
alter system set "_cursor_obsolete_threshold" =100 scope=spfile;
· 错误解析
比如sql语法错误
· DDL
DDL语句会导致相关对象的所有游标都失效,当再次解析时会造成卡顿。
· 收集统计信息
收集统计信息(使用ANALYZE或DBMS_STATS)中参数no_invalidate 设置为false,表示游标立即失效,将导致库缓存对象失效,并且这可能会级联到许多不同的依赖对象(如游标)。失效对库缓存,共享池,行缓存和CPU有很大影响,因为它们可能需要同时进行许多硬解析。
· 大量并发
大并发会导致cursor: pin S wait on X争用。
· Known bugs等
2.2.4. AWR分析
|
硬解析的次数非常低,排除硬解析过高的因素。
子游标版本最多173多,说明不多,不是子游标数量导致。
解析错误的也非常少,说明不是由于解析错误导致。
可以看出故障时间点,sga各个组件在动态调整。
2.2.5. 深入分析
从trc里分析出所有的cursor: pin S wait on X等待的阻塞源头都是SID:737会话,发现737是oracle@pmjxpdbb (MMAN)会话,MMAN进程是Oracle 10g引入用于进行内存管理的进程,在进行动态内存调整时,这个进程要发挥其作用。
等到链都指向了源头SGA: allocation forcing component growth。
SGA组件中KGH: NO ACCESS持续变大 ,KGLH0、SQLA持续变小,KGH: NO ACCESS表示缓冲区缓存和共享池之间的部分传输,正是由于内存组件的调整,latch: shared pool被争用,造成了大量的cursor: pin S wait on X等待。
3. 解决方案
1、增大shared_pool_size 的最小值90G(SGA 600G*15%),或者采用手工内存管理的方式
根据Best Practices and Recommendations for RAC databases with SGA size over 100GB (Doc ID 1619155.1)
2、缩小buffer cache大小,可以减小gcs resources、gcs shadows组件的大小。
2、优化TOP SQL,尤其是全表扫描大量物理读的sql。
来源地址:https://blog.csdn.net/wuweilong/article/details/128449999