Oracle临时表空间使用过高及解决方案

 

一、Oracle临时表空间作用

 

     在Oracle数据库进行排序、分组汇总、索引等动作时，会产生很多的临时数据，如有一张员工信息表，数据库中是按照记录建立的时间来保存的。如果用户查询是，使用Order BY排序语句指定按员工编号来排序，那么排序后产生的所有记录就是临时数据。通常情况下，Oracle会先将这些临时数据存放到内存的PGA（程序全局区）内。但是这个分区容量是有限的。当这个分区的大小不足以容纳排序后所产生的记录时，数据库系统就会将临时数据存放到临时表空间中。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。临时表空间主要使用在以下几种情况：

 

  1、order by or group by（disc sort占主要部分）；

  2、索引的创建和重创建；

  3、distinct操作；

  4、union & intersect & minus sort-merge joins；

  5、Analyze操作；

  6、有些异常也会引起TEMP暴涨。

 

二、Oracle临时表空间暴涨原因

Ø没有为临时表空间设置上限，而是允许无限增长。但是如果如果设置了一个上限，最后可能还是会面临因为空间不够而出错的问题，临时表空间设置太小会影响性能，临时表空间过大同样会影响性能，至于需要设置多大需要仔细的测试。

Ø查询的时候连表查询中使用的表过多造成的。我们知道在连表查询的时候，根据查询的字段和表的个数会生成一个笛卡尔积，这个笛卡尔积的大小就是一次查询需要的临时空间的大小，如果查询的字段过多和数据过大，那么就会消耗非常大的临时表空间。

Ø对查询的某些字段没有建立索引。Oracle中，如果表没有索引，那么会将所有的数据都复制到临时表空间中，而如果有索引的话，一般只是将索引的数据复制到临时表空间中。

 

三、解决TEMP临时表空间过大

ü直接缩小TMEP表空间大小

   alter database tempfile ‘临时文件路径’resize 1024M;

   此语句会直接修改TEMP表空间的大小，但可能会执行不成功，因为当TEMP使用率为100%或者当前有

   会话占用时，TEMP表空间是无法缩小的。使用数据库管理员用户查询当前会话：

   select  se.username,se.sid,se.serial#,se.sql_address,se.machine,se.program,su.tablespace,su.segtype,su.contents

   from v$session se, v$sort_usagesu where se.saddr = su.session_addr

   利用此sql语句查询当前会话，然后kill当前会话：

   alter system kill session ‘sid，serial#’

   执行此语句后再对TEMP表空间resize空间大小就可以了。

   注：此方法只是对TEMP表空间做临时性的缩小，以后还会继续增大。

ü重建TEMP临时表空间

   Temporary tablespace是不能直接drop默认的临时表空间的，不过我们可以通过以下方法达到。

   查看目前的Temporary Tablespace

 

  1、创建中转临时表空间

   create temporary tablespace TEMP1 ‘/oradata/qct/temp02.DBF’ size 1000M REUSE AUTOEXTEND ON

   NEXT 1M MAXSIZE UNLIMITED；

  2、改变缺省临时表空间为刚刚创建的新临时表空间temp1

  3、删除原来临时表空间

   drop tablespace temp including contents and datafiles;

  4、重新创建临时表空间

   create temporary tablespace TEMP TEMPFILE’/oradata/qct/temp01.DBF’ SIZE 1000M REUSE

   AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

  5、重置缺省临时表空间为新建的temp表空间

  alter database default temporary tablespace temp;

  6、删除中转用临时表空间

  drop tablespace temp1 including contents and datafiles;

  以上的方法只是暂时释放了临时表空间的磁盘占用空间，是治标但不是治本的方法，真正治本的方法是找出

  数据库中消耗资源较大的sql语句，然后对其进行优化处理。

 

四、监控临时表空间使用情况

Oracle用户查看哪些用户和SQL导致TEMP增长有两个重要视图：v$sort_usagev和$sort_segment。

通过下面语句可查询在sort排序区使用的执行耗时的用户SQL：

select * from (select sess.sid,segtype,blocks,sql_text from v$sort_usagesu,v$sessionsess,v$sqlsql where su.session_addr = sess.saddr and sql.address = sess.sql_address order by blocks desc) where rownum <= 5

 或者select su.username,su.extents,tablespace,segtype,sql_text from v$sort_usage,v$sql s where su.sql_id = s.sql_id

 

注：如果原临时表空间无用户使用（ select tablespace_name,current_users,total_blocks,used_blocks,free_blocks,round((free_blocks/total_blocks), 4) * 100 || '%' as freeprc from v$sort_segment）

 

通过下面的SQL可以查看哪些用户在使用临时段

select se.username,se.sid,se.serial#,se.sql_address,se.machine,se.program,su.tablespace,su.segtype,su.contents from v$session se, v$sort_usagesu where se.saddr = su.session_addr;

 

通过下面的SQL可以监控临时表空间使用率

select round((f.BYTES_FREE + f.BYTES_USED) / 1024 / 1024, 2) "total MB",round(((f.BYTES_FREE + f.BYTES_USED) - nvl(p.BYTES_USED, 0)) / 1024 / 1024,2) "Free MB",round(nvl(p.BYTES_USED, 0) / 1024 / 1024, 2) "Used MB",round(nvl(p.BYTES_USED, 0) / 1024 / 1024, 2) /round((f.BYTES_FREE + f.BYTES_USED) / 1024 / 1024, 2) * 100 || '%' "UPRC",d.file_name "fileName" from sys.v_$temp_space_headerf,Dba_Temp_Filesd,SYS.v_$temp_Extent_Pool p where f.TABLESPACE_NAME(+) = d.tablespace_name and f.FILE_ID(+) = d.file_id and p.FILE_ID(+) = d.file_id and d.tablespace_name = 'TEMP';

 

或者

select A.tablespace_name, total "total MB", used "Used MB", (used / total * 100 || '%') uprc from (select tablespace_name, sum(bytes) / 1024 / 1024 total from dba_temp_files group by tablespace_name) A left outer join (select tablespace_name,sum(BYTES_USED) / 1024 / 1024 used from v_$temp_Extent_Pool group by tablespace_name) B on b.tablespace_name = a.tablespace_name where A.tablespace_name = 'TEMP‘;

 

注：

可通过修改Nagios（开源IT基础设施监控系统）的check_oracle脚本来实时监控Oracle临时表空间并自动告警运维人员。

Nagios监控系统安装请参考：

http://www.ibm.com/developerworks/cn/lunix/1309_luojun_nagios/

修改Nagios的check_oracle脚本请参考：

http://skymax.blog.51cto.com/165901/103331/