不要讓臨時表空間影響數據庫性能

Oracle臨時表空間目的就是為了存放一些臨時緩沖區數據，對查詢的中間結果進行排序。

臨時表空間是隨著業務釋放,系統會自動清理臨時表空間中的臨時對象，自動釋放臨時段。這里的釋放只是標記為空閑、可以重用，其實實質占用的磁盤空間并沒有真正釋放。這也是臨時表空間有時會不斷增大的原因.

如下操作會占用臨時表空間

create、rebuild、union 、insert （append）、 minus、Order by 、group by、Distinc、analyze、Sort-merge joins 、Hash join

 與臨時表空間相關的等待事件如下：

directpath write temp

direct path read temp 

enq:TS - contention

一、使用臨時表空間場合：

1.查詢的時候連表查詢中使用的表過多，存在如下連接方式Sort-mergejoins 、Hash join。

2.DML語句中有minus、Order by 、 group by、Distinc，查詢數據量很大

3.查詢的某些字段沒有建立索引,oracle將所有的數據都復制到臨時表空間

二、遇到臨時表空間影響數據庫性能處理方法

1、定位問題

 查看臨時表空間使用情況

  selectt1."Tablespace" "Tablespace",

      t1."Total (G)" "Total (G)",

      nvl(t2."Used (G)", 0) "Used(G)",

      t1."Total (G)" - nvl(t2."Used (G)", 0) "Free(G)"

  from (selecttablespace_name "Tablespace",

              to_char((sum(bytes / 1024 / 1024 / 1024)), '99,999,990.900') "Total(G)"

          fromdba_temp_files group by tablespace_name

        union

        selecttablespace_name "Tablespace",

              to_char((sum(bytes / 1024 / 1024 / 1024)), '99,999,990.900') "Total(G)"

          fromdba_data_files

         wheretablespace_name like 'TEMP%'

         group by tablespace_name) t1,

       (selecttablespace, round(sum(blocks) * 8 / 1024) "Used (G)" from

       v$sort_usage group by tablespace) t2

 wheret1."Tablespace" = t2.tablespace(+)

找出消耗sql的語句

select sql_id,count(*) from v$session whereevent like 'direct path % temp' group by sql_id desc;

可以通過如下sql的語句，定位出某段時間消耗臨時表空間的語句：

select sql_id, count(*)
  fromdba_hist_active_sess_history
 where sample_time >=
        to_timestamp('2017-8-01 16:00:00', 'yyyy-mm-ddhh34:mi:ss')
    and sample_time <=
        to_timestamp('2017-8-01 17:00:00', 'yyyy-mm-ddhh34:mi:ss')
    and event  in ('direct path write temp','direct path readtemp'   ,'enq: TS - contention' )
 group by sql_id desc;

處理問題

Kill session

1、 使用如下語句a查看一下認誰在用臨時段

SELECTse.username, se.SID, se.serial#, se.sql_address, se.machine, se.program,su.TABLESPACE,su.segtype, su.CONTENTS from

v$session se,v$sort_usage su WHERE se.saddr = su.session_addr

2、kill正在使用臨時段的進程

alter system killsession 'sid,serial#' immedoiate ;

使用診斷事件

1、查詢事件代碼

SQL>selectts#, name from sys.ts$ ;

TS# NAME

----------------------------------------

0 SYSTEM

1 UNDOTBS1

2 SYSAUX

3 TEMP

4 USERS

5 UNDOTBS2

2、 執行清理操作

SQL>altersession set events 'immediate trace name DROP_SEGMENTS level 4';

說明：temp表空間的TS#為 3, So TS#+ 1= 4。 

三、優化臨時表空間的方法

  業務層優化：

  1. 優化語句

  如 group by、order by 字段 

通過對這些字段建索引消除語句大量使用臨時表空間。

  2.改寫欠佳的sql語句。

   數據庫層優化

    1.考慮用臨時表空間組

使用臨時表空間組的好處：

•  防止一個臨時表空間出現空間不足的問題。

•  同一個用戶同時連接多個session時可以使用不同的臨時表空間。

• 在并行操作中可以并行使用多個臨時表空間

2.設置合理的PGA，減少臨時表空間使用