前段時間DW系統切換到RAC叢集時，出現了嚴重的enq: TS問題。
[@more@]這次問題的現象是原來執行正常的任務忽然變得很慢，分析執行計劃後發現是因為執行計劃出錯，SQL走笛卡爾連線消耗了大量的臨時表空間，從而引起了enq: TS等待。
這次問題的直接解決方法就是修復執行計劃，避免笛卡爾連線，但是僅僅這樣做的話，還是存在風險。因為這個問題的根本原因是因為對臨時表空間的大量消耗導致，如果以後有超大資料量的計算，那麼不可避免需要大量的臨時表空間，這個時候，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，因此，可能需要迴圈多次執行。

下面這個過程把上面的命令封裝了一下：
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，定期執行，就可以把這個問題解決。

RAC中的enq: TS等待

相關文章