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--當系統不是很繁忙時才會執行釋放操作，否則可能會導致異常
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，定期執行，就可以把這個問題解決