enq: TS - contention 等待事件

OS:suse10
oracle 11g
4 RAC 节点 

根据awr 报告
TOP 5 的等待事件

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
DB CPU   13,297   36.83 
db file sequential read 1,547,067 11,087 7 30.71 User I/O
enq: TS - contention 895 4,214 4709 11.67 Other
db file parallel read 853,187 2,556 3 7.08 User I/O
gc buffer busy acquire 127,638 1,204 9 3.34 Cluster

enq: TS - contention也比较突出

同时检查
select sum(blocks)*8/1024/1024 from v$tempfile;

351.998870849609

已到达350GB的大小

本系统是OLAP的业务系统，有大量的复杂查询，及大理的临时表，这种应用的特性是
会引起大量的临时表空间消耗，从而引起了enq: TS等待

alter session set events 'immediate trace name DROP_SEGMENTS level 4';

OLAP 系统运行业务特点R出现了严重的enq: TS问题。

不过这个问题只在rac出现，单实例不会出现这种问题。
这个等待事件容易出现在一个SQL在一个节点消耗大量临时表空间，导致临时表空间分配非常不均衡的情况。
在RAC中，当一个会话需要临时段时，会从temp extent pool中申请，当一个temp extent被一个节点的会话申请后，
其他节点看不到也不能使用这些临时段；当一个节点把自己节点所属的临时段用完后，
会请求从temp extent pool继续申请；如果pool中已经没有可用的temp extent，
则Oracle会请求其他节点释放状态是free的extent到pool中。
而一般来说，每一次申请每个节点最多释放100个extent，在等待释放时，
可以看到enq: TS – contention等待。在
数据仓库中，这个回收速度根本跟不上消耗速度。当用完后会继续上述的申请过程，从而我们会长时间看到enq: TS – contention等待。

要解决这个问题，我们可以让系统尽快释放free状态的extent到pool中，避免在需要时才回收。
Oracle提供了一个命令可以快速回收temp extent：alter session set events 'immediate trace name DROP_SEGMENTS level N';
其中：N为临时表空间对应的TS#+1。
这个命令执行一次，就在一个节点释放100个extent，因此，可能需要循环多次执行。

下面这个过程把上面的命令封装了一下：
create PROCEDURE P_RELEASE_TEMP_SEG(P_GB NUMBER DEFAULT 50) IS
L_RELEASE_SQL VARCHAR2(256);
L_LOOP NUMBER;
L_KEEP_GB NUMBER := 10; --keep 10GB ,don't release
L_SESSION_COUNT NUMBER;--active session count
L_SESSION_THRETHOLD NUMBER:=20;
L_START_DATE DATE:=SYSDATE;
/*
定期把某个节点free的temporary segment释放到temporary segment pool，避免enq: TS contention问题
保留10GB空间不release，避免小temp空间需求频繁从temporary segment pool中申请资源

*/
BEGIN
FOR C_TEMP_SEG IN (SELECT T1.TS#, S.TABLESPACE_NAME, S.USED_EXTENTS, S.FREE_EXTENTS, S.CURRENT_USERS, S.FREE_BLOCKS, T2.NEXT_EXTENT, T2.BLOCK_SIZE, ROUND(S.FREE_BLOCKS *
T2.BLOCK_SIZE / 1024 / 1024 / 1024, 2) FREE_GB, ROUND(S.USED_BLOCKS *
T2.BLOCK_SIZE / 1024 / 1024 / 1024, 2) USED_GB
FROM V$SORT_SEGMENT S, V$TABLESPACE T1, DBA_TABLESPACES T2
WHERE T2.CONTENTS = 'TEMPORARY' AND
T1.NAME = T2.TABLESPACE_NAME AND
S.TABLESPACE_NAME = T2.TABLESPACE_NAME AND
S.FREE_BLOCKS * T2.BLOCK_SIZE / 1024 / 1024 / 1024 > P_GB)
LOOP
L_LOOP := FLOOR((C_TEMP_SEG.FREE_GB - L_KEEP_GB) /
(100 * C_TEMP_SEG.NEXT_EXTENT / 1024 / 1024 / 1024));
L_RELEASE_SQL := ' alter session set events ''immediate trace name DROP_SEGMENTS level ' ||
TO_CHAR(C_TEMP_SEG.TS# + 1) || '''';
SELECT COUNT(1) INTO L_SESSION_COUNT FROM V$SESSION S WHERE STATUS='ACTIVE' AND S.PADDR NOT IN (SELECT BP.PADDR FROM V$BGPROCESS BP WHERE BP.PADDR<>'00') ;
IF L_SESSION_COUNT<L_SESSION_THRETHOLD THEN
--当系统不是很繁忙时才会执行释放操作，否则可能会导致异常
FOR I IN 1 .. L_LOOP
LOOP
EXECUTE IMMEDIATE L_RELEASE_SQL;
--DBMS_OUTPUT.PUT_LINE(L_RELEASE_SQL);
END LOOP;
INSERT INTO TEMP_SEG_RELEASE_LOG
(INST_ID, USED_EXTENTS, USED_GB, FREE_EXTENTS, FREE_GB, END_DATE, LOOP_TIMES,START_DATE)
VALUES
(USERENV('instance'), C_TEMP_SEG.USED_EXTENTS, C_TEMP_SEG.USED_GB, C_TEMP_SEG.FREE_EXTENTS, C_TEMP_SEG.FREE_GB, SYSDATE, L_LOOP,L_START_DATE);
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

在每一个节点都定一个job，定期运行，就可以把这个问题解决