临时表空间使用率高问题的分析解释
当你监控temporary tablespace 的空闲空间，发现空闲空间并没有随着sort的结束而增加，甚至当前数据库没有任何客户端连接也是如此：仍然有大量的temporary类型的extent存在于temporary tablespace，有时候用户会在运行事务的时候遇到ora-1652（在temp表空间上），这个错误表明没有足够的空间，但是当用户的查询失败后，smon并没有去清理临时段。
Scope & Application
===================
如果TEMPORARY TABLESPACE的类型是TEMPORARY，TEMPORARY TABLESPACE里的使用过的空间是不会被释放的，除非shutdown。
如果是PERMANENT，SMON会在process不再使用临时段之后去做清理。
如果使用TEMPORARY类型的临时表空间，数据库刚刚startup后，第一个使用TEMPORARY tablespace进行排序的statement会创一个建sort segment，这个segment不会被释放，除非数据库restart，可以用V$SORT_SEGMENT察看当前的已分配了的sort segments地使用情况。
如果是用PERMANENT tablespace作排序，由smon负责在statement结束之后删除被创建的temporary segments，这样空间可以被其他对象使用。
使用如下查询：
select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE ,
TABLESPACE_NAME
from DBA_SEGMENTS
where SEGMENT_TYPE = ‘TEMPORARY’;
to give results similar to:
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
—– ———— ———— —————
SYS 4.2 TEMPORARY TEMP
Note: segment_name and tablepace_name are likely to be different.
tablespace的类型可以用如下查询：
select TABLESPACE_NAME,
CONTENTS
from DBA_TABLESPACES
where TABLESPACE_NAME in ( );
to give results similar to:
TABLESPACE_NAME CONTENTS
—————————— ———
TEMP TEMPORARY
TEMP1 PERMANENT
解释
=====
1)
对于使用了TEMPORARY类型的TEMPORARY TABLESPACE，临时段被使用后不被回收是正常的行为（7.3以后），这时如果有ora-1652发生，说明临时表空间是真得不够.
系统中的临时段在oracle startup之后被创建，并只有在oracle shutdown的时候被释放，如果存在大量的extent，一个可能的原因是你的storage子句的设置有问题。
考虑到性能原因，当一个temporary extent被分配的时候，tablespace会做一个标记，操作结束之后这个extent不会被释放或回收，相应的，这个extent被简单的标志为free，对于后面的sort操作是available的，这样就省去了系统分配和回收temporary extent的负载。
2）
对于在PERMANENT的tablespace里面使用temporary segment，记得检查parameter file里面没有设置如下参数：
event=”10061 trace name context forever, level 10″
event=”10269 trace name context forever, level 10″
这两个参数禁止smon去做temporary segment的clean up和coalescing，在oraus.msg里面可以看到具体的定义：
10061, 00000, “disable SMON from cleaning temp segments
10269, 00000, “Don’t do coalesces of free space in SMON”
// *Cause: setting this event prevents SMON from doing free space coalesces
（正常情况下，smon会负责定期做temporary segment的clean up和coalescing。具体作的方式metalink可以找到）
通过查询V$SORT_SEGMENT来看temp segment是free还是being used
For example:
select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;
>>> DURING the SORT you will see something like this:
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
—————- ———— ———– ———–
TEMP 590 590 0
>>> AFTER the SORT you will see something like this:
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
————— ———— ———— ———–
TEMP 590 0 590
通过这个查询可以看到在做sort的时候，segment标识为used，sort结束后，标志为free。实际上这时候相应的extents都被释放回SEP（sort extent pool）里面。（SEP是SGA里面存放temp extent的部分，具体描述在metalink也可以找到）
参考MOS文档：Temporary Tablespace, the Sort Extent Pool, and OPS (文档 ID 65973.1)
Temporary Segments Are Not Being De-Allocated After a Sort（Note:1039341.6）
如下为典型的临时表空间问题的排查步骤和命令脚本：
1.查看当前临时表空间配置情况–大小、数据文件个数、扩展情况等
2.哪些会话使用临时表空间较多，具体占用大小、临时段类型
3.找出使用临时表空间较多的SQL信息
4.找出使用临时表空间较多的SESSION信息
###############临时表空间使用过多的排查思路
1.查看当前临时表空间配置情况
–查看当前临时文件大小、是否支持自动扩展等
set linesize 180
col file_name for a50
col tablespace_name for a20
select file_name,file_id,tablespace_name,bytes/1024/1024/1024 gb,status,AUTOEXTENSIBLE,MAXBYTES from dba_temp_files;
set linesize 180
col name for a50
select * from V$TEMPFILE;
—查看临时表空间使用率
col tablespace_name for a30
select (s.tot_used_blocks/f.total_blocks)*100 as “percent used”
from
(select sum(used_blocks) tot_used_blocks from gv$sort_segment where tablespace_name=’TEMP’) s,
(select sum(blocks) total_blocks from dba_temp_files where tablespace_name=’TEMP’) f;
====
select f.TABLESPACE_NAME,f.total_MB,f.USED_MB,f.USED_MB/f.total_MB*100 as percent_used
from (select bb.TABLESPACE_NAME,bb.temp_MB total_MB,aa.used_blocks*P.VALUE/1024/1024 USED_MB
from (select a.TABLESPACE_NAME,sum(a.used_blocks) USED_blocks from gv$sort_segment a group by a.TABLESPACE_NAME) aa,
(select b.TABLESPACE_NAME,sum(b.BYTES/1024/1024) temp_MB from dba_temp_files b group by b.TABLESPACE_NAME) bb, SYS.V_$SYSTEM_PARAMETER P
where aa.TABLESPACE_NAME= bb.TABLESPACE_NAME and UPPER(P.NAME)=’DB_BLOCK_SIZE’) f;
###########################################################
2.查看当前哪个会话使用的临时段较大
—–找出使用临时空间最多的20个会话session_addr及其SQL_ID
set linesize 180
select * from
(select username,session_addr,sql_id,contents,segtype,blocks*8/1024 mb
from v$sort_usage order by blocks desc)
where rownum<11;
select * from v$temporary_lobs;
#####查出TEMP段大小以及进程相关信息
set linesize 180 pagesize 10000
col username for a10
col program for a20
col machine for a15
COL EVENT FOR A28
col sql_id for a15
col spid for a8
col TABLESPACE for a6
select * from (select s.username,b.spid,s.sid,s.program,s.machine,s.sql_id,s.event,to_char(s.LOGON_TIME,’yyyymmdd hh24:mi:ss’),u.tablespace, u.segtype, round(((u.blocks*P.VALUE)/1024/1024),2) MB
from v$session s, v$sort_usage u, SYS.V_$SYSTEM_PARAMETER P,v$process b
where s.saddr = u.session_addr
and s.paddr=b.addr
AND UPPER(P.NAME)=’DB_BLOCK_SIZE’
order by MB DESC) where rownum<31;
===prev_sql_id
========
select * from (select s.username,b.spid,s.sid,s.program,s.machine,s.prev_sql_id,to_char(s.LOGON_TIME,’yyyymmdd hh24:mi:ss’), u.segtype, round(((u.blocks*P.VALUE)/1024/1024),2) MB
from v$session s, v$sort_usage u, SYS.V_$SYSTEM_PARAMETER P,v$process b
where s.saddr = u.session_addr
and s.paddr=b.addr
AND UPPER(P.NAME)=’DB_BLOCK_SIZE’
order by MB DESC) where rownum<31;
===
—通过SQL_ID查SQL文本–如果SESSION已执行完排序SQL后并未有执行其它SQL，则可如下查：
select SQL_ID,sql_text from v$sqltext where sql_id in(’08yg5rb6upbra’,”) order by SQL_ID,piece;
###########################################################
3.找出使用临时表空间较多的SQL信息
v$sort_usage中的SQL_ID是会话前一条执行的SQL,–即对应V$SESSION中的字段PREV_SQL_ID.
–从11.2.0.2这个版本开始，v$sort_usage的基表x$ktsso中增加了一个字段ktssosqlid，表示该临时段真正关联的SQL。
以上述的测试结果为例，查询这个基表的结果如下：
select ktssosqlid,v$session.SADDR from x$ktsso, v$session where ktssoses = v$session.saddr
and ktssosno = v$session.serial#
and v$session.SADDR in(‘3231FE14′,’322DEA14′);
对于11.2.0.2之前版本的查询：
—通过第三步查出的session_addr关联SADDR查prev_sql_id–要求prev_sql_id执行后未执行新SQL
select sid,prev_sql_id, sql_id from v$session where saddr=’070000294AC0D050’;
#######################################
4.找出使用临时表空间较多的SESSION信息
–使用第三步查出的session_addr查出的使用临时表空间较多的SQL_ID及SESSION信息：
select a.sql_id sort_sql_id,b.sql_id,b.prev_sql_id, a.contents,a.segtype,a.blocks*8/1024 Mb,b.sid
from v$sort_usage a,v$session b
where a.session_addr=b.saddr and (b.sid=31 or b.saddr in(‘3231FE14′,’322DEA14′));
–根据session_addr查询会话的SID/终端信息等
col spid for a10
col machine for a30
select b.spid,a.sid,a.username,a.OSUSER,a.program,a.machine from v$session a,v$process b
where a.paddr=b.addr and a.type=’USER’ and a.SADDR in(‘3231FE14′,’322DEA14’);
################
适用11.2.0.2及以上，直接查出排序空间使用较多的SQL_ID
col username for a10
col osuser for a10
col tablespace for a15
select * from
(select k.inst_id “INST_ID”,
ktssoses “SADDR”,
sid,
ktssosno “SERIAL#”,
username “USERNAME”,
osuser “OSUSER”,
s.machine,
s.event,
ktssosqlid “SQL_ID”,
ktssotsn “TABLESPACE”,
decode(ktssocnt, 0, ‘PERMANENT’, 1, ‘TEMPORARY’) “CONTENTS”,
decode(ktssosegt, 1, ‘SORT’, 2, ‘HASH’, 3, ‘DATA’, 4, ‘INDEX’,
5, ‘LOB_DATA’, 6, ‘LOB_INDEX’ , ‘UNDEFINED’) “SEGTYPE”,
ktssofno “SEGFILE#”,
ktssobno “SEGBLK#”,
ktssoexts “EXTENTS”,
ktssoblks “BLOCKS”,
round(ktssoblks*p.value/1024/1024, 2) “SIZE_MB”,
ktssorfno “SEGRFNO#”
from x$ktsso k, v$session s,
(select value from v$parameter where name=’db_block_size’) p
where ktssoses = s.saddr
and ktssosno = s.serial#
order by size_mb)
where rownum<11;
====
select * from
(select
ktssoses “SADDR”,
s.sid,
username “USERNAME”,
s.machine,
s.event,
ktssosqlid “SQL_ID”,
ktssotsn “TABLESPACE”,
decode(ktssocnt, 0, ‘PERMANENT’, 1, ‘TEMPORARY’) “CONTENTS”,
decode(ktssosegt, 1, ‘SORT’, 2, ‘HASH’, 3, ‘DATA’, 4, ‘INDEX’,
5, ‘LOB_DATA’, 6, ‘LOB_INDEX’ , ‘UNDEFINED’) “SEGTYPE”,
round(ktssoblks*p.value/1024/1024, 2) “SIZE_MB”
from x$ktsso k, v$session s,
(select value from v$parameter where name=’db_block_size’) p
where ktssoses = s.saddr
and ktssosno = s.serial#
order by size_mb)
where rownum<11;
