temp表空间涨满，访问速度变慢的问题

[悲酥清风]

不知道大家有没有遇到过这个问题，temp表空间很快使用率涨到了99%多，这时候访问数据库的效率变得奇慢无比，针对这样的问题有什么好的解决方法？
看到很多人遇到这样的问题，但并没有很好的解决，一方面可以从对数据的访问上简化查询之类的操作来优化，但毕竟是有限的
如果删除现有的temp表空间然后新建，会有什么影响吗？

[zzzzzyyy]

看到1楼的高手大哥的空间了，呵呵，学习了！

[傻儿哥]

[Quote=引用 9 楼 vc555 的回复:]
关键是sql的disk sort过多造成。

1.优化sql，减少sort，或尽量将disk sort转为memory sort.

2.考虑加大pga设置：
show parameter pga_aggregate_target
show parameter workarea_size_policy

3.2g的temp表空间使用率99%，说明有近2g数据都是磁盘排序，sql查询变得奇慢是自然的。找出占用temp表空间最多的sql，优化。

如sql执行完，temp segment是可被重用的，使用率会下降。
上面方法都不能在优化，才考虑加大temp表空间，可drop an recreate或者maxsize unlimited.
[/Quote]

不错,引用下,

[傻儿哥]

把disk sort该为 mem sort 的一个建议:

在 SGA中设置
recycle buffer,(它可以缓冲大表)
设置keep buffer,它可以避免经常被访问的小表被淘汰.

查找你sql语句常用到的 大表,和小表.指定存储到keep buffer,或者recycle buffer里来.
语法如下
CREATE INDEX cust_idx …
STORAGE (BUFFER_POOL KEEP …);
ALTER TABLE oe.customers
STORAGE (BUFFER_POOL RECYCLE);
ALTER INDEX oe.cust_lname_ix
STORAGE (BUFFER_POOL KEEP);

[傻儿哥]

关于增大pga:
怎么查看加到多少就能减小使用temp表的概率呢?(增加了cache hit)
可以使用 EM CONTROL 的一个pga 查看功能,
可以使用mem advisor来调整 PGA.

[Well]

temp是当sga的内存不够。。也就是进行排序操作时会用到。。你可以监控一下数据库的有压力的SQL语句，是不是存在过多的不良的SQL语句全表扫描。。可以将temp重新创建。。删除再创建。。

[inthirties]

temp表空间实际过程中确实存在着不断扩张的问题，不过扩张的过度或者频繁的扩展，应该是你的应用中有一些隐患造成的，毕竟temp是排序不够和其他需要排序的运算使用的。

建议你先，收缩一下temp表空间，然后多多监控一下你的数据库。

收缩表空间
http://blog.csdn.net/inthirties/archive/2009/05/29/4221938.aspx

[majy]

把你的数据库的规模描述一下，比如有多少数据量，temp空间多少大

如果数据库使用率比较高的情况下，你的temp大小可以设置到你的主体数据量的大小。如果你有10G的主要数据，那么你的temp表空间也设置成10G吧，这样，基本上能满足使用要求了

[Dave]

oracle排序时，当PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序.

建议：
1. 重启数据库释放temp
2. 查看temp用户并kill掉
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

Alter system kill session 'sid,serial#';
3. 多增加几个temp文件，这样不同的事务可以在不同的temp上，性能上要高点..

[悲酥清风]

数据量在14-18G之间，temp表空间目前设置的是2G
按楼上的说法显然是不够用的…

[Dave]

网上的一种解决方法，楼主可以试试..
　　1、 确定TEMP表空间的ts#
　　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

[多壮志]

其它的不说（例如排序之类的）。
主要就是临时表方面，写程序的时候务必要注意一点。

有的系统中如果大量利用on commit preserve rows类型的临时表，那么如果可能的话，尽量用过之后就trunacate掉，否则session存在的时候，依然会占用不少的空间。

[ojuju10]

[Quote=引用 4 楼 pengjd 的回复:]
数据量在14-18G之间，temp表空间目前设置的是2G
按楼上的说法显然是不够用的…
[/Quote]

2G太小了，多增加一些临时表空间

[hailang1118]

你先看看什么处理用TEMP表空间那么多，看看能不能改善。改善不了那么看看能不能加内存。
上面两个方法都不行，才改TEMP表空间。
TEMP表空间是在你的SGA内存不够用的时候，Oracle会把数据放到TEMP表空间里的。
改TEMP表空间，只能是治标不治本。
你改成很大，对你的性能改善是不会起多少作用的。

[vc555]

关键是sql的disk sort过多造成。

1.优化sql，减少sort，或尽量将disk sort转为memory sort.

2.考虑加大pga设置：
show parameter pga_aggregate_target
show parameter workarea_size_policy

3.2g的temp表空间使用率99%，说明有近2g数据都是磁盘排序，sql查询变得奇慢是自然的。找出占用temp表空间最多的sql，优化。

如sql执行完，temp segment是可被重用的，使用率会下降。
上面方法都不能在优化，才考虑加大temp表空间，可drop an recreate或者maxsize unlimited.