ORACLE 临时表空间使用率过高的原因及解决方案

转载 2012年03月21日 13:34:45
 

      在数据库的日常学习中,发现公司生产数据库的默认临时表空间temp使用情况达到了30G,使用率达到了100%; 待调整为32G后,使用率还是为100%,导致磁盘空间使用紧张。根据临时表空间的主要是对临时数据进行排序和缓存临时数据等特性,待重启数据库后, temp会自动释放。于是想通过重启数据库的方式来缓解这种情况,但是重启数据库之后,发现临时表空间temp的使用率还是100%,一点没变。虽然运行 中应用暂时没有报什么错误,但是这在一定程度上存在一定的隐患,有待解决该问题。由于临时表空间主要使用在以下几种情况:

1、order by or group by (disc sort占主要部分);

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

3、distinct操作;

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

5、Analyze 操作;

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

Oracle临时表空间暴涨的现象经过分析可能是以下几个方面的原因造成的:

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

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

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

针对以上的分析,对查询的语句和索引进行了优化,情况得到缓解,但是需要进一步测试。

总结:

1.SQL语句是会影响到磁盘的消耗的,不当的语句会造成磁盘暴涨。

2.对查询语句需要仔细的规划,不要想当然的去定义一个查询语句,特别是在可以提供用户自定义查询的软件中。

3.仔细规划表索引。如果临时表空间是temporary的,空间不会释放,只是在sort结束后被标记为free的,如果是 permanent的,由SMON负责在sort结束后释放,都不用去手工释放的。查看有哪些用户和SQL导致TEMP增长的两个重要视图:v$ sort_usage和v$sort_segment。

通过查询相关的资料,发现解决方案有如下几种:

一、重建临时表空间temp

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

查看目前的Temporary Tablespace

SQL> select name from v$tempfile;

NAME

———————————————————————

D:\ORACLE\ORADATA\ORCL\TEMP01.DBF

SQL> select username,temporary_tablespace from dba_users;

USERNAME TEMPORARY_TABLESPACE

------------------------------ ------------------------------

MGMT_VIEW TEMP

SYS TEMP

SYSTEM TEMP

DBSNMP TEMP

SYSMAN TEMP

1.创建中转临时表空间

create temporary tablespace TEMP1 TEMPFILE 'D:\ORACLE\ORADATA\ORCL\temp02.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT 1M

MAXSIZE UNLIMITED;

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

alter database default temporary tablespace temp1;

3.删除原来临时表空间

drop tablespace temp including contents and datafiles;

4.重新创建临时表空间

create temporary tablespace TEMP TEMPFILE 'D:\ORACLE\ORADATA\ORCL\temp01.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE

UNLIMITED;

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

alter database default temporary tablespace temp;

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

drop tablespace temp1 including contents and datafiles;

以上的方法只是暂时释放了临时表空间的磁盘占用空间,是治标但不是治本的方法,真正的治本的方法是找出数据库中消耗资源比较大的sql语句,然后对其进行优化处理。下面是查询在sort排序区使用的执行耗时的SQL:

Select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))as Space,tablespace,segtype,sql_text

from v$sort_usage su,v$parameter p,v$session se,v$sql s

where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username,se.sid;

或是:

Select su.username,su.Extents,tablespace,segtype,sql_text

from v$sort_usage su,v$sql s

Where su.SQL_ID = s.SQL_ID;

注:如果原临时表空间无用户使用(select tablespace_name,current_users,total_blocks,used_blocks,free_blocks,free_blocks/total_blocks from v$sort_segment;),如果是文件系统可以看看文件的时间戳。

我们可以删除该表空间:如果原临时表空间还有用户在使用,你是删除不了这个表空间的!在一次生产环境的临时表空间切换中, 原临时表空间始终有用户在上面,即使我关闭了前台程序,也还是有用户,新的临时表空间已经没有用户在使用了。我估计用户进程已经死在原临时表空间了,后来 只有重新启动数据库才能把原来旧的临时表空间给删除。

二、修改参数(这个方案紧适用于8i及8i以下的版本)

修改一下TEMP表空间的storage参数,让Smon进程观注一下临时段,从而达到清理和TEMP表空间的目的。

SQL>alter tablespace temp increase 1;

SQL>alter tablespace temp increase 0;

