一个电信运营商客户的核心交易系统，临时表空间大量被占用，临时表空间被撑到了600GB。这样的问题复杂吗？取决于很多因素，不过今天所要讲的案例，并不复杂，如果我们对临时表空间在何种情况下使用有足够了解。

首先，我们要去检查是什么会话占用了临时表空间，具体占用了多少，临时段的具体类型是什么。正如我们要想知道这个月的花费过大，去分析原因时就要去看是哪些开销过大、开销了多少金额、开销的用途等。

这个步骤比较简单，查询v$sort_usage就可以了：

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是什么：

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：

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：

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是什么：

@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语句看看：

   COUNT(*)
-----------
   12122698

出来的结果居然有1200多万条数据，一个前台应用，不知道取1200多万条数据干嘛。但是从rows_processed/executions只有几万的结果来看，应用在取了几万条数据之后，由于某些原因(最大的可能就是不能再处理更多的数据)，不再继续取数据，但是游标也一直没有关闭。

比较容易就能进行演示sort by时临时表空间的占用。

根据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. MySQL的临时表    (阅读：2354)