oracle数据库表空间增长太快,oracle temp表空间增长过快的解决方法(zt)

经常有人问temp表空间暴涨的问题,以及如何回收临时表空间,由于版本的不同,方法显然也多种多样,但这些方法显示是治标不治本的办法,只有深刻理解temp表空间快速增加的原因,才能从根本上解决temp ts的问题。

[@more@]

是什么操作在使用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

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

TEMP 1 63872 30464 33408

sql>

SQL>select username,session_addr,sqladdr,sqlhash from v$sort_usage

USERNAME SESSION_ADDR SQLADDR SQLHASH

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

CYBERCAFE C0000000D7EF99E8 C0000000E1BFE970 4053158416

然后通过多表联接,我们可以找出更详细的操作:

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;

USERNAME SID EXTENTS SPACE TABLESPACE SEGTYPE

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

SQL_TEXT

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

CYBERCAFE 42 238 249561088 TEMP SORT

select 1 from sys.streams$_prepare_ddl p where ((p.global_flag=1 and :1 is null) or (p.global_flag=0 and p.usrid=:2)) and rownum=1

本例应该是由一些异常引起的,其实大多数情况下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

--重建temp

SQL>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.99

Buffer Hit %: 99.36 In-memory Sort %: 100.00

Library Hit %: 99.87 Soft Parse %: 99.84

Execute to Parse %: 1.17 Latch Hit %: 99.96

Parse CPU to Parse Elapsd %: 92.00 % Non-Parse CPU: 94.59

排序区域的分配-专用服务器分配sort area.排序区域在PGA.

-共享服务器分配sort area.排序区域在UGA. (UGA在shared pool中分配).在9i以前的版本,由sort_area_size决定sort area的分配,在9i及以后的版本,当workarea_size_policy等auto时,由pga_aggregate_target参数决定sort

area的大于,这时的sort area应该是pga总内存的5%.当workarea_size_policy等manual时,sort area的大小还是于sort_area_size决定.

无论是那个版本,如果sort area开得过小,In-memory Sort率较低,那temp表空间肯定会增长得很快,如果开得较高,在C/S结构中将会导致内存消耗严重(长连接较多).

由于smon进程每隔5分钟都要对不再使用的sort segment进行回收,如果你不想让smon回收sort segment的话,可以使用以下两个event写入初始化参数文件,然后重启实例,这样如果你的磁盘排序较多,很快就会涨暴磁盘......

event="10061 trace name context forever, level 10" //禁止加收event="10269 trace name context forever, level 10" //禁止合并碎片

通过合理地设置pga或sort_area_size,可以消除大部分的dist sort,那其它的disk

sort该如何处理呢？从sort引起的原因来看,索引/分析/异常引起的disk sort应该是很少的一部分,其它的应该是select中的distinct/union/group by/order by以及merge sort join啦,那我们如何捕获这些操作呢？通常如何有磁盘排序的SQL,它的逻辑读/物理读/排序/执行时间等都是比较大的,所以我们可以对v$sqlarea或v$sql字典进行过滤,经过长期地监控数据库,相信可以把这些害群之马找出来.即然找出这些引起disk sort的SQL后怎么办呢？当然是对SQL进行分析,尽而优化之。[oracle@www1 sql]$ more show_sql.sh

#!/bin/bash

sqlplus -s aaa/bbbcol sql_text format a81

col disk_reads format 999999.99

col bgets_per format 99999999.99

col "ELAPSD_TIME(s)" format 9999.99

col "cpu_time(s)" format 9999.99

set long 99999999999

set pagesize 9999

select address,hash_value,disk_reads/executions disk_reads,elapsed_time/1000000/executions as "ELAPSD_TIME(s)",

buffer_gets/executions bgets_per,executions,first_load_time as first_time,sql_text

from v$sql

where executions > 0 and (disk_reads/executions > 500 or buffer_gets/executions > 20000) and command_type = 3

order by 3,4;

--select s.disk_reads,s.buffer_gets/s.executions bgets_per,first_load_time,st.sql_text

-- from v$sql s,v$sqltext_with_newlines st

--where s.address=st.address and s.hash_value=st.hash_value

-- and s.disk_reads > 1000 or (s.executions > 0 and s.buffer_gets/s.executions > 50000)

--order by st.piece;

exit

!

总结,如何从根本上降低temp表空间的膨胀呢？方法有2个：1设置合理的pga或sort_area_size

2优化引起disk sort的sql