提数系统中的一个查询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有什么可以优化的地方吗？
谢谢各位！

[睿智天成]

既然问题出在临时表空间，那就查看一下临时表空间信息： 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

[iqlife]

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

[波鲁克]

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

[波鲁克]

临时表空间突然增大，肯定是某种或多种问题的结果，所以在扩大表空间无效的情况下，我尝试看了下磁盘空间是否足够，另外，我在网上还看到这段话： 查询的时候连表查询中使用的表过多造成的。我们知道在连表查询的时候，根据查询的字段和表的个数会生成一个迪斯卡尔积，这个迪斯卡尔积的大小就是一次查询需要的临时空间的大小，如果查询的字段过多和数据过大，那么就会消耗非常大的临时表空间。 于是试着删除了其中3张表中2012年1月1日之前的数据，每张表的数据删完后就只有1千来条了，重新试着跑系统，就没有发现这种问题。但是，我删的是测试库的数据，可以用这种办法，如果是生产库，发生这种问题，那这种问题肯定行不通了。咱就不纠结了，留给老大去纠结~

[波鲁克]

感谢各位的回答，谢谢