oracle临时表空间大量占用问题的处理

一个电信运营商客户的核心交易系统，临时表空间大量被占用，临时表空间被撑到了600GB。这样的问题复杂吗？取决于很多因素，不过今天所要讲的案例，并不复杂，如果我们对临时表空间在何种情况下使用有足够了解。
首先，我们要去检查是什么会话占用了临时表空间，具体占用了多少，临时段的具体类型是什么。正如我们要想知道这个月的花费过大，去分析原因时就要去看是哪些开销过大、开销了多少金额、开销的用途等。
这个步骤比较简单，查询v$sort_usage就可以了：
view plaincopy to clipboardprint?
select * from  
(select username,session_addr,sql_id,contents,segtype,blocks*8/1024/1024 gb  
from v$sort_usage order by blocks desc)  
where rownum<=200; 
 
USERNAME    SESSION_ADDR     SQL_ID        CONTENTS  SEGTYPE            GB 
----------  ---------------- ------------- --------- --------- ----------- 
XXXX        0700002949BCD8A0 291nk7db4bwdh TEMPORARY SORT      .9677734375 
XXXX        070000294BD99628 291nk7db4bwdh TEMPORARY SORT      .9677734375 
XXXX        070000294CD10480 291nk7db4bwdh TEMPORARY SORT      .9677734375 
XXXX        070000294DD1AC88 291nk7db4bwdh TEMPORARY SORT      .9677734375 
XXXX        070000294CD68D70 291nk7db4bwdh TEMPORARY SORT      .9677734375 
XXXX        070000294DBDF760 291nk7db4bwdh TEMPORARY SORT      .9677734375 
XXXX        070000294EDB5D10 291nk7db4bwdh TEMPORARY SORT      .9677734375 
XXXX        070000294FD7D818 291nk7db4bwdh TEMPORARY SORT      .9677734375 
...结果较多，忽略部分输出... 
SQL_ID都是一样的，那这个SQL是否有其特殊性呢？SEGTYPE为SORT表明这个临时段是“排序段”，用于SQL排序，大小居然也是一样，会话占用的临时段大小将近1GB，几百个会话加在一起，想不让临时表空间不撑大都难。
看看这个相同的SQL ID代表的SQL是什么：
view plaincopy to clipboardprint?
SQL> @sqlbyid 291nk7db4bwdh 
 
SQL_FULLTEXT 
-------------------------------------------------------------------------------------------------------------- 
 SELECT  A.LLEVEL,  A.LMODE  FROM TABLE_XXX A  WHERE A.SERVICE_NAME = :SERVICE_NAME AND STATE='Y' 
很明显，这是一条非常简单的SQL，没有ORDER BY ，也没有GROUP BY、UNION、DISTINCT等需要排序的，TABLE_XXX是一张普通的表，而不是视图。出现了什么问题？会不会是v$sort_usage的SQL_ID列有错误？我们查看其中一个会话正在执行的SQL：
view plaincopy to clipboardprint?
select sid,prev_sql_id, sql_id from v$session where saddr='070000294AC0D050'; 
 
        SID PREV_SQL_ID   SQL_ID 
----------- ------------- ------------- 
       3163 291nk7db4bwdh 