三、Kill session

1、 使用如下语句a查看一下认谁在用临时段

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_usage su WHERE se.saddr = su.session_addr

2、kill正在使用临时段的进程

SQL>Alter system kill session 'sid,serial#';

3、把TEMP表空间回缩一下

SQL>Alter tablespace TEMP coalesce;

注:这处方法只能针对字典管理表空间(Dictionary Managed Tablespace)。于本地管理表空间(LMT:Local Managed Tablespace),不需要整理的。9i以后只能创建本地管理的表空间。

CREATE TABLESPACE TEST DATAFILE 'D:\TEST01.dbf' SIZE 5M EXTENT MANAGEMENT DICTIONARY

CREATE TABLESPACE TEST DATAFILE 'D:\TEST01.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL;

四、使用诊断事件,也是相对有效的一种方法

1、查询事件代码

SQL>select ts#, name from sys.ts$ ;

TS# NAME

---------- ------------------------------

0 SYSTEM

1 UNDOTBS1

2 SYSAUX

3 TEMP

4 USERS

5 UNDOTBS2

2、 执行清理操作

SQL>alter session set events 'immediate trace name DROP_SEGMENTS level 4';

说明:temp表空间的TS# 为 3, So TS#+ 1= 4。

oracle临时表空间过大的原因

2009-05-12 11:22

Oracle 临时表空间主要是用来做查询和存放一些缓存的数据的,磁盘消耗的一个主要原因是需要对查询的结果进行排序,如果没有猜错的话,在磁盘空间的(内存)的分配 上,Oracle使用的是贪心算法,如果上次磁盘空间消耗达到1GB,那么临时表空间就是1GB,如果还有增长,那么依此类推,临时表空间始终保持在一个 最大的上限。Oracle临时表空间暴涨的现象经过分析可能是以下几个方面的原因造成的。

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

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

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

针对以上的分析,对查询的语句和索引进行了优化,情况得到缓解,但是需要进一步测试。

       总结:

1.SQL语句是会影响到磁盘的消耗的,不当的语句会造成磁盘暴涨。

2.对查询语句需要仔细的规划,不要想当然的去定义一个查询语句,特别是在可以提供用户自定义查询的软件中。

3.仔细规划表索引。

如果临时表空间是temporary的,空间不会释放,只是在sort结束后被标记为free的,如果是permanent的,由SMON负责在sort结束后释放,都不用去手工释放的。查看有哪些用户和SQL导致TEMP增长的两个重要视图:

v$sort_usage和v$sort_segment

对于非LMT管理方式的TEMP表空间,最简单的方法是Metalink给出的一个方法:

修改一下TEMP表空间的storage参数,让Smon进程观注一下临时段,从而达到清理和TEMP表空间的目的。

SQL>alter tablespace temp default storage(pctincrease 1);

SQL>alter tablespace temp default storage(pctincrease 0);

而对于LMT管理方式的TEMP表空间,需要重新建立一个新的临时表空间,将所有用户的默认临时表空间指定到新的表空间上,然后offline旧的临时表空间,并drop掉。具体步骤如下:

首先查询用户的缺省临时表空间:

