17,377
社区成员
发帖
与我相关
我的任务
分享temp表空间涨满，访问速度变慢的问题
不知道大家有没有遇到过这个问题，temp表空间很快使用率涨到了99%多，这时候访问数据库的效率变得奇慢无比，针对这样的问题有什么好的解决方法？
看到很多人遇到这样的问题，但并没有很好的解决，一方面可以从对数据的访问上简化查询之类的操作来优化，但毕竟是有限的
如果删除现有的temp表空间然后新建，会有什么影响吗？
看到很多人遇到这样的问题，但并没有很好的解决，一方面可以从对数据的访问上简化查询之类的操作来优化，但毕竟是有限的
如果删除现有的temp表空间然后新建，会有什么影响吗？
...全文
请发表友善的回复…
发表回复
vc555 2009-08-26
- 打赏
- 举报
关键是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.
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.
hailang1118 2009-08-26
- 打赏
- 举报
你先看看什么处理用TEMP表空间那么多，看看能不能改善。改善不了那么看看能不能加内存。
上面两个方法都不行，才改TEMP表空间。
TEMP表空间是在你的SGA内存不够用的时候，Oracle会把数据放到TEMP表空间里的。
改TEMP表空间，只能是治标不治本。
你改成很大，对你的性能改善是不会起多少作用的。
上面两个方法都不行，才改TEMP表空间。
TEMP表空间是在你的SGA内存不够用的时候，Oracle会把数据放到TEMP表空间里的。
改TEMP表空间，只能是治标不治本。
你改成很大，对你的性能改善是不会起多少作用的。
ojuju10 2009-08-26
- 打赏
- 举报
[Quote=引用 4 楼 pengjd 的回复:]
数据量在14-18G之间，temp表空间目前设置的是2G
按楼上的说法显然是不够用的…
[/Quote]
2G太小了，多增加一些临时表空间
数据量在14-18G之间，temp表空间目前设置的是2G
按楼上的说法显然是不够用的…
[/Quote]
2G太小了，多增加一些临时表空间
多壮志 2009-08-26
- 打赏
- 举报
其它的不说（例如排序之类的）。
主要就是临时表方面，写程序的时候务必要注意一点。
有的系统中如果大量利用on commit preserve rows类型的临时表，那么如果可能的话，尽量用过之后就trunacate掉，否则session存在的时候，依然会占用不少的空间。
主要就是临时表方面，写程序的时候务必要注意一点。
有的系统中如果大量利用on commit preserve rows类型的临时表，那么如果可能的话，尽量用过之后就trunacate掉，否则session存在的时候，依然会占用不少的空间。
Dave 2009-08-26
- 打赏
- 举报
网上的一种解决方法，楼主可以试试..
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
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
悲酥清风 2009-08-26
- 打赏
- 举报
数据量在14-18G之间，temp表空间目前设置的是2G
按楼上的说法显然是不够用的…
按楼上的说法显然是不够用的…
Dave 2009-08-26
- 打赏
- 举报
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上，性能上要高点..
建议：
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上，性能上要高点..
majy 2009-08-26
- 打赏
- 举报
把你的数据库的规模描述一下，比如有多少数据量，temp空间多少大
如果数据库使用率比较高的情况下，你的temp大小可以设置到你的主体数据量的大小。如果你有10G的主要数据，那么你的temp表空间也设置成10G吧，这样，基本上能满足使用要求了
如果数据库使用率比较高的情况下，你的temp大小可以设置到你的主体数据量的大小。如果你有10G的主要数据，那么你的temp表空间也设置成10G吧，这样，基本上能满足使用要求了
inthirties 2009-08-26
- 打赏
- 举报
temp表空间实际过程中确实存在着不断扩张的问题，不过扩张的过度或者频繁的扩展，应该是你的应用中有一些隐患造成的，毕竟temp是排序不够和其他需要排序的运算使用的。
建议你先，收缩一下temp表空间，然后多多监控一下你的数据库。
收缩表空间
http://blog.csdn.net/inthirties/archive/2009/05/29/4221938.aspx
建议你先，收缩一下temp表空间，然后多多监控一下你的数据库。
收缩表空间
http://blog.csdn.net/inthirties/archive/2009/05/29/4221938.aspx
Well 2009-08-26
- 打赏
- 举报
temp是当sga的内存不够。。也就是进行排序操作时会用到。。你可以监控一下数据库的有压力的SQL语句，是不是存在过多的不良的SQL语句全表扫描。。可以将temp重新创建。。删除再创建。。
傻儿哥 2009-08-26
- 打赏
- 举报
关于增大pga:
怎么查看加到多少就能减小使用temp表的概率呢?(增加了cache hit)
可以使用 EM CONTROL 的一个pga 查看功能,
可以使用mem advisor来调整 PGA.
怎么查看加到多少就能减小使用temp表的概率呢?(增加了cache hit)
可以使用 EM CONTROL 的一个pga 查看功能,
可以使用mem advisor来调整 PGA.
傻儿哥 2009-08-26
- 打赏
- 举报
把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);
在 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);
傻儿哥 2009-08-26
- 打赏
- 举报
[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]
不错,引用下,
关键是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]
不错,引用下,
zzzzzyyy 2009-08-26
- 打赏
- 举报
看到1楼的高手大哥的空间了，呵呵，学习了！
