临时表错误收集了统计信息导致TEMP暴满
星期二, 十月 28th, 2008今天，收到客户的请求说，一个关于风控用于财务的汇总完成不了，在运行过程中，一段时间后，就暴临时表空间不足，然后还暴了死锁的错误。
登陆到系统上后，看了下alert.log，从17号以后一直有这个报错，然后看了下临时表空间有130G，按理来说130G的临时表空间已经够大了，想着重新建个临时表空间看下，是不是回收没完成的原因，结果还是不行。后来跟踪SQL，发现在执行一下SQL时卡住
insert into zzye(dwbm, kmbm, kjnd, kjqj, bzbm, qcye1, qcye2, qcye3, jfse1, jfse2, jfse3, dfse1, dfse2, dfse3, qcquantity, jquantity, dquantity)
select rtrim(b.pk_corp) as dwbm, rtrim(k.subjcode) as kmbm, rtrim(b.year) as kjnd, rtrim(b.period) as kjqj,
(case c.bzbf when ‘RMB’ then ‘1′ when ‘USD’ then ‘2′ when ‘HKD’ then ‘3′ else ‘ ‘ end) as bzbm,
(case k.balanorient when 1 then 1 else -1 end)*(sum(t.debitamount)-sum(t.creditamount)) as qcye1,
(case k.balanorient when 1 then 1 else -1 end)*(sum(t.fracdebitamount)-sum(t.fraccreditamount)) as qcye2,
(case k.balanorient when 1 then 1 else -1 end)*(sum(t.localdebitamount)-sum(t.localcreditamount)) as qcye3,
sum(b.debitamount) as jfse1, sum(b.fracdebitamount) as jfse2, sum(b.localdebitamount) as jfse3,
sum(b.creditamount) as dfse1, sum(b.fraccreditamount) as dfse2, sum(b.localcreditamount) as dfse3,
0 as qcquantity, 0 as jquantity, 0 as dquantity
from t_tmpbalance_tmp b, t_tmpye_tmp t, nc23_accsubj k, zzbz c
where oc_date >= v_begin_yearmonth*100
and ((v_hisflag <> 1) or (oc_date <= v_end_yearmonth*100+31))
and trim(b.pk_corp) = trim(t.pk_corp)
and b.year = t.year
and b.period = t.period
and trim(b.pk_accsubj) = trim(t.pk_accsubj)
and trim(b.pk_currtype) = trim(t.pk_currtype)
and trim(c.dwbm) = rtrim(b.pk_corp)
and trim(c.bzmc) = trim(b.pk_currtype)
and c.bzbf in (’RMB’, ‘USD’, ‘HKD’)
and rtrim(b.pk_corp) = rtrim(k.pk_corp)
and rtrim(b.pk_accsubj) = rtrim(k.pk_accsubj)
group by b.pk_corp, k.subjcode, b.year, b.period, c.bzbf, k.balanorient;
棉花在此时提示了下，不是临时表的统计信息收集错了，去数据库查了下，确认在17号有人收集了统计信息，num_rows都是0，这个是导致临时表空间爆满的原因，找到问题了。解决很简单，删除统计信息就OK了，2分钟数据汇总就完成了
