今天早上到公司,发现一个JOB跑了几个小时还没停下来,测试的时候这个任务执行两分钟就结束了。于是找DBA帮我查原因,原代码大致如此:
select g2.col1, g1.col2
from (select nvl(tt.col1,pp.col1) col1, nvl(tt.col2,0) + nvl(pp.col2,0) col2
from (select u.col1, count(*) as col2
from a g
inner join b u on g.username = u.username
where ...
group by u.col1) tt
full join
(select col1, count(*) as col2
from c t1, d t2
where ...
group by t2.col1) pp
on tt.col1 = pp.col1) g1,
b g2
where ...;
经过分解执行,发现没有问题,只要整体执行就特别慢。查看执行计划,发现问题出现在full join上, tt的结果比较多,pp的结果相当少。DBA建议改用unoin all,于是改为:
select g2.col1, g1.col2
from (select col1, sum(col2)
from (select u.col1, count(*) as col2
from a g
inner join b u on g.username = u.username
where ...
group by u.col1
union all
select col1, count(*) as col2
from c t1, d t2
where ...
group by t2.col1)
group by username) g1,
b g2
where ...;
重新执行任务,OK!
20110825
最近又遇到oracle的一个BUG,在存储过程中执行cube函数,产生600错误:
-- FOR <ORA-00600: 内部错误代码, 参数: [qctcte1], [0], [], [], [], [], [], []>
execute immediate 'alter session set "_optimizer_cost_based_transformation" = off';
同时,今天DBA帮我解决了一个问题,还是full join引起的,这个SQL的执行计划cost值大的可怕, 执行两个小时进度还只是百分之零点几.
DBA拿出了杀手锏:
alter session set "_complex_view_merging" = false;
问题搞定!(当然,实际上可以有别的办法绕过去,那就是不使用full join也能解决问题)