oracletemp表空间增长过快的解决方法.docVIP

oracle temp表空间增长过快的解决方法 temp表空间暴涨的问题,以及如何回收临时表空间,由于版本的不同,方法显然也多种多样,但这些方法显示是治标不治本的办法,只有深刻理解temp表空间快速增加的原因,才能从根本上解决temp ts的问题。 是什么操作在使用temp ts?- 索引创建或重创建. - ORDER BY or GROUP BY - DISTINCT 操作. - UNION INTERSECT MINUS - Sort-Merge joins. - Analyze 操作- 有些异常将会引起temp暴涨 所以,在处理以上操作时,dba需要加倍关注temp的使用情况,v$sort_segment字典可以记载temp的比较详细的使用情况,而v$sort_usage将会告诉我们是谁在做什么. sql select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;TABLESPACE_NAME CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS 然后通过多表联接,我们可以找出更详细的操作: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 swhere 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;USERNAME SID EXTENTS SPACE TABLESPACE SEGTYPE 本例应该是由一些异常引起的,其实大多数情况下sort都会在几乎内结束,如果在sort操作的若干秒内刚好就捕获了该SQL,应该走狗屎运的事情,即你知道某个SQL将会发生sort操作,当你想捕抓它们时,发现它们已经sort完了,排序完毕后sort segment会被smon清除。但很多时间,我们则会遇到临时段没有被释放，temp表空间几乎满的状况,这时该如何处理呢？ metalink上推荐的方法收集整理如下-- 重启实例重启实例重启时，smon进程会完成临时段释放,不过很多的时侯我们的库是不允许down的,所以这种方法缺应用机会不多,不过这种方法还是很好用的,如果你的实例在重启后sort段没有被释放,这种情况就需要慎重对待。-- 修改参数 仅适用于8i及8i以下版本 SQL alter tablespace temp increase 1;SQL alter tablespace temp increase 0;-- 合并碎片SQL alter tablespace temp coalesce;-- 诊断事件SQL alter session set events immediate trace name DROP_SEGMENTS level 4 说明：temp表空间的TS#为3,So TS#+1 4-- 重建tempSQL alter database temp tempfile drop;SQL alter tablespace temp add tempfile ; 可以说,以上的方法都是治标不治本的,因为temp增长过快显然是由于disk sort过多,造成disk sort的原因也很多,比如sort area较小等原因,当然,sort area设置多大才合理？这个当然需要满足In-memory Sort大于99%以上哦。 Instance Efficiency Percentages Target 100% ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Buffer Nowait %: 100.00 Redo NoWait %: 99.99Buffer Hit %: 99.36 In-memory Sort %: 100.00Library Hit %: 99.87 Soft Parse %: 99.84Execute to Parse %: 1.17 Latch Hit %: 99.96Parse CPU to Parse Elapsd