经常看到有人说看到temporary tablespace空间不释放等等的问题,整理一篇metalink上的相关文章,入门级的,还算浅显吧。基本上就是翻译了。
有疑问的欢迎讨论。
参考metalink:Temporary Segments Are Not Being De-Allocated After a Sort(Note:1039341.6)
sort之后临时段不回收
描述
======
当你监控temporary tablespace 的空闲空间,发现空闲空间并没有随着sort的结束而增加,甚至当前数据库没有任何客户端连接也是如此:仍然有大量的temporary类型的extent存在于temporary tablespace,有时候用户会在运行事务的时候遇到ora-1652(在temp表空间上),这个错误表明没有足够的空间,但是当用户的查询失败后,smon并没有去清理临时段。
Scope & Application
===================
如果TEMPORARY TABLESPACE的类型是TEMPORARY,TEMPORARY TABLESPACE里的使用过的空间是不会被释放的,除非shutdown。
如果是PERMANENT,SMON会在process不再使用临时段之后去做清理。
如果使用TEMPORARY类型的临时表空间,数据库刚刚startup后,第一个使用TEMPORARY tablespace进行排序的statement会创一个建sort segment,这个segment不会被释放,除非数据库restart,可以用V$SORT_SEGMENT察看当前的已分配了的sort segments地使用情况。
如果是用PERMANENT tablespace作排序,由smon负责在statement结束之后删除被创建的temporary segments,这样空间可以被其他对象使用。
使用如下查询:
select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE ,
TABLESPACE_NAME
from DBA_SEGMENTS
where SEGMENT_TYPE = 'TEMPORARY';
to give results similar to:
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
----- ------------ ------------ ---------------
SYS 4.2 TEMPORARY TEMP
Note: segment_name and tablepace_name are likely to be different.
tablespace的类型可以用如下查询:
select TABLESPACE_NAME,
CONTENTS
from DBA_TABLESPACES
where TABLESPACE_NAME in ( );
to give results similar to:
TABLESPACE_NAME CONTENTS
------------------------------ ---------
TEMP TEMPORARY
TEMP1 PERMANENT
解释
=====
1)
对于使用了TEMPORARY类型的TEMPORARY TABLESPACE,临时段被使用后不被回收是正常的行为(7.3以后),这时如果有ora-1652发生,说明临时表空间是真得不够:-)
系统中的临时段在oracle startup之后被创建,并只有在oracle shutdown的时候被释放,如果存在大量的extent,一个可能的原因是你的storage子句的设置有问题。
考虑到性能原因,当一个temporary extent被分配的时候,tablespace会做一个标记,操作结束之后这个extent不会被释放或回收,相应的,这个extent被简单的标志为free,对于后面的sort操作是available的,这样就省去了系统分配和回收temporary extent的负载。
2)
对于在PERMANENT的tablespace里面使用temporary segment(别让概念搞昏了,呵呵),记得检查parameter file里面没有设置如下参数:
event="10061 trace name context forever, level 10"
event="10269 trace name context forever, level 10"
这两个参数禁止smon去做temporary segment的clean up和coalescing,在oraus.msg里面可以看到具体的定义:
10061, 00000, "disable SMON from cleaning temp segments
10269, 00000, "Don't do coalesces of free space in SMON"
// *Cause: setting this event prevents SMON from doing free space coalesces
(正常情况下,smon会负责定期做temporary segment的clean up和coalescing。具体作的方式metalink可以找到)
通过查询V$SORT_SEGMENT来看temp segment是free还是being used
For example:
select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;
>>> DURING the SORT you will see something like this:
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
---------------- ------------ ----------- -----------
TEMP 590 590 0
>>> AFTER the SORT you will see something like this:
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
--------------- ------------ ------------ -----------
TEMP 590 0 590
通过这个查询可以看到在做sort的时候,segment标识为used,sort结束后,标志为free。实际上这时候相应的extents都被释放回SEP(sort extent pool)里面。(SEP是SGA里面存放temp extent的部分,具体描述在metalink也可以找到)
最后,把tablespace的类型改为temporary可以这样:
ALTER TABLESPACE TEMPORARY;
如果temp被扩大 关于回收temp空间大小 9i就是shutdown在startup也不能 回收空间
只能重建才能回收空间
temporary 类型的temporary tablespace,是没有回收这个概念,用完了的temp extent,只是标志为free,不回收。temp表空间为autoextend,所以一直扩张.
看我翻译的文章,当你使用的temporary tablespace 的类型是PERMANENT的时候,smon才会负责回收空间。如果你的类型时TEMPORARY,smon不会起作用,这跟表空间是否 locally managed (还是字典管理表空间)是不同概念吧。
可以将表空间的缺省存储参数pctincrease改为非0。一般将其设为1,如:
alter tablespace temp
default storage(pctincrease 1);
这样smon便会将自由范围自动合并。也可以手工合并自由范围:
alter tablespace temp coalesce; (注:这个仅仅对使用字典管理的temp空间有用。)
昨天在做测试的时候发现一个非常奇怪的问题:在程序的查询模块中做查询的时候,开始速度很快,但是过了一段时间以后速度就变慢,最后干脆就报错,不工作了。在排错的过程中,发现Oracle临时表空间暴涨,达到了几十个GB,在Oracle中对Session进行跟踪,发现磁盘空间还在不停的消耗,几乎是每隔5s,临时表空间就会增长500MB左右,最后报错的原因应该是因为没有磁盘空间可以分配造成的。这是一件十分恐怖的事情。
我们知道Oracle临时表空间主要是用来做查询和存放一些缓存的数据的,磁盘消耗的一个主要原因是需要对查询的结果进行排序,如果没有猜错的话,在磁盘空间的(内存)的分配上,Oracle使用的是贪心算法,如果上次磁盘空间消耗达到1GB,那么临时表空间就是1GB,如果还有增长,那么依此类推,临时表空间始终保持在一个最大的上限。像上文提到的恐怖现象经过分析可能是以下几个方面的原因造成的。
1. 没有为临时表空间设置上限,而是允许无限增长。但是如果设置了一个上限,最后可能还是会面临因为空间不够而出错的问题,临时表空间设置太小会影响性能,临时表空间过大同样会影响性能,至于需要设置为多大需要仔细的测试。
2.查询的时候连表查询中使用的表过多造成的。我们知道在连表查询的时候,根据查询的字段和表的个数会生成一个迪斯卡尔积,这个迪斯卡尔积的大小就是一次查询需要的临时空间的大小,如果查询的字段过多和数据过大,那么就会消耗非常大的临时表空间。
3.对查询的某些字段没有建立索引。Oracle中,如果表没有索引,那么会将所有的数据都复制到临时表空间,而如果有索引的话,一般只是将索引的数据复制到临时表空间中。
针对以上的分析,对查询的语句和索引进行了优化,情况得到缓解,但是需要进一步测试。
总结:
1.SQL语句是会影响到磁盘的消耗的,不当的语句会造成磁盘暴涨。
2.对查询语句需要仔细的规划,不要想当然的去定义一个查询语句,特别是在可以提供用户自定义查询的软件中。
3.仔细规划表索引。
[@more@]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8570952/viewspace-911103/,如需转载,请注明出处,否则将追究法律责任。