ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 临时表空间的管理

临时表空间的管理

原创 Linux操作系统 作者:dragon路 时间:2011-07-17 22:00:03 0 删除 编辑

本文系转载与互联网,只是为了查看方便。如有不妥,可以随时删除。

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
------------------------------- ------------- ------------ ----------- -----------
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没有被释放,这种情况就需要慎重对待。
--
修改参数 (仅适用于8i8i以下版本)
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. (UGAshared pool中分配). 9i以前的版本,sort_area_size决定sort area的分配,9i及以后的版本,workarea_size_policyauto,pga_aggregate_target参数决定sort
area
的大于,这时的sort area应该是pga总内存的5%.workarea_size_policymanual,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" //
禁止合并碎片

通过合理地设置pgasort_area_size,可以消除大部分的dist sort,那其它的disk
sort
该如何处理呢?从sort引起的原因来看,索引/分析/异常引起的disk sort应该是很少的一部分,其它的应该是select中的distinct/union/group by/order by以及
merge sort join
,那我们如何捕获这些操作呢?通常如何有磁盘排序的SQL,它的逻辑读/物理读/排序/执行时间等都是比较大的,所以我们可以对v$sqlareav$sql字典进行过滤,经过长期地监控数据库,相信可以把这些害群之马找出来.即然找出这些引起disk sortSQL后怎么办呢?当然是对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
设置合理的pgasort_area_size
2
优化引起disk sortsql

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

上一篇: 会话及对象查询
请登录后发表评论 登录
全部评论

注册时间:2011-04-24

  • 博文量
    34
  • 访问量
    54194