ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 转:Oracle临时表空间问题解答

转:Oracle临时表空间问题解答

原创 Linux操作系统 作者:yanyp 时间:2009-04-15 11:10:07 0 删除 编辑

转:Oracle临时表空间问题解答

查看Oracle回滚段的周详情况,连同对性能的影响: 
select a.name, b.extents, b.rssize,b.xacts,b.waits,b.gets,b.optsize,b.status 
from v$rollname a,v$rollstat b 
where a.usn = b.usn 


[转载]临时表空间问题 
[整理]关于temporary tablespace,翻译一篇入门文档。 
经常看到有人说看到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临时表空间为何暴涨? 

昨天在做测试的时候发现一个非常奇怪的问题:在程序的查询模块中做查询的时候,开始速度很快,但是过了一段时间以后速度就变慢,最后干脆就报错,不工作了。在排错的过程中,发现Oracle临时表空间暴涨,达到了几十个GB,在Oracle中对Session进行跟踪,发现磁盘空间还在不停的消耗,几乎是每隔5s,临时表空间就会增长500MB左右,最后报错的原因应该是因为没有磁盘空间可以分配造成的。这是一件十分恐怖的事情。 
        我们知道Oracle临时表空间主要是用来做查询和存放一些缓存的数据的,磁盘消耗的一个主要原因是需要对查询的结果进行排序,如果没有猜错的话,在磁盘空间的(内存)的分配上,Oracle使用的是贪心算法,如果上次磁盘空间消耗达到1GB,那么临时表空间就是1GB,如果还有增长,那么依此类推,临时表空间始终保持在一个最大的上限。像上文提到的恐怖现象经过分析可能是以下几个方面的原因造成的。 
        1. 没有为临时表空间设置上限,而是允许无限增长。但是如果设置了一个上限,最后可能还是会面临因为空间不够而出错的问题,临时表空间设置太小会影响性能,临时表空间过大同样会影响性能,至于需要设置为多大需要仔细的测试。 
        2.查询的时候连表查询中使用的表过多造成的。我们知道在连表查询的时候,根据查询的字段和表的个数会生成一个迪斯卡尔积,这个迪斯卡尔积的大小就是一次查询需要的临时空间的大小,如果查询的字段过多和数据过大,那么就会消耗非常大的临时表空间。 
       3.对查询的某些字段没有建立索引。Oracle中,如果表没有索引,那么会将所有的数据都复制到临时表空间,而如果有索引的话,一般只是将索引的数据复制到临时表空间中。 
       针对以上的分析,对查询的语句和索引进行了优化,情况得到缓解,但是需要进一步测试。 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/230160/viewspace-589221/,如需转载,请注明出处,否则将追究法律责任。

下一篇: ttrtyrt
请登录后发表评论 登录
全部评论

注册时间:2009-04-12

  • 博文量
    139
  • 访问量
    174214