临时表空间满分析
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 ;
