一个sql导致temp表空间爆掉

Buffer sort引发的血案

　　

今天遇到的一个问题，在线系统上，有两张表，test1大概50G，test2大概200G，需要查询出来test1表中部分记录，并且这些记录不存在test2表中。于是就写了一个sql：

select t1.*
  from test1 t1, test2 t2
 where t1.col1 = t2.col1(+)
   and t1.col2 = t2.col2(+)
   and t1.col3 = t2.col3(+)
   and t2.col1 is null;

因为是在线系统，考虑对系统的影响，这个就放到备库中去执行，而test1和test2这两张表又不在一个物理db上，所以就写了下面的sql：

select /*+ ordered  use_hash(t,f)  parallel(t 4)  parallel(f 4) */ t1.*
  from test1 t1, test2@remote_stb t2
 where t1.col1 = t2.col1(+)
   and t1.col2 = t2.col2(+)
   and t1.col3 = t2.col3(+)
   and t2.col1 is null;

分别打开两个物理备库，进行查询，结果出现了ORA-1652: unable to extend temp segment by 128 in tablespace TEMP。

分析原因：

考虑到使用hash join，先要构造test1的哈希表，test1表有50G，所以就在standby上增大了临时表空间，想着一定没有问题了，天不遂人愿，同样的错误又出现了，崩溃。于是就查看执行计划：

　　

SQLPLAN                                                 
--------------------------------------------------------
  0     SELECT STATEMENT Optimizer=ALL_ROWS             
  1   0   SORT (AGGREGATE)                              
  2   1     PX COORDINATOR
  3   2       PX SEND (QC (RANDOM)) OF :TQ10002         
  4   3         SORT (AGGREGATE)                        
  5   4           FILTER
  6   5             HASH JOIN (OUTER)                   
  7   6               PX RECEIVE                        
  8   7                 PX SEND (HASH) OF :TQ10001      
  9   8                   PX BLOCK (ITERATOR)           
 10   9                     TABLE ACCESS (FULL) OF test1
 11   6               BUFFER (SORT)
 12  11                 PX RECEIVE                      
 13  12                   PX SEND (HASH) OF :TQ10000    
 14  13                     REMOTE                      

　　临时表空间应该不是问题，所以推断应该是执行计划出来问题，查看执行计划，出现sort的地方就只有SORT (AGGREGATE)和BUFFER (sort)，由于是count的计数，所以SORT (AGGREGATE)不会使用到排序空间，那只有BUFFER(sort)。于是上网上搜索，发现Lewis的一篇介绍BUFFER（sort）的博客：

　　

http://jonathanlewis.wordpress.com/2006/12/17/buffer-sorts/

http://jonathanlewis.wordpress.com/2006/12/13/cartesian-merge-join/

里边有介绍buffer(sort)的内容。

但博客里没有找到灵感，后面再仔细的看一下，翻译下。不过最后问题的解决还是同事的提醒，在并发的时候，hash join会对探测表进行buffer sort，去掉并发的hint，sql最终跑了出来。究竟为什么并发的hash join会出现这样的情况，有待研究。

未完待续……