• 一个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会出现这样的情况,有待研究。

    未完待续……

      

  • 相关阅读:
    动手做第一个Chrome插件
    Discuz NT 架构剖析之Config机制
    用游标实现查询当前服务器所有数据库所有表的SQL
    Discuz X3.2 网站快照被劫持的解决方法
    centos下MYSQL 没有ROOT用户的解决方法。
    redis命令1
    在当今快节奏的软件更迭当中,我们是否还需要进行系统的学习?
    StructureMap 代码分析之Widget 之Registry 分析 (1)
    C#面试题汇总(未完成)
    C#:解决WCF中服务引用 自动生成代码不全的问题。
  • 原文地址:https://www.cnblogs.com/xpchild/p/3695000.html
Copyright © 2020-2023  润新知