消除temp ts暴涨的方法-技术开发专区

消除temp ts暴涨的方法

作者：xzh2000 编辑：魏国亮 2006-08-02 19:57

【IT168 技术文档】经常有人问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段没有被释放,这种情况就需要慎重对待。

-- 修改参数 (仅适用于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

第1页：消除temp ts暴涨的方法（上）第2页：消除temp ts暴涨的方法（下）

关注我们