oracle一次卡顿案例(二)-row cache lock

故障描述

10:30左右，应用多次反馈jsbas数据库应用程序报错，会话断开。

原因分析

检查会话情况：

检查会话执行的sql：

从上面可以看出来，JOUR用户的会话等待事件row cache lock，sql文本为“SELECT * FROM OM_LINE WHERE SUBSCRIBER_INS_ID = 108166239 AND ORDER_STATUS = '0’AND EXEC_TIME > SYSDATE AND BUSI_ITEM_CODE in (‘80010032’,‘80010033’)”除了SUBSCRIBER_INS_ID 这个条件不一样，其他内容是一样的，这个很明显的问题就是没有使用绑定变量。

行缓冲锁（row cache lock）用于在Shared Pool内存中锁住数据字典（Data Dictionary）信息。row cache lock等待事件是一个共享池相关的等待事件，是由于对字典缓冲的访问造成的。每一个行缓冲队列锁都对应一个特定的数据字典对象，这被叫做队列锁类型，并可以在V$ROWCACHE视图中找到。

另外一个等待事件cusor: pin S wait on X，这个等待事件是某个会话需要申请S模式的mutex，而mutex被其他会话以X模式占有了，当前会话会处于cusor: pin S wait on X等待事件，结合这些会话的阻塞会话发现，阻塞会话全部是row cache lock等待事件的会话，所以说根本原因是row cache lock导致的。

检查row cache lock会话p1参数：
SQL> select sid,event,p1,program from v$session where event=‘row cache lock’;
SID EVENT P1 PROGRAM

---

   569 row cache lock          63 app-node01-srv01
   577 row cache lock          63 app-node01-srv01
   592 row cache lock          63 app-node01-srv01
   769 row cache lock          63 app-node01-srv01
   954 row cache lock          63 app-node01-srv01
   966 row cache lock          63 app-node01-srv01

查看缓存的类型：
SQL> select parameter from v$rowcache where cache#=63;

PARAMETER
————————
dc_realtime_tabst

dc_realtime_tabst 以前很少会看到这个缓存类型，但是可以推断出realtime_tabst应该指的是实时table statics，这个是19C的新特性，可以判断出这个row cache lock等待事件和表的实时统计信息有关系。

检查到表的统计信息，早上刚刚收集过，如下图所示：

可以看出，早上10:02这个表刚刚收集过统计信息，不到20分钟，统计信息已经过期了，说明这个表数据变化特别快。对于统计信息过期的表，oracle会进行实时统计信息的收集，实际会延长一段时间之后才收集。

最后是通过杀会话，发现无法杀掉所有的cursor: pin S wait on X，row cache lock异常等待事件的会话，不起作用，杀掉后新的会话不断连接进来，最后通过重启应用，断开所有业务会话解决。

总结：结合row cache lock的争用的缓存类型dc_realtime_tabst，以及这个表的统计信息过期，可以判断在高并发未绑定变量sql的会话下，19C的新特性实时统计信息收集导致的。

解决办法和建议

1. 建议应用对应用的sql“SELECT * FROM OM_LINE WHERE SUBSCRIBER_INS_ID = 108166239 AND ORDER_STATUS = '0’AND EXEC_TIME > SYSDATE AND BUSI_ITEM_CODE in (‘80010032’,‘80010033’)”进行整改，SUBSCRIBER_INS_ID字段明显没有使用绑定变量，建议改为使用绑定变量。
2. 业务进行sql整改 hint添加“/*+ NO_GATHER_OPTIMIZER_STATISTICS */” 禁止收集实时统计信息。
3. 建议关闭19C实时收集统计信息的新特性，默认 “_optimizer_gather_stats_on_conventional_dml” 为true， 这样实时统计信息将自动启用。我们可以设置这个参数到 “FALSE” 来禁止实时统计信息的收集。。

参考：

在19c中禁用实时统计信息 (Doc ID 2639856.1)