oracle换临时表执行慢,oracle程序性能优化实录-临时表空间

进行oracle程序性能优化时，还要注意undo空间和临时表空间的使用率，如果过高的话，也会导致程序运行缓慢。一般使用率至少需要小于80%。

程序运行中报错：ORA-01652:无法通过128

(在表空间TEMP中)扩展temp段。数据库原来没有这个现象，三表关联查询做insert，速度比较慢。后来在查询语句中加了full提示，，就出现了临时表空间不足。查询发现临时表空间的使用率已经达到100%了。

insert into

RT_2601_Detail(CHARGEMONTH,DEPT_ID,HOUSECODE,ITEMCODE,BWXS,AMJ,SFXZ)

select

'201703',b.dept_id,a.housecode,a.itemcode,c.BWXS,b.amj,a.itemcode

from sf_cwjz

a,fc_house b,of_station c

where

a.housecode=b.housecode and b.stat_id=c.stat_id

and

a.chargemonth='201703'

临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候，并且当PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序。像数据库中一些操作：CREATE

INDEX、ANALYZE、SELECT

DISTINCT、ORDER

BY、GROUP

BY、UNION

ALL、INTERSECT、MINUS、SORT-MERGE

JOINS、HASH

JOIN等都可能会用到临时表空间。当操作完成后，系统会自动清理临时表空间中的临时对象，自动释放临时段。这里的释放只是标记为空闲、可以重用，其实实质占用的磁盘空间并没有真正释放。这也是临时表空间有时会不断增大的原因。

临时表空间存储大规模排序操作(小规模排序操作会直接在RAM里完成，大规模排序才需要磁盘排序Disk

Sort)和散列操作的中间结果.它跟永久表空间不同的地方在于它由临时数据文件(temporary

files)组成的,而不是永久数据文件(datafiles)。临时表空间不会存储永久类型的对象，所以它不会也不需要备份。另外，对临时数据文件的操作不产生redo日志，不过会生成undo日志。

创建临时表空间或临时表空间添加临时数据文件时，即使临时数据文件很大，添加过程也相当快。这是因为ORACLE的临时数据文件是一类特殊的数据文件:稀疏文件(Sparse

File),当临时表空间文件创建时，它只会写入文件头部和最后块信息(only

writes to the header and last block of thefile)。它的空间是延后分配的.这就是你创建临时表空间或给临时表空间添加数据文件飞快的原因。

另外，临时表空间是NOLOGGING模式以及它不保存永久类型对象，因此即使数据库损毁，做Recovery也不需要恢复Temporary

Tablespace。

引起临时表空间增大主要使用在以下几种情况：

Øorder

by or group by (disc sort占主要部分)；

Ø索引的创建和重创建；

Ødistinct操作；

Øunion

& intersect & minus sort-merge joins；

ØAnalyze操作；

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

(1)查看临时表空间(dba_temp_files视图)(v_$tempfile视图)

--搜索

select

tablespace_name,file_name,bytes/1024/1024file_size,autoextensible

from dba_temp_files;

--sys用户查看

select status,enabled,name, bytes/1024/1024 file_size from

v_$tempfile;

(2)缩小临时表空间大小

alter database tempfile

'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TELEMT\TEMP01.DBF' resize

100M;

(3)扩展临时表空间：

方法一、增大临时文件大小：

SQL> alter database tempfile

‘/u01/app/oracle/oradata/orcl/temp01.dbf’ resize 100m;

方法二、将临时数据文件设为自动扩展：

SQL> alter database tempfile

‘/u01/app/oracle/oradata/orcl/temp01.dbf’ autoextend on next 5m

maxsize unlimited;

方法三、向临时表空间中添加数据文件：

SQL> alter tablespace temp add tempfile

‘/u01/app/oracle/oradata/orcl/temp02.dbf’ size 100m;

(4)创建临时表空间：

SQL> create temporary tablespace temp1 tempfile

‘/u01/app/oracle/oradata/orcl/temp11.dbf’ size 10M;

(5)更改系统的默认临时表空间：

--查询默认临时表空间

select * from database_properties where

property_name='DEFAULT_TEMP_TABLESPACE';

--修改默认临时表空间

alter database default temporary tablespace temp1;

所有用户的默认临时表空间都将切换为新的临时表空间：

select username,temporary_tablespace,default_ from

dba_users;

--更改某一用户的临时表空间：

alter user scott temporary tablespace temp;

(6)删除临时表空间

删除临时表空间的一个数据文件：

SQL> alter database tempfile

‘/u01/app/oracle/oradata/orcl/temp02.dbf’ drop;

删除临时表空间(彻底删除)：

SQL> drop tablespace temp1 including contents and datafiles

cascade constraints;

(7)查看临时表空间的使用情况(GV_$TEMP_SPACE_HEADER视图必须在sys用户下才能查询)

GV$TEMP_SPACE_HEADER视图记录了临时表空间的使用大小与未使用的大小

dba_temp_files视图的bytes字段记录的是临时表空间的总大小

SELECT temp_used.tablespace_name,

total - used as "Free",

total as "Total",

round(nvl(total - used, 0) * 100 / total,3) "Free

percent"

FROM (SELECT tablespace_name, SUM(bytes_used)/

1024 / 1024 used

FROM GV$TEMP_SPACE_HEADER

GROUP BY tablespace_name) temp_used,

(SELECT tablespace_name, SUM(bytes) / 1024/ 1024 total

FROM dba_temp_files

GROUP BY tablespace_name) temp_total

WHERE temp_used.tablespace_name =

temp_total.tablespace_name;

1、直接缩小temp表空间大小的方法

alter database tempfile 'd:powerdmsdb\dmsdb\temp01.dbf' resize

1024M;

此语句会直接修改temp表空间的大小，但可能会执行不成功，因为当temp使用率为100%或当前有会话占用时，temp表空间是无法缩小的。注意锁表情况，如果有锁表，需要先kill掉。

这种方法亲手实验过，太慢。

2、删除掉重建方法

(1)创建中转临时表空间

create temporary tablespace TEMP1 TEMPFILE

'H:\DATABASE\HX\temp101.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

'H:\DATABASE\HX\temp01.DBF'

SIZE 8192M REUSE AUTOEXTEND ON NEXT 256MMAXSIZE

UNLIMITED;

alter tablespace temp add tempfile

'H:\DATABASE\HX\TEMP02.DBF'

size 8192m autoextend on next 256m maxsize unlimited;

alter tablespace temp add tempfile

'H:\DATABASE\HX\TEMP03.DBF'

size 8192m autoextend on next 256m maxsize unlimited;

alter tablespace temp add tempfile

'H:\DATABASE\HX\TEMP04.DBF'

size 8192m autoextend on next 256m maxsize unlimited;

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

alter database default temporary tablespace temp;

(6)删除中转用临时表空间

drop tablespace temp1 including contents and datafiles;

这种方法亲手实验过，很快，10分钟以内搞定。