<div class="entry" style="margin:20px 0px 5px;padding:0px 0px 5px;border-bottom-width:1px;border-bottom-style:dashed;border-bottom-color:#CCCCCC;color:#333333;font-family:'Lucida Grande', 'Segoe UI', 'Bitstream Vera Sans', Tahoma, Verdana, Arial, sans-serif;font-size:13px;line-height:20.7999992370605px;white-space:normal;background-color:#FFFFFF;"> 一个电信运营商客户的核心交易系统，临时表空间大量被占用，临时表空间被撑到了600GB。这样的问题复杂吗？取决于很多因素，不过今天所要讲的案例，并不复杂，如果我们对临时表空间在何种情况下使用有足够了解。 首先，我们要去检查是什么会话占用了临时表空间，具体占用了多少，临时段的具体类型是什么。正如我们要想知道这个月的花费过大，去分析原因时就要去看是哪些开销过大、开销了多少金额、开销的用途等。 这个步骤比较简单，查询v$sort_usage就可以了： <div class="dp-highlighter" style="margin:18px 0px;padding:1px 0px 0px;font-family:Consolas, 'Courier New', Courier, mono;font-size:12px;width:653.390625px;overflow:auto;background-color:#E7E5DC;"> <div class="bar" style="margin:0px;padding:0px 0px 0px 45px;"> <div class="tools" style="margin:0px;padding:3px 8px 10px 10px;font-stretch:normal;font-size:9px;line-height:normal;font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;color:silver;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;background-color:#F8F8F8;"> <a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">view plain</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">copy to clipboard</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">print</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">?</a> </div> </div> <ol start="1" class="dp-sql" style="margin:0px 0px 1px 45px;padding:0px;list-style:decimal-leading-zero;color:#5C5C5C;background-color:#FFFFFF;"> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> select * from    </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> (select username,session_addr,sql_id,contents,segtype,blocks*8/1024/1024 gb    </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> from v$sort_usage order by blocks desc)    </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> where rownum<=200;   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">    </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> USERNAME    SESSION_ADDR     SQL_ID        CONTENTS  SEGTYPE            GB   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> ----------  ---------------- ------------- --------- --------- -----------   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> XXXX        0700002949BCD8A0 291nk7db4bwdh TEMPORARY SORT      .9677734375   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> XXXX        070000294BD99628 291nk7db4bwdh TEMPORARY SORT      .9677734375   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> XXXX        070000294CD10480 291nk7db4bwdh TEMPORARY SORT      .9677734375   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> XXXX        070000294DD1AC88 291nk7db4bwdh TEMPORARY SORT      .9677734375   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> XXXX        070000294CD68D70 291nk7db4bwdh TEMPORARY SORT      .9677734375   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> XXXX        070000294DBDF760 291nk7db4bwdh TEMPORARY SORT      .9677734375   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> XXXX        070000294EDB5D10 291nk7db4bwdh TEMPORARY SORT      .9677734375   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> XXXX        070000294FD7D818 291nk7db4bwdh TEMPORARY SORT      .9677734375   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> ...结果较多，忽略部分输出...   </li> </ol> </div> SQL_ID都是一样的，那这个SQL是否有其特殊性呢？SEGTYPE为SORT表明这个临时段是“排序段”，用于SQL排序，大小居然也是一样，会话占用的临时段大小将近1GB，几百个会话加在一起，想不让临时表空间不撑大都难。 看看这个相同的SQL ID代表的SQL是什么： <div class="dp-highlighter" style="margin:18px 0px;padding:1px 0px 0px;font-family:Consolas, 'Courier New', Courier, mono;font-size:12px;width:653.390625px;overflow:auto;background-color:#E7E5DC;"> <div class="bar" style="margin:0px;padding:0px 0px 0px 45px;"> <div class="tools" style="margin:0px;padding:3px 8px 10px 10px;font-stretch:normal;font-size:9px;line-height:normal;font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;color:silver;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;background-color:#F8F8F8;"> <a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">view plain</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">copy to clipboard</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">print</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">?</a> </div> </div> <ol start="1" class="dp-sql" style="margin:0px 0px 1px 45px;padding:0px;list-style:decimal-leading-zero;color:#5C5C5C;background-color:#FFFFFF;"> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> SQL> @sqlbyid 291nk7db4bwdh   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">    </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> SQL_FULLTEXT   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> --------------------------------------------------------------------------------------------------------------   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">  SELECT  A.LLEVEL,  A.LMODE  FROM TABLE_XXX A  WHERE A.SERVICE_NAME = :SERVICE_NAME AND STATE='Y'   </li> </ol> </div> 很明显，这是一条非常简单的SQL，没有ORDER BY ，也没有GROUP BY、UNION、DISTINCT等需要排序的，TABLE_XXX是一张普通的表，而不是视图。出现了什么问题？会不会是v$sort_usage的SQL_ID列有错误？我们查看其中一个会话正在执行的SQL： <div class="dp-highlighter" style="margin:18px 0px;padding:1px 0px 0px;font-family:Consolas, 'Courier New', Courier, mono;font-size:12px;width:653.390625px;overflow:auto;background-color:#E7E5DC;"> <div class="bar" style="margin:0px;padding:0px 0px 0px 45px;"> <div class="tools" style="margin:0px;padding:3px 8px 10px 10px;font-stretch:normal;font-size:9px;line-height:normal;font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;color:silver;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;background-color:#F8F8F8;"> <a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">view plain</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">copy to clipboard</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">print</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">?</a> </div> </div> <ol start="1" class="dp-sql" style="margin:0px 0px 1px 45px;padding:0px;list-style:decimal-leading-zero;color:#5C5C5C;background-color:#FFFFFF;"> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> select sid,prev_sql_id, sql_id from v$session where saddr='070000294AC0D050';   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">    </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">         SID PREV_SQL_ID   SQL_ID   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> ----------- ------------- -------------   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">        3163 291nk7db4bwdh   </li> </ol> </div> v$sort_usage中看到某个会话当前没有执行任何SQL，v$sort_usage中的SQL_ID是该会话前一条执行的SQL。为什么这里显示的是会话前一条执行的SQL，关于这个问题后面再详述，但至少有一点是可以判断的：如果大量的临时段都是由会话当前正在执行的SQL所产生的，那说明同时有几百个会话在执行需要大量临时空间的SQL，那系统早就崩溃了。所以这些临时表空间的占用不应该是由当前在执行的SQL所产生的，至少大部分不是。 大部分人的一个错误观点是，临时表空间中当前占用的空间是由会话当前正在执行的SQL所产生的。上面的一个简单的分析判断，情况不应该是这样。我们可以基于查询类SQL的执行过程来分析： <ol style="margin:10px 0px;padding:0px 40px;"> <li style="margin:0px;padding:2px 0px;"> 解析SQL语句(Parse)，生成一个游标(Open Cursor)。 </li> <li style="margin:0px;padding:2px 0px;"> 执行SQL语句(Execute)，严格说就是执行新产生的游标。 </li> <li style="margin:0px;padding:2px 0px;"> 在游标中取数据(Fetch)。 </li> <li style="margin:0px;padding:2px 0px;"> 关闭游标(Close Cursor)。 </li> </ol> 关键在第3步。大家都知道取数据有一个array size的概念，表示一次从游标中取多少条数据，这是一个循环的过程。如果SQL查询得到的数据有1000条，每次取100条，则需要取10次。对于Fetch Cursor，有两点： <ol style="margin:10px 0px;padding:0px 40px;"> <li style="margin:0px;padding:2px 0px;"> 一个游标，或者说一条SQL语句，并不要求客户端把所有数据取完，只取了一部分数据就关闭游标也是可以的。 </li> <li style="margin:0px;padding:2px 0px;"> 只要还没有关闭游标，数据库就要维护该游标的状态，如果是排序的SQL，也需要维持该SQL已经排好序的数据。 </li> </ol> 很显然，从上述第2点可以知道，如果一条SQL使用了临时段来排序，在SQL对应的游标没关闭的情况下，Oracle数据库不会去释放临时段，因为对于Oracle数据库来说，它不会知道客户端是否还要继续取游标的数据。 基于这样的分析，我们只需要随便选择一个占用了接近1GB的会话，查询v$open_cursor，查看其打开的游标中是否有大数据量排序的SQL： <div class="dp-highlighter" style="margin:18px 0px;padding:1px 0px 0px;font-family:Consolas, 'Courier New', Courier, mono;font-size:12px;width:653.390625px;overflow:auto;background-color:#E7E5DC;"> <div class="bar" style="margin:0px;padding:0px 0px 0px 45px;"> <div class="tools" style="margin:0px;padding:3px 8px 10px 10px;font-stretch:normal;font-size:9px;line-height:normal;font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;color:silver;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;background-color:#F8F8F8;"> <a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">view plain</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">copy to clipboard</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">print</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">?</a> </div> </div> <ol start="1" class="dp-sql" style="margin:0px 0px 1px 45px;padding:0px;list-style:decimal-leading-zero;color:#5C5C5C;background-color:#FFFFFF;"> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> SQL> select sql_id,sorts,rows_processed/executions from v$sql   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">   2  where parsing_schema_name='ACCT' and executions>0 and sorts>0   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">   3  and sql_id in (select sql_id from v$open_cursor where sid=4505)   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">   4  order by 3;   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">      </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">   SQL_ID              SORTS ROWS_PROCESSED/EXECUTIONS   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> ------------- ----------- -------------------------   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> ...省略部分输出结果...   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> 86vp997jbz7s6       63283                       593   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> cfpdpb526ad43         592               35859.79899   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> cfpdpb526ad43         188               55893.61702   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> cfpdpb526ad43         443                     71000   </li> </ol> </div> 最后三个游标，实际上都是同一条SQL语句，排序的数据量最大，我们来看看这条SQL是什么： <div class="dp-highlighter" style="margin:18px 0px;padding:1px 0px 0px;font-family:Consolas, 'Courier New', Courier, mono;font-size:12px;width:653.390625px;overflow:auto;background-color:#E7E5DC;"> <div class="bar" style="margin:0px;padding:0px 0px 0px 45px;"> <div class="tools" style="margin:0px;padding:3px 8px 10px 10px;font-stretch:normal;font-size:9px;line-height:normal;font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;color:silver;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;background-color:#F8F8F8;"> <a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">view plain</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">copy to clipboard</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">print</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">?</a> </div> </div> <ol start="1" class="dp-sql" style="margin:0px 0px 1px 45px;padding:0px;list-style:decimal-leading-zero;color:#5C5C5C;background-color:#FFFFFF;"> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> @sqlbyid cfpdpb526ad43   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">    </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> SQL_FULLTEXT   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> ---------------------------------------------------------------------------------------------------   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> select ... from  c, b, a, d, e where ... order by d.billing_cycle_id desc,e.offer_name,a.acc_name   </li> </ol> </div> 基于为客户保密的原因，SQL做了处理，能知道这条SQL的确是排了序就行，不过在SQL中看不出来的是，这条SQL没有任何实质性的能够过滤大量数据的条件。那么我们count(*)这条SQL语句看看： <div class="dp-highlighter" style="margin:18px 0px;padding:1px 0px 0px;font-family:Consolas, 'Courier New', Courier, mono;font-size:12px;width:653.390625px;overflow:auto;background-color:#E7E5DC;"> <div class="bar" style="margin:0px;padding:0px 0px 0px 45px;"> <div class="tools" style="margin:0px;padding:3px 8px 10px 10px;font-stretch:normal;font-size:9px;line-height:normal;font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;color:silver;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;background-color:#F8F8F8;"> <a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">view plain</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">copy to clipboard</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">print</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">?</a> </div> </div> <ol start="1" class="dp-sql" style="margin:0px 0px 1px 45px;padding:0px;list-style:decimal-leading-zero;color:#5C5C5C;background-color:#FFFFFF;"> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> COUNT(*)   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> --------   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> 12122698   </li> </ol> </div> 出来的结果居然有1200多万条数据，一个前台应用，不知道取1200多万条数据干嘛。但是从rows_processed/executions只有几万的结果来看，应用在取了几万条数据之后，由于某些原因（最大的可能就是不能再处理更多的数据），不再继续取数据，但是游标也一直没有关闭。 比较容易就能进行演示sort by时临时表空间的占用。 <div class="dp-highlighter" style="margin:18px 0px;padding:1px 0px 0px;font-family:Consolas, 'Courier New', Courier, mono;font-size:12px;width:653.390625px;overflow:auto;background-color:#E7E5DC;"> <div class="bar" style="margin:0px;padding:0px 0px 0px 45px;"> <div class="tools" style="margin:0px;padding:3px 8px 10px 10px;font-stretch:normal;font-size:9px;line-height:normal;font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;color:silver;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;background-color:#F8F8F8;"> <a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">view plain</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">copy to clipboard</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">print</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">?</a> </div> </div> <ol start="1" class="dp-sql" style="margin:0px 0px 1px 45px;padding:0px;list-style:decimal-leading-zero;color:#5C5C5C;background-color:#FFFFFF;"> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> 根据dba_objects建一个测试表T1，使其数据量达到2000万行。   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">  select count(*) from t1;   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">    </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">    COUNT(*)   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> -----------   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">    20171200   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">     </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> 然后将SQL工作区设置为手动模式，设置sort内存大小限制为200M：   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">  alter session set workarea_size_policy=manual;   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">  alter session set sort_area_size=209715200;   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">    </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> 查询得到当前的会话sid：   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">  select sid from v$mystat where rownum< =1;   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">    </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">         SID   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> -----------   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">        2111   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">    </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> 执行这下面的代码：   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">  declare   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">   2     v_object_name varchar2(100);   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">   3     v_dummy varchar2(100);   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">   4  begin   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">   5    for rec in (select * from t1 order by object_id,object_name) loop   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">   6       select object_type into v_dummy from t1 where rownum<=1;   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">   7       select object_name into v_object_name from dba_objects where object_id=rec.object_id;   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">   8       dbms_lock.sleep(60*10);   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">   9       exit;   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">  10    end loop;   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">  11  end;   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">  12  /   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> 这段代码会打开一个游标，对2000万的数据量进行排序，然后在循环中只取一条数据，然后就进入sleep。在另一个窗口中监控到2111这个会话的event变成了PL/SQL lock timer，就去查询v$sort_usage:   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> select a.sql_id sort_sql_id,b.sql_id,b.prev_sql_id, contents,segtype,blocks*8/1024/1024 gb    </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">   2  from v$sort_usage a,v$session b    </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">   3  where a.session_addr=b.saddr   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">   4  and b.sid=2111;   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">    </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> SORT_SQL_ID   SQL_ID        PREV_SQL_ID   CONTENTS  SEGTYPE            GB   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> ------------- ------------- ------------- --------- --------- -----------   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> fabh24prgk2sj bhzf316mdc07w fabh24prgk2sj TEMPORARY SORT      1.444824219   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> 可以看到v$sort_usage中的SQL_ID（即上述结果中SORT_SQL_ID）与v$session中的pre_sql_id一致，这条SQL是：   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">    </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> @sqlbyid fabh24prgk2sj   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> SQL_FULLTEXT   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> --------------------------------------------------------   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_ID=:B1   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">    </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> 而实际上当前正在执行的SQL是：   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">  @sqlbyid bhzf316mdc07w   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">    </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> SQL_FULLTEXT   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> ---------------------------------------------------------------------------   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> declare   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">    v_object_name varchar2(100);   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">    v_dummy varchar2(100);   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> begin   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">   for rec in (select * from t1 order by object_id,object_name) loop   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">      select object_type into v_dummy from t1 where rownum<=1;   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">      select object_name into v_object_name from dba_objects where object_id=rec.object_id;   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">      dbms_lock.sleep(60*10);   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">      exit;   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">   end loop;   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> end;   </li> </ol> </div> 问题分析到这里，很明显确认的是，应用存在问题，也许是业务逻辑问题；也许是根据前台选择的条件拼接的SQL，但是没有任何条件时就查询了所有数据。接下来就是找来开发人员，至于后面的事就跟这个主题没有太大关系。我们可以根据这个案例来进一步展开，去探寻临时表空间的更多知识点。 这里要展开的第1点是，v$sort_usage中的sql_id是不是会话正在执行的SQL，我们去看看视图fixed_View_definition就知道了： <div class="dp-highlighter" style="margin:18px 0px;padding:1px 0px 0px;font-family:Consolas, 'Courier New', Courier, mono;font-size:12px;width:653.390625px;overflow:auto;background-color:#E7E5DC;"> <div class="bar" style="margin:0px;padding:0px 0px 0px 45px;"> <div class="tools" style="margin:0px;padding:3px 8px 10px 10px;font-stretch:normal;font-size:9px;line-height:normal;font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;color:silver;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;background-color:#F8F8F8;"> <a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">view plain</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">copy to clipboard</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">print</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">?</a> </div> </div> <ol start="1" class="dp-sql" style="margin:0px 0px 1px 45px;padding:0px;list-style:decimal-leading-zero;color:#5C5C5C;background-color:#FFFFFF;"> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> select x$ktsso.inst_id, username, username, ktssoses, ktssosno, prev_sql_addr, prev_hash_value,   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> prev_sql_id, ktssotsn, decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY'), decode(ktssosegt, 1,   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED'), ktssofno,   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> ktssobno, ktssoexts, ktssoblks, ktssorfno from x$ktsso, v$session where ktssoses = v$session.saddr   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> and ktssosno = v$session.serial#   </li> </ol> </div> 原来在v$sort_usage的定义中，就明确地说明了SQL_ID列是v$session中的prev_sql_id列，而不是当前的SQL。至于为什么这样定义，老实说，现在还不知道。 不过从11.2.0.2这个版本开始，v$sort_usage的基表x$ktsso中增加了一个字段ktssosqlid，表示该临时段真正关联的SQL，以上述的测试结果为例，查询这个基表的结果如下： <div class="dp-highlighter" style="margin:18px 0px;padding:1px 0px 0px;font-family:Consolas, 'Courier New', Courier, mono;font-size:12px;width:653.390625px;overflow:auto;background-color:#E7E5DC;"> <div class="bar" style="margin:0px;padding:0px 0px 0px 45px;"> <div class="tools" style="margin:0px;padding:3px 8px 10px 10px;font-stretch:normal;font-size:9px;line-height:normal;font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;color:silver;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;background-color:#F8F8F8;"> <a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">view plain</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">copy to clipboard</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">print</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">?</a> </div> </div> <ol start="1" class="dp-sql" style="margin:0px 0px 1px 45px;padding:0px;list-style:decimal-leading-zero;color:#5C5C5C;background-color:#FFFFFF;"> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> select ktssosqlid from x$ktsso, v$session where ktssoses = v$session.saddr   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">   2  and ktssosno = v$session.serial#   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">   3  and v$session.sid=2111;   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">    </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> KTSSOSQLID   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> -------------   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> 60t6fmjsw6v8y   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">    </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> @sqlbyid 60t6fmjsw6v8y   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">    </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> SQL_FULLTEXT   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> ---------------------------------------------------------------------------   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> SELECT * FROM T1 ORDER BY OBJECT_ID,OBJECT_NAME   </li> </ol> </div> 可以看到的是我们查询到了真正产生临时段的SQL。 一直以来，v$sort_usage中的SQL_ID误导了很多人。所幸的是Oracle从11.2.0.2开始进行了弥补，MOS中有文档： <blockquote style="margin:10px 30px 10px 20px;padding:5px 10px;border-width:1px 1px 1px 2px;border-style:solid;border-color:#DDDDDD #DDDDDD #DDDDDD #91AA9D;background-color:#EEEEEE;"> 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. </blockquote> 我们改良一下v$sort_usage，使用如下的查询来代替： <div class="dp-highlighter" style="margin:18px 0px;padding:1px 0px 0px;font-family:Consolas, 'Courier New', Courier, mono;font-size:12px;width:653.390625px;overflow:auto;background-color:#E7E5DC;"> <div class="bar" style="margin:0px;padding:0px 0px 0px 45px;"> <div class="tools" style="margin:0px;padding:3px 8px 10px 10px;font-stretch:normal;font-size:9px;line-height:normal;font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;color:silver;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;background-color:#F8F8F8;"> <a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">view plain</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">copy to clipboard</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">print</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">?</a> </div> </div> <ol start="1" class="dp-sql" style="margin:0px 0px 1px 45px;padding:0px;list-style:decimal-leading-zero;color:#5C5C5C;background-color:#FFFFFF;"> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> select k.inst_id "INST_ID",   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">        ktssoses "SADDR",   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">        sid,   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">        ktssosno "SERIAL#",   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">        username "USERNAME",   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">        osuser "OSUSER",    </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">        ktssosqlid "SQL_ID",   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">        ktssotsn "TABLESPACE",   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">        decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY') "CONTENTS",   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">        --注意在12c的v$sort_usage定义中TABLESPACE和CONTENTS已经发生变化了。   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">        decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX',    </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">           5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED') "SEGTYPE",   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">        ktssofno "SEGFILE#",   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">        ktssobno "SEGBLK#",   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">        ktssoexts "EXTENTS",   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">        ktssoblks "BLOCKS",   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">        round(ktssoblks*p.value/1024/1024, 2) "SIZE_MB",   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">        ktssorfno "SEGRFNO#"   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> from x$ktsso k, v$session s,    </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">      (select value from v$parameter where name='db_block_size') p    </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> where ktssoses = s.saddr   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">   and ktssosno = s.serial#;   </li> </ol> </div> 要展开的第2点是，v$sort_usage中的SEGTYPE列的不同的值各有什么意义： <ol style="margin:10px 0px;padding:0px 40px;"> <li style="margin:0px;padding:2px 0px;"> SORT：SQL排序使用的临时段，包括order by、group by、union、distinct、窗口函数(window function)、建索引等产生的排序。 </li> <li style="margin:0px;padding:2px 0px;"> DATA：临时表(Global Temporary Table)存储数据使有的段。 </li> <li style="margin:0px;padding:2px 0px;"> INDEX：临时表上建的索引使用的段。 </li> <li style="margin:0px;padding:2px 0px;"> HASH：hash算法，如hash连接所使用的临时段。 </li> <li style="margin:0px;padding:2px 0px;"> LOB_DATA和LOB_INDEX：临时LOB使用的临时段。 </li> </ol> 根据上述的段类型，大体可以分为三类占用： <ol style="margin:10px 0px;padding:0px 40px;"> <li style="margin:0px;padding:2px 0px;"> SQL语句排序、HASH JOIN占用 </li> <li style="margin:0px;padding:2px 0px;"> 临时表占用 </li> <li style="margin:0px;padding:2px 0px;"> 临时LOB对象占用 </li> </ol> 临时表空间的异常占用，一种缓步增长的，另一种情况：一下撑满的通常是一个极大数据量的排序或极大的索引的创建。缓步增长的情况，跟系统的内存被逐渐占用类似，存在“泄露”。比如排序的SQL游标没有关闭，比如本文的案例；比如会话级临时表产生了数据后一直没有清除；临时LOB对象没有清理或泄露。前两种比较好去分析处理，但是临时LOB的泄露问题就复杂很多。 来看一个测试： <div class="dp-highlighter" style="margin:18px 0px;padding:1px 0px 0px;font-family:Consolas, 'Courier New', Courier, mono;font-size:12px;width:653.390625px;overflow:auto;background-color:#E7E5DC;"> <div class="bar" style="margin:0px;padding:0px 0px 0px 45px;"> <div class="tools" style="margin:0px;padding:3px 8px 10px 10px;font-stretch:normal;font-size:9px;line-height:normal;font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;color:silver;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;background-color:#F8F8F8;"> <a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">view plain</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">copy to clipboard</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">print</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">?</a> </div> </div> <ol start="1" class="dp-sql" style="margin:0px 0px 1px 45px;padding:0px;list-style:decimal-leading-zero;color:#5C5C5C;background-color:#FFFFFF;"> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">  select sid from v$mystat where rownum<=1;   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">    </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">         SID   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> -----------   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">        1773   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">  declare   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">   2    v_lob clob;   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">   3  begin   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">   4    dbms_lob.createtemporary(v_lob,true);   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">   5    dbms_lob.writeappend(v_lob,1000,lpad('a',1000,'a'));   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">   6  end;   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">   7  /   </li> </ol> </div> 上述的代码执行完之后，在另一个窗口中，我们查询v$sort_usage： <div class="dp-highlighter" style="margin:18px 0px;padding:1px 0px 0px;font-family:Consolas, 'Courier New', Courier, mono;font-size:12px;width:653.390625px;overflow:auto;background-color:#E7E5DC;"> <div class="bar" style="margin:0px;padding:0px 0px 0px 45px;"> <div class="tools" style="margin:0px;padding:3px 8px 10px 10px;font-stretch:normal;font-size:9px;line-height:normal;font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;color:silver;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;background-color:#F8F8F8;"> <a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">view plain</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">copy to clipboard</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">print</a><a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html#" style="margin:0px 10px 0px 0px;padding:0px;color:#A0A0A0;text-decoration:none;outline-color:invert;outline-style:none;font-size:9px;">?</a> </div> </div> <ol start="1" class="dp-sql" style="margin:0px 0px 1px 45px;padding:0px;list-style:decimal-leading-zero;color:#5C5C5C;background-color:#FFFFFF;"> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> select a.sql_id sort_sql_id,b.sql_id,b.prev_sql_id, contents,segtype,blocks*8/1024/1024 gb    </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">   2  from v$sort_usage a,v$session b    </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">   3  where a.session_addr=b.saddr   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;">   4  and b.sid=1773;   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">    </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> SORT_SQL_ID   SQL_ID        PREV_SQL_ID   CONTENTS  SEGTYPE            GB   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> ------------- ------------- ------------- --------- --------- -----------   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> 9babjv8yq8ru3               9babjv8yq8ru3 TEMPORARY LOB_DATA  .0004882813   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">    </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> @sqlbyid 9babjv8yq8ru3   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;">    </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> SQL_FULLTEXT   </li> <li class="alt" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;"> ---------------------------------------------------------------------------   </li> <li class="" style="margin:0px;padding:2px 0px 2px 10px;border-left-width:3px;border-left-style:solid;border-left-color:#6CE26C;line-height:14px;background-color:#F8F8F8;"> BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;   </li> </ol> </div> 可以看到，这个会话已经产生了类型为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临时段的泄露或临时段没有释放相关的文档。 最后，不管是什么情况导致的临时表空间被过多占用，通常重启应用能够释放掉临时段，因为会话退出后，相对应的临时段就会被释放。看来，“重启”大法在这种情况下就很有用。 --The END. <div id="crp_related" style="margin:0px;padding:0px;"> </div> <div style="margin:0px;padding:0px;clear:both;"> </div> </div> <div class="info" style="margin:0px;padding:10px 0px;color:#666666;font-family:'Lucida Grande', 'Segoe UI', 'Bitstream Vera Sans', Tahoma, Verdana, Arial, sans-serif;font-size:13px;line-height:20.7999992370605px;white-space:normal;background-color:#FFFFFF;"> Address: <a href="http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html" rel="bookmark" title="一次临时表空间大量占用问题的处理" style="margin:0px;padding:0px;color:#666666;text-decoration:none;outline-color:invert;outline-style:none;">http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html</a> </div>

ITPub博客

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

最新文章