某一天某个系统打破了往日的平静,temp表空间迅猛增长,突破了90%,而且还在一直增长,冲击100%大关,要知道temp表空间如果不能正常用了,估计系统就hang住了,应用无法运转,这是致命的!
看到这么危险的事情,自然要先解决燃眉之急,看看有空间赶紧扩容让使用率先降下来,但是temp表空间还在一直涨,扩容不是解决问题的根本办法。
要想真正解决问题,必须找到问题的根源!那么究竟是什么导致的呢,我们先来看看是谁在占用temp。
col sid for a35
col username for a20
col TABLESPACE for a30
col SQL_ID for a30
SELECT a.username,
a.sid||','||a.serial# sid,
a.sql_id,
b.tablespace,
b.contents,
b.blocks*c.block_size/1024/1024 size_m
FROM v$session a, v$tempseg_usage b, dba_tablespaces c
WHERE a.saddr = b.session_addr
and b.tablespace=c.tablespace_name
ORDER BY b.tablespace, b.blocks;
使用上述脚本查到了占用temp的大批量会话(这里仅显示部分处理后的结果)
查看占用temp较多的会话的详细信息。
col sample_time for a30
select sample_time,sample_id,SQL_ID,event,TEMP_SPACE_ALLOCATED from v$active_session_history where sample_time between
to_date('2/9/2017 6:29:0','mm/dd/yyyy hh24:mi:ss') and to_date('2/15/2017 17:30:0','mm/dd/yyyy hh24:mi:ss') and session_id=5550 and SESSION_SERIAL#=5779
order by SAMPLE_id desc;
然后,取其中一条sql查看语句内容:
Select SQL_TEXT from v$sqltext a, v$active_session_history b where a. SQL_ID=b. SQL_ID and SQL_ID=’ 70jt12utfswhb’;
SQL_TEXT
----------------------------------------------------------------
select xxx ,yyy, zzz, wm_concat(s.status) ,wm_concat(s. no) from 表1 s where………
乍一看,也没什么问题,唯一特殊的一点是这个函数wm_concat不太常见,这到底是个什么函数,很有可能根源就是它?!
查阅oracle相关文档发现,wm_concat函数是oracle内部函数,用来将列转行,该函数在不同数据库版本返回值类型不一样,在11.2.0.1里返回VARCHAR2,在11.2.0.2以后返回CLOB;该函数的变化更新不会通知用户,没有保证,所以oracle不推荐客户使用该函数,建议在11GR2上使用LISTAGG函数代替。
而发生问题的数据库正是11.2.0.4版本,返回的是CLOB! 查看其它相关的sql,发现都有这个函数!
很明显LOB是会占用temp表空间的,下面进一步证实:
select * from v$temporary_lobs order by 2;
查到的会话与之前完全一致,可以看到LOB字段以CACHE_LOBS形式存在。
进一步使用如下脚本查询占用TEMP的详细信息,查到的会话信息也与之前查询完全一致,占用TEMP表空间的段类型为LOB_DATA,进一步验证了判断。
col username for a10
col event for a35
select sid,b.segtype,a.username,BLOCKS*8192/1024/1024 size_mb,status,event,LOGON_TIME
from v$session a,v$tempseg_usage b
where a.saddr=b.session_addr
and b.segtype='LOB_DATA'
order by 4;
原来大量的wm_concat函数调用导致了大量的LOB占用temp表空间现象,就是wm_concat!wm_concat!wm_concat!根源就在这里!
虽然SQL语句没有显式使用LOB,但是使用了wm_concat,这个函数会“偷偷地”返回大量的LOB,防不胜防! 这是一个陷阱,千万别跳!
最后,将SQL语句中的wm_concat用LISTAGG代替后,temp表空间使用情况稳定下来了,系统又重新恢复平静。
如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!
赞1
添加新评论0 条评论