ts-contention ss-contention等待事件

一、现象：

数据库存在大量ts-contention ss-contention 等待事件，而阻塞的最原始的源头居然是 smon进程，而smon进程处于 空闲状态，其事件为：smon timer

将阻塞会话杀掉，会重新产生新的阻塞会话。

二、分析：

RAC里，所有节点共用同一个临时表空间，而temp extent一旦分配给某一个节点，其它节点将不可见；

一旦某个节点上分配的temp extent耗尽，则会发出cross instance call(CIC)向其它节点请求temp extent;

此时SMON就去回收所有节点的free temp extent，此过程会持有TS enq，CIC必须等待SMON完成对所有节点的free temp extent完成回收才会继续下一步；

SMON每次向每个节点回收100个extents，目前每个extents设置为1M，4节点RAC一次回收400M；

但是如果操作的排序很大或者hash join数据非常多，SMON的处理速度可能跟不上应用请求速度，此时就会产生TS – contention；

另外，如果每个节点的temp extents分配不均，而大查询正好连接到分配比较少的节点上，情况会加重。

三、解决：

两个节点

ALTER SESSION SET events ‘immediate trace name drop_segments level ts#+1′;

select ts# ,tablespace_name from v$tablespace;

kill掉阻塞会话。

上述方法临时解决后，仍会重复

通过更换temp分区解决该问题。

Step-1: Create the new temporary tablespace.

CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE '+DATA' SIZE 100M AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED TABLESPACE GROUP '' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Step-2: Alter default temporary tablespace of database.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;

Step-3: alter application user’s temporary tablespace as follows.

alter user MSD temporary tablespace TEMP_NEW;

Step-4: Find the related objects that are using the old temporary tablespace.

select tu.tablespace,tu.username,s.sid,s.serial#,s.inst_id from gv$tempseg_usage tu, gv$session s where tu.session_addr=s.saddr;

Generate kill session script as follows.

select 'ALTER SYSTEM KILL SESSION '''||s.sid||','||s.serial#||',@'||s.inst_id||''';' from gv$tempseg_usage tu, gv$session s where tu.session_addr=s.saddr and tu.tablespace='TEMP';

Step-5: Wait for completion of the Step-4, or kill the related sessions as follows.

ALTER SYSTEM KILL SESSION ‘sid,serial#,@inst_id’ IMMEDIATE;

ALTER SYSTEM KILL SESSION '682,5094,@6' IMMEDIATE; ALTER SYSTEM KILL SESSION '1031,18702,@6' IMMEDIATE; ALTER SYSTEM KILL SESSION '208,24717,@8' IMMEDIATE; ALTER SYSTEM KILL SESSION '682,4169,@8' IMMEDIATE; ALTER SYSTEM KILL SESSION '1031,27591,@8' IMMEDIATE; ALTER SYSTEM KILL SESSION '208,17983,@1' IMMEDIATE; ALTER SYSTEM KILL SESSION '682,32246,@1' IMMEDIATE;

Step-6: Drop the old temp tablespace as follows.

Make sure that any session and objects don’t use this tablespace.

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;