《oracle temp表空间增长过快的解决方法》由会员分享，可在线阅读，更多相关《oracle temp表空间增长过快的解决方法（4页珍藏版）》请在金锄头文库上搜索。
1、oracle temp 表空间增长过快的解决方法经常碰到 temp 表空间暴涨的问题, 以及如何回收临时表空间,由于版本的不同,方法显然也多种多样,但这些方法显示是治标不治本的办法, 只有深刻理解 temp 表空间快速增加的原因,才能从根本上解决 temp ts 的问题。 是什么操作在使用 temp ts?- 索引创建或重创建. - ORDER BY or GROUP BY - DISTINCT 操作 . - UNION & INTERSECT & MINUS - Sort-Merge joins. - Analyze 操作- 有些异常将会引起 temp 暴涨 所以,在处理以上操作时,dba
2、需要加倍关注 temp 的使用情况,v$sort_segment 字典可以记载 temp 的比较详细的使用情况, 而 v$sort_usage 将会告诉我们是谁在做什么. sqlselect 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 33408sqlSQLselect usern
3、ame,session_addr,sqladdr,sqlhash from v$sort_usageUSERNAME SESSION_ADDR SQLADDR SQLHASH- - - -CYBERCAFE C0000000D7EF99E8 C0000000E1BFE970 4053158416 然后通过多表联接,我们可以找出更详细的操作:SQLselect se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value) as Space,tablespace,segtype,sql_text from v$sort_usage
4、 su,v$parameter p,v$session se,v$sql swhere 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 SORTselect 1 from sys.stream
5、s$_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 表空间几乎满的状况,这时该如何处
6、理呢？ metalink 上推荐的方法收集整理如下- 重启实例重启实例重启时，smon 进程会完成临时段释放 ,不过很多的时侯我们的库是不允许 down的,所以这种方法缺应用机会不多,不过这种方法还是很好用的, 如果你的实例在重启后 sort 段没有被释放,这种情况就需要慎重对待。- 修改参数 (仅适用于 8i 及 8i 以下版本)SQLalter tablespace temp increase 1;SQLalter tablespace temp increase 0;- 合并碎片SQLalter tablespace temp coalesce;- 诊断事件SQLalter sessio
7、n set events immediate trace name DROP_SEGMENTS level 4 说明：temp 表空间的 TS#为 3,So TS#+1=4- 重建 tempSQLalter database temp tempfile . drop;SQLalter tablespace temp add tempfile .; 可以说,以上的方法都是治标不治本的, 因为 temp 增长过快显然是由于 disk sort 过多,造成disk sort 的原因也很多,比如 sort area 较小等原因,当然,sort area 设置多大才合理？这个当然需要满足 In-memo
8、ry 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 %: 92.00 % Non-Parse CPU: 94.59 排序区域的分配
9、- 专用服务器分配 sort area. 排序区域在 PGA.- 共享服务器分配 sort area. 排序区域在 UGA. (UGA 在 shared pool 中分配). 在 9i 以前的版本 ,由 sort_area_size 决定 sort area 的分配,在 9i 及以后的版本,当workarea_size_policy 等 auto 时,由 pga_aggregate_target 参数决定 sortarea 的大于,这时的 sort area 应该是 pga 总内存的 5%.当 workarea_size_policy 等manual 时,sort area 的大小还是于 so
10、rt_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 /禁止加收e
11、vent=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,它的逻辑读/物理读/排序/执行时间等都是比较大的 ,所以
12、我们可以对 v$sqlarea 或 v$sql 字典进行过滤,经过长期地监控数据库,相信可以把这些害群之马找出来. 即然找出这些引起 disk sort 的SQL 后怎么办呢？当然是对 SQL 进行分析,尽而优化之。oraclewww1 sql$ more show_sql.sh #!/bin/bashsqlplus -s aaa/bbbcol sql_text format a81col disk_reads format 999999.99col bgets_per format 99999999.99col ELAPSD_TIME(s) format 9999.99col cpu_tim
13、e(s) format 9999.99set long 99999999999set pagesize 9999select 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$sqlwhere executions 0 and (disk_reads/executi
14、ons 500 or buffer_gets/executions 20000) and command_type = 3order 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
15、0 and s.buffer_gets/s.executions 50000)-order by st.piece;exit! 总结,如何从根本上降低 temp 表空间的膨胀呢？方法有 2 个：1 设置合理的 pga 或 sort_area_size2 优化引起 disk sort 的 sql 清理临时段可以使用如下办法、 使用如下语句查看一下认谁在用临时段SELECT username,sid,serial#,sql_address,machine,program,tablespace,segtype, contents FROM v$session se,v$sort_usage suWHERE se.
