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