v$sort_usage中看到某个会话当前没有执行任何SQL，v$sort_usage中的SQL_ID是该会话前一条执行的SQL。为什么这里显示的是会话前一条执行的SQL，关于这个问题后面再详述，但至少有一点是可以判断的：如果大量的临时段都是由会话当前正在执行的SQL所产生的，那说明同时有几百个会话在执行需要大量临时空间的SQL，那系统早就崩溃了。所以这些临时表空间的占用不应该是由当前在执行的SQL所产生的，至少大部分不是。
大部分人的一个错误观点是，临时表空间中当前占用的空间是由会话当前正在执行的SQL所产生的。上面的一个简单的分析判断，情况不应该是这样。我们可以基于查询类SQL的执行过程来分析：
解析SQL语句(Parse)，生成一个游标(Open Cursor)。
执行SQL语句(Execute)，严格说就是执行新产生的游标。
在游标中取数据(Fetch)。
关闭游标(Close Cursor)。
关键在第3步。大家都知道取数据有一个array size的概念，表示一次从游标中取多少条数据，这是一个循环的过程。如果SQL查询得到的数据有1000条，每次取100条，则需要取10次。对于Fetch Cursor，有两点：
一个游标，或者说一条SQL语句，并不要求客户端把所有数据取完，只取了一部分数据就关闭游标也是可以的。
只要还没有关闭游标，数据库就要维护该游标的状态，如果是排序的SQL，也需要维持该SQL已经排好序的数据。
很显然，从上述第2点可以知道，如果一条SQL使用了临时段来排序，在SQL对应的游标没关闭的情况下，Oracle数据库不会去释放临时段，因为对于Oracle数据库来说，它不会知道客户端是否还要继续取游标的数据。
基于这样的分析，我们只需要随便选择一个占用了接近1GB的会话，查询v$open_cursor，查看其打开的游标中是否有大数据量排序的SQL：
view plaincopy to clipboardprint?
SQL> select sql_id,sorts,rows_processed/executions from v$sql 
  2  where parsing_schema_name='ACCT' and executions>0 and sorts>0 
  3  and sql_id in (select sql_id from v$open_cursor where sid=4505) 
  4  order by 3; 
   
  SQL_ID              SORTS ROWS_PROCESSED/EXECUTIONS 
------------- ----------- ------------------------- 
...省略部分输出结果... 
86vp997jbz7s6       63283                       593 
cfpdpb526ad43         592               35859.79899 
cfpdpb526ad43         188               55893.61702 
cfpdpb526ad43         443                     71000 
最后三个游标，实际上都是同一条SQL语句，排序的数据量最大，我们来看看这条SQL是什么：
view plaincopy to clipboardprint?
@sqlbyid cfpdpb526ad43 
 
SQL_FULLTEXT 
--------------------------------------------------------------------------------------------------- 
select ... from  c, b, a, d, e where ... order by d.billing_cycle_id desc,e.offer_name,a.acc_name 
基于为客户保密的原因，SQL做了处理，能知道这条SQL的确是排了序就行，不过在SQL中看不出来的是，这条SQL没有任何实质性的能够过滤大量数据的条件。那么我们count(*)这条SQL语句看看：
view plaincopy to clipboardprint?
COUNT(*) 
-------- 
12122698 
出来的结果居然有1200多万条数据，一个前台应用，不知道取1200多万条数据干嘛。但是从rows_processed/executions只有几万的结果来看，应用在取了几万条数据之后，由于某些原因（最大的可能就是不能再处理更多的数据），不再继续取数据，但是游标也一直没有关闭。
比较容易就能进行演示sort by时临时表空间的占用。
view plaincopy to clipboardprint?
根据dba_objects建一个测试表T1，使其数据量达到2000万行。 
 select count(*) from t1; 
 
   COUNT(*) 
----------- 
   20171200 
  
然后将SQL工作区设置为手动模式，设置sort内存大小限制为200M： 
 alter session set workarea_size_policy=manual; 
 alter session set sort_area_size=209715200; 
 
查询得到当前的会话sid： 
 select sid from v$mystat where rownum< =1; 
 
        SID 
----------- 
       2111 
 
执行这下面的代码： 
 declare 
  2     v_object_name varchar2(100); 
  3     v_dummy varchar2(100); 
  4  begin 
  5    for rec in (select * from t1 order by object_id,object_name) loop 
  6       select object_type into v_dummy from t1 where rownum<=1; 
  7       select object_name into v_object_name from dba_objects where object_id=rec.object_id; 
  8       dbms_lock.sleep(60*10); 
  9       exit; 
 10    end loop; 
 11  end; 
 12  / 
这段代码会打开一个游标，对2000万的数据量进行排序，然后在循环中只取一条数据，然后就进入sleep。在另一个窗口中监控到2111这个会话的event变成了PL/SQL lock timer，就去查询v$sort_usage: 
select a.sql_id sort_sql_id,b.sql_id,b.prev_sql_id, contents,segtype,blocks*8/1024/1024 gb  
  2  from v$sort_usage a,v$session b  
  3  where a.session_addr=b.saddr 
  4  and b.sid=2111; 
 
