提数系统中的一个查询sql执行很久很久
昨天在跑一个提数系统，后台跑到一个查询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
[解决办法]
既然问题出在临时表空间，那就查看一下临时表空间信息：
select * from dba_tablespaces;
select * from dba_temp_files;
select * from v$tempfile
然后根据再将temp数据文件自动扩展。先不管这个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) <=
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
[解决办法]
ORA-01652: 无法通过 128 (在表空间 TEMP 中) 扩展 temp
看看临时表空间的状态，是不是太小造成的
还有你这个语句是不是有大量排序》?看看执行计划
