不要让临时表空间影响数据库性能

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，减少临时表空间使用