此事件在RAC中会更加常见,原因在于系统中临时表空间争用。
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 tablespace_number+1′;
可以将此命令设置成crontab job,定时在每个节点执行;各个节点的free temp extents会及时的返回到temp pool
在单实例数据库中,试图drop temp tablespace的时候可能也会遇到类似问题
删除时候长时间等待enq: TS – contention,查看GV$lock找出阻塞会话为SMON进程,等待事件smon timer;
解决方案
1、 查看temp表空间使用情况,杀掉session v$session,v$sort_usage
2、 设置drop segments事件,手工清理,ALTER SESSION SET events‘immediate trace name drop_segments level tablespace_number+1′
在DROP TEMP表空间时,会话长时间不能结束,查看等待事件为:
enq: TS - contention SQL> select name, parameter1, parameter2, parameter3,wait_class
from v$event_name where name ='enq: TS - contention';
NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS
-------------------- ------------ --------------- ----------- ----------
enq: TS - contention name|mode tablespace ID dba Other
找出持有该表空间锁的会话,发现是smon进程,等待事件是smon timer,是个空闲等待事件:
SQL> SELECT DECODE(request, 0, 'Holder: ', 'Waiter: ') status, SID,
inst_id,ctime, id1, id2, lmode, request, TYPE
FROM gv$lock
WHERE(id1, id2, TYPE) IN(SELECT id1, id2, TYPE FROM gv$lock
WHERE request >0)
ORDER BY id1, request
STATUS SID INST_ID CTIME ID1 ID2 LMODE REQUEST TY
-------- ------ ------- -------- ------ ------ ------- ---------- --
Holder:10991 1843932 3 13 0
TSWaiter: 859 11513 1 0 6
TS
SQL> select sid,username,type,PROCESS,PROGRAM,EVENT
from v$session where sid=1099;
SID USERNAME TYPE PROCESS PROGRAM EVENT
---------- ---------- ---------- ------------ --------------- -----------
1099 BACKGROUND2494 oracle@jhsit01 smon timer
SQL> select name, parameter1, parameter2, parameter3,wait_class
2 from v$event_name
3 where name = 'smon timer';
NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS
----------------- ------------- ------------ ----------- ----------
smon timer sleep time failed Idle
估计是SMON进程正在回收、清理TEMP表空间之类的操作。当时直接了重启数据库,事后想想,还有以下两步可以尝试:
1、查看TEMP表空间的使用情况,找出使用TEMP表空间会话,看看是否可以杀掉:
select TABLESPACE_NAME, TOTAL_EXTENTS, USED_EXTENTS, FREE_EXTENTS
from v$sort_segment;
SELECT se.username username,
se.SID sid,
se.serial# serial#,
se.statusstatus,
se.sql_hash_value,
se.prev_hash_value,
se.machine machine,
su.TABLESPACE tablespace,
su.segtype,
su.CONTENTS CONTENTS
FROM v$session se, v$sort_usage suWHERE se.saddr=su.session_addr;
2、设置DROP_SEGMENTS事件,手工清理TEMP表空间:
alter sessionset events 'immediate trace name DROP_SEGMENTS level TS#+1';
http://www.tbdata.org/archives/203
http://www.dbaroad.me/archives/2009/07/drop_temp_wait_enq_ts.html