oracle RAC enq: SS - contention 等待事件

问题描述：

数据库版本：11.2.0.3 两节点的集群

操作系统版本：Oracle Linux Server release 6.1

问题发生：2018-10-25 12:40--2018-10-25 15:22节点1监控发现大量的enq: SS - contention等待事件。

节点一的性能视图：

 节点二的性能视图：

 

问题分析：

select * from v$lock_type where type='SS';

 通过描述可以看出该锁是为了确保并行DML操作中创建的排序段不会过早清理。也就是数据库中的临时表空间不够用了，在等待数据库分配新的Sort Segment。

我们通过历史会话等待信息，查看是哪些会话在等待临时表空间：

SELECT instance_number,
       ash.p1,
       ash.p2,
       ash.p3,
       to_char(sample_time, 'hh24:mi:ss.ff') TIME,
       session_id sid,
       ash.sql_id,
       ash.program,
       en.event_name,
       ash.blocking_session
  FROM sys.WRH$_ACTIVE_SESSION_HISTORY ash, sys.wrh$_event_name en
 WHERE ash.event_id = en.event_id(+)
   AND sample_time >= to_timestamp('20181025 1230', 'yyyymmdd hh24mi')
   AND sample_time <= to_timestamp('20181025 1522', 'yyyymmdd hh24mi')
   and event_name like '%enq: SS - contention%'
 ORDER BY sample_time;

 

 (这里只放出部分截图)

可以看到所有的会话都在被530会话阻塞，这里查看该时间段内530在做什么操作：

SELECT instance_number,
       ash.p1,
       ash.p2,
       ash.p3,
       to_char(sample_time, 'hh24:mi:ss.ff') TIME,
       session_id sid,
       session_serial#,
       ash.sql_id,
       ash.program,
       en.event_name,
       ash.blocking_session
  FROM sys.WRH$_ACTIVE_SESSION_HISTORY ash, sys.wrh$_event_name en
 WHERE ash.event_id = en.event_id(+)
   AND sample_time >= to_timestamp('20181025 1230', 'yyyymmdd hh24mi')
   AND sample_time <= to_timestamp('20181025 1522', 'yyyymmdd hh24mi')
   and session_id = 530
 ORDER BY sample_time;

 

DFS lock handle：The session waits for the lock handle of a global lock request. The lock handle identifies a global lock. With this lock handle,other operations can be performed on this global lock (to identify the global lock in future operations such as conversions or release). The global lock is maintained by the DLM.表示会话在等待 global lock的锁请求。

关于该等待事件的p1，p2，p3参数值，在mos和官方文档上均为找到相关的文档。但是在https://blogs.oracle.com/database4cn/奇怪的等待事件“enq%3a-ss-contention”上看到有解释（仅做参考）

这里再往前推进，查看485是什么会话。

SELECT instance_number,
       ash.p1,
       ash.p2,
       ash.p3,
       to_char(sample_time, 'hh24:mi:ss.ff') TIME,
       session_id sid,
       session_serial#,
       ash.sql_id,
       ash.program,
       en.event_name,
       ash.blocking_session
  FROM sys.WRH$_ACTIVE_SESSION_HISTORY ash, sys.wrh$_event_name en
 WHERE ash.event_id = en.event_id(+)
   AND sample_time >= to_timestamp('20181025 1230', 'yyyymmdd hh24mi')
   AND sample_time <= to_timestamp('20181025 1522', 'yyyymmdd hh24mi')
   and session_id = 485
 ORDER BY sample_time;

 

这里可以看到485会话是节点2的进程DBW0，为何节点1请求sort segment会等节点2的DBW0呢？

因为在RAC中，TEMP表空间各个节点是可以共享的，并在各个节点有缓存，可以通过gv$temp_extent_pool视图查看。如果一个节点缓存的TEMP blocks耗尽，会请求另一个节点释放一些未使用的TEMP给他用。

当前的环境每个节点的temp表空间均为32G，正常情况下，temp表空间不存在使用爆满的情况。

分析故障时期，哪个sql语句占用大量temp空间。

SELECT instance_number,
       ash.p1,
       ash.p2,
       ash.p3,
       ash.temp_space_allocated/1024/1024/1024 "USETEMP/G",
       ash.module,
       to_char(sample_time, 'hh24:mi:ss.ff') TIME,
       session_id sid,
       ash.sql_id,
       ash.program,
       en.event_name,
       ash.blocking_session
  FROM sys.WRH$_ACTIVE_SESSION_HISTORY ash, sys.wrh$_event_name en
 WHERE ash.event_id = en.event_id(+)
   AND sample_time >= to_timestamp('20181025 1200', 'yyyymmdd hh24mi')
   AND sample_time <= to_timestamp('20181025 1230', 'yyyymmdd hh24mi')
   and temp_space_allocated is not null
 ORDER BY temp_space_allocated desc;

 

这里我们可以看到这个sql：2arnvg1b7gpk3执行竟然消耗了61个G的TEMP表空间。几乎将两个节点的表空间占满了。

从数据库的alert日志也可以看到有报错temp表空间无法扩展：节点一

后期对有问题的sql进行分析，了解为何占用大量temp表空间