消除臨時表空間暴漲的方法

  關于消除temp ts暴漲的方法
    經常有人問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>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 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參數決定sortarea的大于,這時的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