臨時表空間滿分析

oracle 臨時表空間滿，業務無法進行，臨時表空間快速增長通過以下方式來分析

分析步驟

1. 查看臨時空間占用情況

• 總占用情況

select inst_id,  username, blocks*8/1024/1024 from  gv$tempseg_usage  order by blocks DESC;

2. 查看會話的占用臨時空間情況

• 通過會話和應用做分類

--11g
 SELECT a.inst_id,b.sid,b.serial#,sum(a.blocks*8/1024/1024),b.program
                                            FROM  gv$tempseg_usage a,gv$session b WHERE a.inst_id=b.inst_id  AND a.session_num=b.serial# AND a.SESSION_ADDR =b.SADDR  GROUP BY
                                          a.inst_id,b.sid,b.serial#,b.program ORDER BY sum(a.blocks*8/1024/1024) DESC 
--19c
SELECT a.sql_id_tempseg, a.inst_id,b.sid,b.serial#,sum(a.blocks*8/1024/1024),b.program
                                            FROM  gv$tempseg_usage a,gv$session b WHERE a.inst_id=b.inst_id  AND a.session_num=b.serial# AND a.SESSION_ADDR =b.SADDR  GROUP BY
                                        a.sql_id_tempseg,  a.inst_id,b.sid,b.serial#,b.program ORDER BY sum(a.blocks*8/1024/1024) DESC 

如果超過幾個g，可以采用kill處理
臨時解決方法

alter system kill session 'sid,serial#,@inst_id' immediate；

--19c 的話，可以直接具體的sql_id查看，通過上面的sql_id_tempseg 獲取到

SELECT * FROM gv$sql WHERE sql_id=

3. 查看會話空間增長情況

分析這個時間段07:00-08:30會話的，增量量情況如何

SELECT * FROM (select sample_time,session_id,session_serial#,sql_id,temp_space_allocated/1024/1024 temp_mb,

               temp_space_allocated/1024/1024-lag(temp_space_allocated/1024/1024,1,0) over (order by sample_time) as temp_diff

               from Gv$active_session_history

               where

               session_id=1201

               and session_serial#=55655 and

               SAMPLE_TIME>TO_dATE('2025-01-01 07:00:00','YYYY-MM-DD HH24:MI:SS') AND  SAMPLE_TIME<TO_dATE('2025-01-01 08:30:00','YYYY-MM-DD HH24:MI:SS')

               order by sample_time DESC) A ;

都是一點一點累増的，沒有任何釋放。其他會話查看也是類似的情況

4. 分析相關的sql

通過sql_id分析相對的sql語句，發現問題

• wm_concat：調用wmsys.wm_concat

5. WM_CONCAT為什么會導致增長

排查一些資料，應該是oracle的bug

• WMSYS.WM_CONCAT Should Not Be Used For Customer Applications, It Is An Internal Function [ID 1336219.1]
  Problem with WMSYS.WM_CONCAT Function after Upgrading [ID 1300595.1]
• SQL USING WM_CONCAT RUNS SLOWER IN 10.2.0.5 and 11.2.0.2 [ID 1393596.1]
• How to Release Temporary LOB Segments without Closing the JDBC Connection [ID 1384829.1]
• How to Release the Temp LOB Space and Avoid Hitting ORA-1652 [ID 802897.1]
  使用了clob或者blob字段后，WM_CONCAT導致，無法釋放臨時空間。

改進措施

1. 處理方案

• 殺了一些占用過高
• 把wm_CONCAT，自己來創建返回to_char類型，外面的附件有類似自定義的創建函數，來替換。或者11g以后使用listagg來替換
• 設置alter system set events '60025 trace name context forever';這樣還要看看效果

2. 預防措施

主要通過腳本，釘釘推送，加上閾值干預，kill session和增加臨時空間

• 監控腳本

select case
when round(g.usedtemp/f.zmax*100) >= 70 and round(g.usedtemp/f.zmax*100) < 80 then
'目前臨時表空間占用:' || round(g.usedtemp/f.zmax*100) || '%，可以適當處理'
when round(g.usedtemp/f.zmax*100) >= 80 and round(g.usedtemp/f.zmax*100) < 90 then
'目前臨時表空間占用:' || round(g.usedtemp/f.zmax*100) || '%，馬上處理'
when round(g.usedtemp/f.zmax*100) >= 90 then
'目前臨時表空間占用:' || round(g.usedtemp/f.zmax*100) || '%，快崩了，快快處理'
else
'目前臨時表空間占用:' || round(g.usedtemp/f.zmax*100) || '%'
end content,round(g.usedtemp/f.zmax*100) value
from (
select round(sum(decode(autoextensible, 'YES', maxbytes, 'NO', bytes) / 1048576/1024),2) zmax from dba_temp_files) f,(select round(sum(blocks*8/1024/1024),2) usedtemp from gv$tempseg_usage) g
where round(g.usedtemp/f.zmax*100)>=50
group by g.usedtemp,f.zmax

• 用python 可以開發一個類似的監控，如一下，利用AI寫代碼的功能，python 寫代碼還是很方便的
  

附件

1. 參考資料

• Analysing Temp usage on 11GR2 when Temp space is not released
  https://coskan.wordpress.com/2011/01/24/analysing-temp-usage-on-11gr2-temp-space-is-not-released/
• Use of WM_CONCAT Can Exhaust Temporary Tablespace in 11gR2 & 10gR2
  https://asanga-pradeep.blogspot.com/2012/10/use-of-wmconcat-can-exhaust-temporary.html

語法高亮讓你的代碼更易讀。

2. 自定義的wm_concat

--定義類型
-- WDHIS.WM_CONCAT_IMPL definition

CREATE OR REPLACE TYPE WDHIS.WM_CONCAT_IMPL                                                                                                                                                                                                                                         AS OBJECT
-- AUTHID CURRENT_USER AS OBJECT
(
CURR_STR VARCHAR2(32767), 
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER
);

TYPE BODY       WM_CONCAT_IMPL
IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
SCTX := WM_CONCAT_IMPL(NULL) ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
IF(CURR_STR IS NOT NULL) THEN
CURR_STR := CURR_STR || ',' || P1;
ELSE
CURR_STR := P1;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURNVALUE := CURR_STR ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
IF(SCTX2.CURR_STR IS NOT NULL) THEN
SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR ;
END IF;
RETURN ODCICONST.SUCCESS;
END;
END;

--自定義函數
CREATE OR REPLACE FUNCTION WDHIS.wm_concat(P1 VARCHAR2)
RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL ;