提数系统中的一个查询sql执行很久很久

Question

昨天在跑一个提数系统，后台跑到一个查询sql时就不动了，卡了很久，看了下被锁住的sql，sql如下：select comcode,'RMB',sum(sumclaim) FROM((SELECT c.circcomcode AS ComCode,'RMB',SUM(ck.SumClaim * decode(ck.Currency,'RMB',1,decode(b.ExchRate,null,1,b.ExchRate))*decode(p.CoinsRate,null,100,p.CoinsRate)/100) AS SumClaim FROM  prpLclaim a,PrpDExch b,statcomcodemapping c ,prpLclaimLoss ck ,prpcitemcar cc,PrpCcoins p WHERE (a.EndCaseDate IS NULL OR to_date(a.EndCaseDate,'yyyy-mm-dd') > date'2013-01-31')  AND a.ClaimDate <= date'2013-01-31' AND (a.canceldate is null OR a.canceldate > date'2013-01-31') AND substr(a.InputDate,1,10) <= to_date('2013-01-31','yyyy-mm-dd') AND a.PolicyNo = p.PolicyNo(+)  AND (p.CoinsType = '1' OR p.CoinsType IS NULL)  AND b.ExchDate =     (SELECT max(ExchDate) FROM PrpDExch g      WHERE g.ExchDate<=ck.InputDate      AND g.ExchCurrency='RMB' AND ck.Currency=g.BaseCurrency(+))  AND b.BaseCurrency = ck.Currency  AND b.ExchCurrency = 'RMB'  AND a.PolicyNo = cc.PolicyNo  AND a.claimno = ck.claimno AND a.ClassCode IN  ('05','06')  AND a.ComCode = c.cciccomcode GROUP BY c.circcomcode )) GROUP BY comcode不明白为什么会这样，因为这样的sql在系统中比比皆是，为什么到这句就卡住了，之前跑系统时从来没遇到过这种情况，换了两个数据库跑系统，都会报ORA-01652: 无法通过 128 (在表空间 TEMP 中) 扩展 temp 段这样的错，的确，在执行此sql之前，临时表空间不会有什么变化，但是到此sql，表空间突然会一直变大，直到最大空间限额,可以从2g到31g多。此sql在PL/SQL中单独执行需要耗时8.6秒左右。今天重新换了个数据库跑系统，还是出现同样的问题。网上说大量频繁的排序等操作会致使临时表空间突然增大，那么为什么在执行此sql前的N多排序sql不会出现此情况，另外，此问题该如何解决才好？我也把表空间清空并扩大了TEMP表空间，TEMP表空间是自增的,问题照样发生。这个sql有什么可以优化的地方吗？
谢谢各位！sql

Answer 1

继续补充下情况，sql中的有几张表数据量比较大：如下
SELECT COUNT(*) FROM prpLclaim ;--2467
SELECT COUNT(*) FROM PrpDExch  ;--3828
SELECT COUNT(*) FROM prpLclaimLoss  ;--5658
SELECT COUNT(*) FROM prpcitemcar;--4683
另外，数据库是oracle10的，，木有人回帖，自己顶

Answer 2

既然问题出在临时表空间，那就查看一下临时表空间信息：
   select * from dba_tablespaces;
   select * from dba_temp_files;
   select * from v$tempfile
然后根据再将temp数据文件自动扩展。先不管这个sql语句是否优化，做一个执行计划和统计分析，根据分析结果进行临时表的扩展。

Answer 3

select comcode, 'RMB', sum(sumclaim)
  FROM ((SELECT c.circcomcode AS ComCode,
                'RMB',
                SUM(ck.SumClaim *
                    decode(ck.Currency,
                           'RMB',
                           1,
                           decode(b.ExchRate, null, 1, b.ExchRate)) *
                    decode(p.CoinsRate, null, 100, p.CoinsRate) / 100) AS SumClaim
           FROM prpLclaim          a,
                PrpDExch           b,
                statcomcodemapping c,
                prpLclaimLoss      ck,
                prpcitemcar        cc,
                PrpCcoins          p
          WHERE (a.EndCaseDate IS NULL OR
                to_date(a.EndCaseDate, 'yyyy-mm-dd') > date '2013-01-31')
            AND a.ClaimDate <= date
          '2013-01-31'
            AND (a.canceldate is null OR a.canceldate > date '2013-01-31')
            --AND substr(a.InputDate, 1, 10) <=
            and truncate(a.InputDate)<=
                to_date('2013-01-31', 'yyyy-mm-dd')
            AND a.PolicyNo = p.PolicyNo(+)
            AND (p.CoinsType = '1' OR p.CoinsType IS NULL)
            AND b.ExchDate =
                (SELECT max(ExchDate)
                   FROM PrpDExch g
                  WHERE g.ExchDate <= ck.InputDate
                    AND g.ExchCurrency = 'RMB'
                    AND ck.Currency = g.BaseCurrency(+))
            AND b.BaseCurrency = ck.Currency
            AND b.ExchCurrency = 'RMB'
            AND a.PolicyNo = cc.PolicyNo
            AND a.claimno = ck.claimno
            AND a.ClassCode IN ('05', '06')
            AND a.ComCode = c.cciccomcode
          GROUP BY c.circcomcode))
 GROUP BY comcode

Answer 4

ORA-01652: 无法通过 128 (在表空间 TEMP 中) 扩展 temp看看临时表空间的状态，是不是太小造成的还有你这个语句是不是有大量排序》?看看执行计划