ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 消除temp ts暴涨的方法

消除temp ts暴涨的方法

原创 Linux操作系统 作者:听海★蓝心梦 时间:2009-03-11 19:18:07 0 删除 编辑

最近碰到ORACLE TEMP表空间暴涨的问题,查了很多资料,觉得这篇是比较满意的,拿来做个笔记,顺便有喜欢的朋友也可以参考下。

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

是什么操作在使用temp tbs?
- 索引创建或重创建.
- ORDER BY or GROUP BY
- DISTINCT 操作.
- UNION & INTERSECT & MINUS
- Sort-Merge joins.
- Analyze 操作
- 有些异常将会引起temp暴涨

所以,在处理以上操作时,dba需要加倍关注temp的使用情况,v$sort_segment字典可以记载temp的比较详细的使用情况,而v$sort_usage将会告诉我们是谁在做什么.

--查看TEMP表空间的使用情况
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

--查看SQLHASH的TEMP表空间
SQL>select username,session_addr,sqladdr,sqlhash from v$sort_usage;

USERNAME   SESSION_ADDR                    SQLADDR                         SQLHASH
---------------      ---------------------             -------------------------------       -----------------------
TESTUSER     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
--------------     --------  ----------   --------------   ---------------      -------------    ----------------------------------------------------------------
TESTUSER     42        238       249561088    TEMP              SORT          select * from …………

本例应该是由一些异常引起的,其实大多数情况下sort都会在几乎内结束,如果在sort操作的若干秒内刚好就捕获了该SQL,应该走狗屎运的事情,即你知道某个SQL将会发生sort操作,当你想捕抓它们时,发现它们已经sort完了,排序完毕后sort segment会被smon清除。但很多时间,我们则会遇到临时段没有被释放,temp表空间几乎满的状况,这时该如何处理呢?

一、metalink上推荐的方法收集整理如下
1、--重启实例
重启实例重启时,smon进程会完成临时段释放,不过很多的时侯我们的库是不允许down的,所以这种方法缺应用机会不多,不过这种方法还是很好用的,如果你的实例在重启后sort段没有被释放,这种情况就需要慎重对待。

2、-- 修改参数 (仅适用于8i及8i以下版本)
SQL>alter tablespace temp increase 1;
SQL>alter tablespace temp increase 0;

3、-- 合并碎片
SQL>alter tablespace temp coalesce;

4、-- 诊断事件
SQL>alter session set events 'immediate trace name DROP_SEGMENTS level 4'
说明:temp表空间的TS#为3,So TS#+1=4

5、-- 重建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

二、排序区域的分配
1、-- 专用服务器分配sort area.
排序区域在PGA.
2、-- 共享服务器分配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

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/751371/viewspace-566864/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-02-18

  • 博文量
    256
  • 访问量
    1202194