SORT_SQL_ID   SQL_ID        PREV_SQL_ID   CONTENTS  SEGTYPE            GB 
------------- ------------- ------------- --------- --------- ----------- 
fabh24prgk2sj bhzf316mdc07w fabh24prgk2sj TEMPORARY SORT      1.444824219 
可以看到v$sort_usage中的SQL_ID（即上述结果中SORT_SQL_ID）与v$session中的pre_sql_id一致，这条SQL是： 
 
@sqlbyid fabh24prgk2sj 
SQL_FULLTEXT 
-------------------------------------------------------- 
SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_ID=:B1 
 
而实际上当前正在执行的SQL是： 
 @sqlbyid bhzf316mdc07w 
 
SQL_FULLTEXT 
--------------------------------------------------------------------------- 
declare 
   v_object_name varchar2(100); 
   v_dummy varchar2(100); 
begin 
  for rec in (select * from t1 order by object_id,object_name) loop 
     select object_type into v_dummy from t1 where rownum<=1; 
     select object_name into v_object_name from dba_objects where object_id=rec.object_id; 
     dbms_lock.sleep(60*10); 
     exit; 
  end loop; 
end; 
问题分析到这里，很明显确认的是，应用存在问题，也许是业务逻辑问题；也许是根据前台选择的条件拼接的SQL，但是没有任何条件时就查询了所有数据。接下来就是找来开发人员，至于后面的事就跟这个主题没有太大关系。我们可以根据这个案例来进一步展开，去探寻临时表空间的更多知识点。
这里要展开的第1点是，v$sort_usage中的sql_id是不是会话正在执行的SQL，我们去看看视图fixed_View_definition就知道了：
view plaincopy to clipboardprint?
select x$ktsso.inst_id, username, username, ktssoses, ktssosno, prev_sql_addr, prev_hash_value, 
prev_sql_id, ktssotsn, decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY'), decode(ktssosegt, 1, 
'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED'), ktssofno, 
ktssobno, ktssoexts, ktssoblks, ktssorfno from x$ktsso, v$session where ktssoses = v$session.saddr 
and ktssosno = v$session.serial# 
原来在v$sort_usage的定义中，就明确地说明了SQL_ID列是v$session中的prev_sql_id列，而不是当前的SQL。至于为什么这样定义，老实说，现在还不知道。
不过从11.2.0.2这个版本开始，v$sort_usage的基表x$ktsso中增加了一个字段ktssosqlid，表示该临时段真正关联的SQL，以上述的测试结果为例，查询这个基表的结果如下：
view plaincopy to clipboardprint?
select ktssosqlid from x$ktsso, v$session where ktssoses = v$session.saddr 
  2  and ktssosno = v$session.serial# 
  3  and v$session.sid=2111; 
 
KTSSOSQLID 
------------- 
60t6fmjsw6v8y 
 
@sqlbyid 60t6fmjsw6v8y 
 
SQL_FULLTEXT 
--------------------------------------------------------------------------- 
SELECT * FROM T1 ORDER BY OBJECT_ID,OBJECT_NAME 
可以看到的是我们查询到了真正产生临时段的SQL。
一直以来，v$sort_usage中的SQL_ID误导了很多人。所幸的是Oracle从11.2.0.2开始进行了弥补，MOS中有文档：
Bug 17834663 - Include SQL ID for statement that created a temporary segment in GV$SORT_USAGE (文档 ID 17834663.8)
In previous versions, it was not possible to identify the SQL ID
of the statement that created a given temporary segment in
eg. (G)V$SORT_USAGE.
@ Via the fix for bug:8806817 we added the SQL ID to the X$KTSSO
@ table (ktssosqlid), but it was not exposed in the GV$SORT_USAGE
@ view until now.
The SQL ID of the statement is in column SQL_ID_TEMPSEG
Note that this fix cannot be provided as an interim patch.
我们改良一下v$sort_usage，使用如下的查询来代替：
view plaincopy to clipboardprint?
select k.inst_id "INST_ID", 
       ktssoses "SADDR", 
       sid, 
       ktssosno "SERIAL#", 
       username "USERNAME", 
       osuser "OSUSER",  
       ktssosqlid "SQL_ID", 
       ktssotsn "TABLESPACE", 
       decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY') "CONTENTS", 
       --注意在12c的v$sort_usage定义中TABLESPACE和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#; 
