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

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

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

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



SQL_ID都是一样的，那这个SQL是否有其特殊性呢？SEGTYPE为SORT表明这个临时段是“排序段”，用于SQL排序，大小居然也是一样，会话占用的临时段大小将近1GB，几百个会话加在一起，想不让临时表空间不撑大都难。

看看这个相同的SQL ID代表的SQL是什么：



很明显，这是一条非常简单的SQL，没有ORDER BY ，也没有GROUP BY、UNION、DISTINCT等需要排序的，TABLE_XXX是一张普通的表，而不是视图。出现了什么问题？会不会是v$sort_usage的SQL_ID列有错误？我们查看其中一个会话正在执行的SQL：



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语句，排序的数据量最大，我们来看看这条SQL是什么：



基于为客户保密的原因，SQL做了处理，能知道这条SQL的确是排了序就行，不过在SQL中看不出来的是，这条SQL没有任何实质性的能够过滤大量数据的条件。那么我们count(*)这条SQL语句看看：



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

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



问题分析到这里，很明显确认的是，应用存在问题，也许是业务逻辑问题；也许是根据前台选择的条件拼接的SQL，但是没有任何条件时就查询了所有数据。接下来就是找来开发人员，至于后面的事就跟这个主题没有太大关系。我们可以根据这个案例来进一步展开，去探寻临时表空间的更多知识点。

这里要展开的第1点是，v$sort_usage中的sql_id是不是会话正在执行的SQL，我们去看看视图fixed_View_definition就知道了：



原来在v$sort_usage的定义中，就明确地说明了SQL_ID列是v$session中的prev_sql_id列，而不是当前的SQL。至于为什么这样定义，老实说，现在还不知道。

不过从11.2.0.2这个版本开始，v$sort_usage的基表x$ktsso中增加了一个字段ktssosqlid，表示该临时段真正关联的SQL，以上述的测试结果为例，查询这个基表的结果如下：



可以看到的是我们查询到了真正产生临时段的SQL。

一直以来，v$sort_usage中的SQL_ID误导了很多人。所幸的是Oracle从11.2.0.2开始进行了弥补，MOS中有文档：



我们改良一下v$sort_usage，使用如下的查询来代替：



要展开的第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的泄露问题就复杂很多。

来看一个测试：



上述的代码执行完之后，在另一个窗口中，我们查询v$sort_usage：



可以看到，这个会话已经产生了类型为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临时段的泄露或临时段没有释放相关的文档。

最后，不管是什么情况导致的临时表空间被过多占用，通常重启应用能够释放掉临时段，因为会话退出后，相对应的临时段就会被释放。看来，“重启”大法在这种情况下就很有用。