關(guān)于消除temp ts暴漲的方法
經(jīng)常有人問temp表空間暴漲的問題,以及如何回收臨時(shí)表空間,由于版本的不同,方法顯然也多種多樣,但這些方法顯示是治標(biāo)不治本的辦法,只有深刻理解temp表空間快速增加的原因,才能從根本上解決temp ts的問題。
是什么操作在使用temp ts?
- 索引創(chuàng)建或重創(chuàng)建.
- ORDER BY or GROUP BY
- DISTINCT 操作.
- UNION & INTERSECT & MINUS
- Sort-Merge joins.
- Analyze 操作
- 有些異常將會(huì)引起temp暴漲
所以,在處理以上操作時(shí),dba需要加倍關(guān)注temp的使用情況,v$sort_segment字典可以記載temp的比較詳細(xì)的使用情況,而v$sort_usage將會(huì)告訴我們是誰在做什么.
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
然后通過多表聯(lián)接,我們可以找出更詳細(xì)的操作:
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
本例應(yīng)該是由一些異常引起的,其實(shí)大多數(shù)情況下sort都會(huì)在幾乎內(nèi)結(jié)束,如果在sort操作的若干秒內(nèi)剛好就捕獲了該SQL,應(yīng)該走狗屎運(yùn)的事情,即你知道某個(gè)SQL將會(huì)發(fā)生sort操作,當(dāng)你想捕抓它們時(shí),發(fā)現(xiàn)它們已經(jīng)sort完了,排序完畢后sort segment會(huì)被smon清除。但很多時(shí)間,我們則會(huì)遇到臨時(shí)段沒有被釋放，temp表空間幾乎滿的狀況,這時(shí)該如何處理呢？
metalink上推薦的方法收集整理如下
-- 重啟實(shí)例
重啟實(shí)例重啟時(shí)，smon進(jìn)程會(huì)完成臨時(shí)段釋放,不過很多的時(shí)侯我們的庫是不允許down的,
所以這種方法缺應(yīng)用機(jī)會(huì)不多,不過這種方法還是很好用的,如果你的實(shí)例在重啟后sort段
沒有被釋放,這種情況就需要慎重對待。
-- 修改參數(shù) (僅適用于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 '......';
可以說,以上的方法都是治標(biāo)不治本的,因?yàn)閠emp增長過快顯然是由于disk sort過多,造成disk sort的原因也很多,比如sort area較小等原因,當(dāng)然,sort area設(shè)置多大才合理？這個(gè)當(dāng)然需要滿足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
排序區(qū)域的分配
- 專用服務(wù)器分配sort area.
排序區(qū)域在PGA.
- 共享服務(wù)器分配sort area.
排序區(qū)域在UGA. (UGA在shared pool中分配).
在9i以前的版本,由sort_area_size決定sort area的分配,在9i及以后的版本,當(dāng)workarea_size_policy等auto時(shí),由pga_aggregate_target參數(shù)決定sortarea的大于,這時(shí)的sort area應(yīng)該是pga總內(nèi)存的5%.當(dāng)workarea_size_policy等manual時(shí),sort area的大小還是于sort_area_size決定.
無論是那個(gè)版本,如果sort area開得過小,In-memory Sort率較低,那temp表空間肯定會(huì)增長得很快,如果開得較高,在C/S結(jié)構(gòu)中將會(huì)導(dǎo)致內(nèi)存消耗嚴(yán)重(長連接較多).由于smon進(jìn)程每隔5分鐘都要對不再使用的sort segment進(jìn)行回收,如果你不想讓smon回收sort segment的話,可以使用以下兩個(gè)event寫入初始化參數(shù)文件,然后
重啟實(shí)例,這樣如果你的磁盤排序較多,很快就會(huì)漲暴磁盤......
event="10061 trace name context forever, level 10" //禁止加收
event="10269 trace name context forever, level 10" //禁止合并碎片
通過合理地設(shè)置pga或sort_area_size,可以消除大部分的dist sort,那其它的disk sort該如何處理呢？從sort引起的原因來看,索引/分析/異常引起的disk sort應(yīng)該是很少的一部分,其它的應(yīng)該是select中的distinct/union/group by/order by以及merge sort join啦,那我們?nèi)绾尾东@這些操作呢？通常如何有磁盤排序的SQL,它的邏輯讀/物理讀/排序/執(zhí)行時(shí)間等都是比較大的,所以我們可以對v$sqlarea或v$sql字典進(jìn)行過濾,經(jīng)過長期地監(jiān)控?cái)?shù)據(jù)庫,相信可以把這些害群之馬找出來.即然找出這些引起disk sort的SQL后怎么辦呢？當(dāng)然是對SQL進(jìn)行分析,盡而優(yōu)化之。
[oracle@www1 sql]$ more show_sql.sh
#!/bin/bash
sqlplus -s aaa/bbb
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
!
總結(jié),如何從根本上降低temp表空間的膨脹呢？方法有2個(gè)：
1 設(shè)置合理的pga或sort_area_size
2 優(yōu)化引起disk sort的sql