[oracle@jumper oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Apr 12 11:11:43 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning option

JServer Release 9.2.0.4.0 - Production

SQL> select username,temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE

------------------------------ ------------------------------

SYS                                 TEMP2

SYSTEM                         TEMP2

OUTLN                           TEMP2

EYGLE                            TEMP2

CSMIG                            TEMP2

TEST                               TEMP2

REPADMIN                     TEMP2

......

13 rows selected.

SQL> select name from v$tempfile;

NAME

---------------------------------------------------------------------

/opt/oracle/oradata/conner/temp02.dbf

/opt/oracle/oradata/conner/temp03.dbf

重建新的临时表空间并进行切换:

SQL> create temporary tablespace temp tempfile '/opt/oracle/oradata/conner/temp1.dbf' size 10M;

Tablespace created.

SQL> alter tablespace temp add tempfile '/opt/oracle/oradata/conner/temp2.dbf' size 20M;

Tablespace altered.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> select username,temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE

------------------------------ ------------------------------

SYS                                 TEMP

SYSTEM                         TEMP

OUTLN                           TEMP

EYGLE                            TEMP

CSMIG                           TEMP

TEST                               TEMP

REPADMIN                     TEMP

.......

13 rows selected.

如果原临时表空间无用户使用(select tablespace_name,current_users,total_blocks,used_blocks,free_blocks,free_blocks/total_blocks from v$sort_segment;),如果是文件系统可以看看文件的时间戳。

我们可以删除该表空间:(如 果原临时表空间还有用户在用,你是删除不了这个表空间的!在一次生产环境的临时表空间切换中,原临时表空间始终有用户在上面,即使我关闭了前台程序,也还 是有用户,新的临时表空间已经没有用户在使用了。我估计用户进程已经死在原临时表空间了。后来只有重新启动数据库才能把原来旧的临时表空间给删除。)

SQL> drop tablespace temp2;

Tablespace dropped.

SQL>

SQL> select name from v$tempfile;

NAME

---------------------------------------------------------------

/opt/oracle/oradata/conner/temp1.dbf

/opt/oracle/oradata/conner/temp2.dbf

SQL> select file_name,tablespace_name,bytes/1024/1024 MB,autoextensible

2 from dba_temp_files

3 /

FILE_NAME                              TABLESPACE_NAME              MB AUTOEXTENSIBLE

-------------------------------------- -------------------- ---------- --------------

/opt/oracle/oradata/conner/temp2.dbf   TEMP                         20 NO

/opt/oracle/oradata/conner/temp1.dbf   TEMP                         10 NO

drop tablespace temp including contents and datafiles; --将表空间的内容和数据文件一起删除。

下面是查询在sort排序区使用的执行耗时的SQL:

Select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))as    Space,tablespace,segtype,sql_text

from v$sort_usage su,v$parameter p,v$session se,v$sql s

where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr

order by se.username,se.sid

或是:

Select su.username,su.Extents,tablespace,segtype,sql_text

from v$sort_usage su,v$sql s

Where su.SQL_ID = s.SQL_ID

临时表空间空间不足解决方法

--临时表空间空间不足解决方法 --关闭自动增长 SYS@PROD2> select * from v$version; BANNER ---------------------...

表空间_临时表空间引起的错误:ora-01652 小例

原创作品,出自 “深蓝的blog” 博客,深蓝的blog: 报临时表空间不足错误:ora-01652 在进行insert操作的时候,遇到报错如下:提示临时表空间不足问题。 错误信息如下: 查看临时...

ORACLE 临时表空间使用率过高的原因及临时解决方案

数据库temp临时表空间增大,一般在数据安装时设置临时表空间大小,默认的情况下都是设置为自动增长。这样会引起一个问题:在数据库使用中temp表空间会自动扩展的越来越大,造成磁盘空间使用不足。 引...

Oracle 11G清理临时表空间

一. 临时表空间收缩   1.1 说明 关于Oracle 的临时表空间,之前有整理过一篇Blog: Oracle Temp 临时表空间 http://blog.csdn.ne...

Oracle查看表空间使用率(包括临时表空间)

oracle 查看表空间使用率(包括临时表) select * from ( Select a.tablespace_name, to_char(a.bytes/1024/1024,'99,999....

Delphi7高级应用开发随书源码

  • 2003年04月30日 00:00
  • 676KB
  • 下载

Scripts:查询使用临时表空间最多的10个SQL htemp10.sql

set linesize 200                                                                                    ...

如何处理Oracle中TEMP表空间满的问题(含查询表空间SQL)

如何处理Oracle中TEMP表空间满的问题2008-05-26 10:36正常来说,在完成Select语句、create index等一些使用TEMP表空间的排序操作后,Oracle是会自动释放掉临...

不要让临时表空间影响Oracle数据库性能

在Oracle数据库中进行排序、分组汇总、索引等到作时,会产生很多的临时数据。如有一张员工信息表,数据库中是安装记录建立的时间来保存的。如果用户查询时,使用Order BY排序语句指定按员工编号来排序...

Oracle中如何查找消耗资源较大的SQL

从V$SQLAREA中查询最占用资源的查询select b.username username,a.disk_reads reads, a.executions exec,a.disk_read...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ORACLE 临时表空间使用率过高的原因及解决方案
举报原因:
原因补充:

(最多只允许输入30个字)