要展开的第2点是，v$sort_usage中的SEGTYPE列的不同的值各有什么意义：
SORT：SQL排序使用的临时段，包括order by、group by、union、distinct、窗口函数(window function)、建索引等产生的排序。
DATA：临时表(Global Temporary Table)存储数据使有的段。
INDEX：临时表上建的索引使用的段。
HASH：hash算法，如hash连接所使用的临时段。
LOB_DATA和LOB_INDEX：临时LOB使用的临时段。
根据上述的段类型，大体可以分为三类占用：
SQL语句排序、HASH JOIN占用
临时表占用
临时LOB对象占用
临时表空间的异常占用，一种缓步增长的，另一种情况：一下撑满的通常是一个极大数据量的排序或极大的索引的创建。缓步增长的情况，跟系统的内存被逐渐占用类似，存在“泄露”。比如排序的SQL游标没有关闭，比如本文的案例；比如会话级临时表产生了数据后一直没有清除；临时LOB对象没有清理或泄露。前两种比较好去分析处理，但是临时LOB的泄露问题就复杂很多。
来看一个测试：
view plaincopy to clipboardprint?
 select sid from v$mystat where rownum<=1; 
 
        SID 
----------- 
       1773 
 declare 
  2    v_lob clob; 
  3  begin 
  4    dbms_lob.createtemporary(v_lob,true); 
  5    dbms_lob.writeappend(v_lob,1000,lpad('a',1000,'a')); 
  6  end; 
  7  / 
上述的代码执行完之后，在另一个窗口中，我们查询v$sort_usage：
view plaincopy to clipboardprint?
select a.sql_id sort_sql_id,b.sql_id,b.prev_sql_id, contents,segtype,blocks*8/1024/1024 gb  
  2  from v$sort_usage a,v$session b  
  3  where a.session_addr=b.saddr 
  4  and b.sid=1773; 
 
SORT_SQL_ID   SQL_ID        PREV_SQL_ID   CONTENTS  SEGTYPE            GB 
------------- ------------- ------------- --------- --------- ----------- 
9babjv8yq8ru3               9babjv8yq8ru3 TEMPORARY LOB_DATA  .0004882813 
 
@sqlbyid 9babjv8yq8ru3 
 
SQL_FULLTEXT 
--------------------------------------------------------------------------- 
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; 
可以看到，这个会话已经产生了类型为LOB_DATA的临时段。虽然SQL代码已经执行完成，会话已经处于空闲状态，但是临时段仍然存在着。
Oracle中的LOB变量，类似于C语句中的指针，或者类似于JAVA代码中的数据库连接Connection，是需要释放的。上述有问题的代码，缺少了释放LOB的代码：dbms_log.freetemporary(v_lob)。好在对于这种情况，Oracle提供了一个补救措施，就是设置60025事件可以自动清理掉不活动的LOB，只需要在参数文件中加上event='60025 trace name context forever'。
在Oracle数据库中，xmltype类型内部也实际上是LOB类型，xmltype类型的数据操作可能会产生较多的LOB临时段。lob类型的字段上的更改操作，比如lob拼接等，同样会产生LOB临时段。如果在v$sort_usage中发现大量的LOB类型的临时段，那么通常是由于代码存在问题，没有释放LOB，或者是由于Oracle本身的BUG。在MOS上，如果以lob temporary关键字搜索，会发现相当多的关于lob临时段的泄露或临时段没有释放相关的文档。
最后，不管是什么情况导致的临时表空间被过多占用，通常重启应用能够释放掉临时段，因为会话退出后，相对应的临时段就会被释放。看来，“重启”大法在这种情况下就很有用