请教这个package执行巨慢的原因

[qjyh]

环境为solaris上的oracle10gr2。
有如下package的Procedure(代码附后)，过程内部为三个merge into 语句，现将三个语句复制出来依次执行（在同一个事物中），三段执行都很快，总时间不超过0.5秒，但是如果调用这个过程，则第一个merge花费1秒不到，而后两个merge花费2分钟以上；将三个merge删除，使得该过程中仅余一个merge执行，发现仅余第一个merge是花费1秒不到，但是如果是后两个的任一个的话则花费2分钟左右；
不知道这个问题改如何处理，从哪方面着手，请各位达人指点。

注：实际执行环境中join操作的各个表中约有1w条左右的数据，user_、ip都是主键，channel、day_与ip或者user_共同组成主键；解释计划的耗费均在70左右；

[qjyh]

Procedure Chl_City_v_Day(Day_Seq In Channelcityvisitdaytab.Day_%Type) As
Begin
-- pv
dbms_output.put_line('1: ' || to_char(sysdate, 'yyyy-MM-dd HH24:mi:ss'));
Merge into Channelcityvisitdaytab c1
using (select nvl(sum(case
when tmp1.play = 0 and tmp1.charge = 0 then
tmp1.count_
else
0
end),
0) as Fee_Live_Pv,
nvl(sum(case
when tmp1.play = 1 and tmp1.charge = 0 then
tmp1.count_
else
0
end),
0) as Fee_Vod_Pv,
nvl(sum(case
when tmp1.play = 0 and tmp1.charge = 1 then
tmp1.count_
else
0
end),
0) as Free_Live_Pv,
nvl(sum(case
when tmp1.play = 1 and tmp1.charge = 1 then
tmp1.count_
else
0
end),
0) as Free_Vod_Pv,
tmp1.channel,
tmp1.province,
tmp1.city
from (Select U1.Count_,
U1.Channel,
A1.Province,
A1.City,
n1.play,
n1.charge
From Userchannelvisitdaytab U1
Inner Join Channelinfotab N1 On N1.Channel =
U1.Channel
And N1.Day_ = Day_Seq
Inner Join Userareatab A1 On A1.User_ = U1.User_
Where U1.Day_ = Day_Seq
Union All
Select U2.Count_,
U2.Channel,
A2.Province,
A2.City,
n2.play,
n2.charge
From Usernodevisitdaytab U2
Inner Join Nodeinfotab N2 On N2.Channel = U2.Channel
And N2.Node = U2.Node
And N2.Day_ = Day_Seq
Inner Join Userareatab A2 On A2.User_ = U2.User_
Where U2.Day_ = Day_Seq
Union All
Select U3.Count_,
U3.Channel,
A3.Province,
A3.City,
n3.play,
n3.charge
From Usermovievisitdaytab U3
Inner Join Movieinfotab N3 On N3.Channel = U3.Channel
And N3.Node = U3.Node
And N3.Movie = U3.Movie
And N3.Day_ = Day_Seq
Inner Join Userareatab A3 On A3.User_ = U3.User_
Where U3.Day_ = Day_Seq) tmp1
group by tmp1.channel, tmp1.province, tmp1.city) s1
on (c1.channel = s1.channel and c1.province = s1.province and c1.city = s1.city and c1.day_ = Day_Seq)
when matched then
update
set c1.pv = (s1.Fee_Live_Pv + s1.Fee_Vod_Pv +
s1.Free_Live_Pv + s1.Free_Vod_Pv),
c1.fee_live_pv = s1.Fee_Live_Pv,
c1.Fee_Vod_Pv = s1.Fee_Vod_Pv,
c1.Free_Live_Pv = s1.Free_Live_Pv,
c1.Free_Vod_Pv = s1.Free_Vod_Pv
when not matched then
insert
(Channel,
Day_,
Province,
City,
Pv,
Uv,
Ip,
Visits,
Fee_Live_Pv,
Fee_Vod_Pv,
Free_Live_Pv,
Free_Vod_Pv,
Fee_Uv,
Fee_Live_Uv,
Fee_Vod_Uv,
Free_Uv,
Free_Live_Uv,
Free_Vod_Uv,
Live_Uv,
Vod_Uv,
Fee_Ip,
Fee_Live_Ip,
Fee_Vod_Ip,
Free_Ip,
Free_Live_Ip,
Free_Vod_Ip,
Live_Ip,
Vod_Ip,
Fee_Visits,
Fee_Live_Visits,
Fee_Vod_Visits,
Free_Visits,
Free_Live_Visits,
Free_Vod_Visits,
Live_Visits,
Vod_Visits)
values
(s1.channel,
Day_Seq,
s1.province,
s1.city,
(s1.Fee_Live_Pv + s1.Fee_Vod_Pv + s1.Free_Live_Pv +
s1.Free_Vod_Pv),
0,
0,
0,
s1.Fee_Live_Pv,
s1.Fee_Vod_Pv,
s1.Free_Live_Pv,
s1.Free_Vod_Pv,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0);
-- uv

[qjyh]

dbms_output.put_line('2: ' || to_char(sysdate, 'yyyy-MM-dd HH24:mi:ss'));
Merge into Channelcityvisitdaytab c1
using (select count(distinct(case
when tmp1.play = 0 and tmp1.charge = 0 then
tmp1.user_
else
'-'
end)) as Fee_Live_Uv,
count(distinct(case
when tmp1.play = 1 and tmp1.charge = 0 then
tmp1.user_
else
'-'
end)) as Fee_Vod_Uv,
count(distinct(case
when tmp1.play = 0 and tmp1.charge = 1 then
tmp1.user_
else
'-'
end)) as Free_Live_Uv,
count(distinct(case
when tmp1.play = 1 and tmp1.charge = 1 then
tmp1.user_
else
'-'
end)) as Free_Vod_Uv,
count(distinct(case
when tmp1.play = 0 then
tmp1.user_
else
'-'
end)) as Fee_Uv,
count(distinct(case
when tmp1.play = 1 then
tmp1.user_
else
'-'
end)) as Free_Uv,
count(distinct(case
when tmp1.charge = 0 then
tmp1.user_
else
'-'
end)) as Live_Uv,
count(distinct(case
when tmp1.charge = 1 then
tmp1.user_
else
'-'
end)) as Vod_Uv,
count(distinct tmp1.user_) as Uv,
tmp1.channel,
tmp1.province,
tmp1.city
from (Select U1.User_,
U1.Channel,
A1.Province,
A1.City,
n1.play,
n1.charge
From Userchannelvisitdaytab U1
Inner Join Channelinfotab N1 On N1.Channel =
U1.Channel
And N1.Day_ = Day_Seq
Inner Join Userareatab A1 On A1.User_ = U1.User_
Where U1.Day_ = Day_Seq
Union all
Select U2.User_,
U2.Channel,
A2.Province,
A2.City,
n2.play,
n2.charge
From Usernodevisitdaytab U2
Inner Join Nodeinfotab N2 On N2.Channel = U2.Channel
And N2.Node = U2.Node
And N2.Day_ = Day_Seq
Inner Join Userareatab A2 On A2.User_ = U2.User_
Where U2.Day_ = Day_Seq
Union all
Select U3.User_,
U3.Channel,
A3.Province,
A3.City,
n3.play,
n3.charge
From Usermovievisitdaytab U3
Inner Join Movieinfotab N3 On N3.Channel = U3.Channel
And N3.Node = U3.Node
And N3.Movie = U3.Movie
And N3.Day_ = Day_Seq
Inner Join Userareatab A3 On A3.User_ = U3.User_
Where U3.Day_ = Day_Seq) tmp1
group by tmp1.channel, tmp1.province, tmp1.city) s1
on (c1.channel = s1.channel and c1.province = s1.province and c1.city = s1.city and c1.day_ = Day_Seq)
when matched then
update
set c1.Uv = s1.Uv,
c1.Fee_Live_Uv = s1.Fee_Live_Uv,
c1.Fee_Vod_Uv = s1.Fee_Vod_Uv,
c1.Free_Live_Uv = s1.Free_Live_Uv,
c1.Free_Vod_Uv = s1.Free_Vod_Uv,
c1.Fee_Uv = s1.Fee_Uv,
c1.Free_Uv = s1.Free_Uv,
c1.Live_Uv = s1.Live_Uv,
c1.Vod_Uv = s1.Vod_Uv
when not matched then
insert
(Channel,
Day_,
Province,
City,
Pv,
Uv,
Ip,
Visits,
Fee_Live_Pv,
Fee_Vod_Pv,
Free_Live_Pv,
Free_Vod_Pv,
Fee_Uv,
Fee_Live_Uv,
Fee_Vod_Uv,
Free_Uv,
Free_Live_Uv,
Free_Vod_Uv,
Live_Uv,
Vod_Uv,
Fee_Ip,
Fee_Live_Ip,
Fee_Vod_Ip,
Free_Ip,
Free_Live_Ip,
Free_Vod_Ip,
Live_Ip,
Vod_Ip,
Fee_Visits,
Fee_Live_Visits,
Fee_Vod_Visits,
Free_Visits,
Free_Live_Visits,
Free_Vod_Visits,
Live_Visits,
Vod_Visits)
values
(s1.channel,
Day_Seq,
s1.province,
s1.city,
0,
s1.uv,
0,
0,
0,
0,
0,
0,
s1.Fee_Uv,
s1.Fee_Live_Uv,
s1.Fee_Vod_Uv,
s1.Free_Uv,
s1.Free_Live_Uv,
s1.Free_Vod_Uv,
s1.Live_Uv,
s1.Vod_Uv,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0);
-- ip
dbms_output.put_line('3: ' || to_char(sysdate, 'yyyy-MM-dd HH24:mi:ss'));

[qjyh]

merge into Channelcityvisitdaytab c1
using (select count(distinct(case
when tmp1.play = 0 and tmp1.charge = 0 then
tmp1.ip
else
-1
end)) as Fee_Live_Ip,
count(distinct(case
when tmp1.play = 1 and tmp1.charge = 0 then
tmp1.ip
else
-1
end)) as Fee_Vod_Ip,
count(distinct(case
when tmp1.play = 0 and tmp1.charge = 1 then
tmp1.ip
else
-1
end)) as Free_Live_Ip,
count(distinct(case
when tmp1.play = 1 and tmp1.charge = 1 then
tmp1.ip
else
-1
end)) as Free_Vod_Ip,
count(distinct(case
when tmp1.charge = 0 then
tmp1.ip
else
-1
end)) as Fee_Ip,
count(distinct(case
when tmp1.charge = 1 then
tmp1.ip
else
-1
end)) as Free_Ip,
count(distinct(case
when tmp1.play = 0 then
tmp1.ip
else
-1
end)) as Live_Ip,
count(distinct(case
when tmp1.play = 1 then
tmp1.ip
else
-1
end)) as Vod_Ip,
count(distinct tmp1.ip) as Ip,
tmp1.channel,
nvl(tmp1.province, '其他') as province,
nvl(tmp1.city, '-') as city
from (Select P1.Ip,
P1.Channel,
A1.Province,
A1.City,
n1.play,
n1.charge
From Ipchannelvisitdaytab P1
Inner Join Channelinfotab N1 On N1.Channel =
P1.Channel
And N1.Day_ = Day_Seq
inner Join IpAreaTab A1 On P1.Ip = A1.ip
Where P1.Day_ = Day_Seq
Union all
Select P2.Ip,
P2.Channel,
A2.Province,
A2.City,
n2.play,
n2.charge
From Ipnodevisitdaytab P2
Inner Join Nodeinfotab N2 On N2.Channel = P2.Channel
And N2.Node = P2.Node
And N2.Day_ = Day_Seq
inner Join IpAreaTab A2 On P2.Ip = A2.ip
Where P2.Day_ = Day_Seq
Union all
Select P3.Ip,
P3.Channel,
A3.Province,
A3.City,
n3.play,
n3.charge
From Ipmovievisitdaytab P3
Inner Join Movieinfotab N3 On N3.Channel = P3.Channel
And N3.Node = P3.Node
And N3.Movie = P3.Movie
And N3.Day_ = Day_Seq
inner Join IpAreaTab A3 On P3.Ip = A3.ip
Where P3.Day_ = Day_Seq) tmp1
group by tmp1.channel, tmp1.province, tmp1.city) s1
on (c1.channel = s1.channel and c1.province = s1.province and c1.city = s1.city and c1.day_ = Day_Seq)
when matched then
update
set c1.ip = s1.ip,
c1.Fee_Live_Ip = s1.Fee_Live_Ip,
c1.Fee_Vod_Ip = s1.Fee_Vod_Ip,
c1.Free_Live_Ip = s1.Free_Live_Ip,
c1.Free_Vod_Ip = s1.Free_Vod_Ip,
c1.Fee_Ip = s1.Fee_Ip,
c1.Free_Ip = s1.Free_Ip,
c1.Live_Ip = s1.Live_Ip,
c1.Vod_Ip = s1.Vod_Ip
when not matched then
insert
(Channel,
Day_,
Province,
City,
Pv,
Uv,
Ip,
Visits,
Fee_Live_Pv,
Fee_Vod_Pv,
Free_Live_Pv,
Free_Vod_Pv,
Fee_Uv,
Fee_Live_Uv,
Fee_Vod_Uv,
Free_Uv,
Free_Live_Uv,
Free_Vod_Uv,
Live_Uv,
Vod_Uv,
Fee_Ip,
Fee_Live_Ip,
Fee_Vod_Ip,
Free_Ip,
Free_Live_Ip,
Free_Vod_Ip,
Live_Ip,
Vod_Ip,
Fee_Visits,
Fee_Live_Visits,
Fee_Vod_Visits,
Free_Visits,
Free_Live_Visits,
Free_Vod_Visits,
Live_Visits,
Vod_Visits)
values
(s1.channel,
Day_Seq,
s1.province,
s1.city,
0,
0,
s1.ip,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
s1.Fee_Ip,
s1.Fee_Live_Ip,
s1.Fee_Vod_Ip,
s1.Free_Ip,
s1.Free_Live_Ip,
s1.Free_Vod_Ip,
s1.Live_Ip,
s1.Vod_Ip,
0,
0,
0,
0,
0,
0,
0,
0);
dbms_output.put_line('4: ' || to_char(sysdate, 'yyyy-MM-dd HH24:mi:ss'));
End;

[oraclelogan]

[Quote=引用 5 楼 qjyh 的回复:]
引用 4 楼 hongqi162 的回复:
还是merge语句的问题，注意里面的group by什么的，索引什么的

但是拿出来执行很快啊
[/Quote]

你单独执行才0.5秒，但是merge 3次的话，就是0.5秒的几何倍数增长啦。

如果是在同一个ip库上的话，应该不会花费这么长的时间的。

1，问下，你的单个merge之间的select语句的基表都是在一个数据库上吗？

2，建议改下业务规则，为什么要用merge呢，可以有很多方法替代merge的功能啊！

[hongqi162]

还是merge语句的问题，注意里面的group by什么的，索引什么的

[qjyh]

[Quote=引用 4 楼 hongqi162 的回复:]
还是merge语句的问题，注意里面的group by什么的，索引什么的
[/Quote]
但是拿出来执行很快啊

[qjyh]

我说的复制出来执行花0.5秒是这么操作的：在同一个事务内，先执行第一个，花费0.2秒不到，执行后不提交，接着执行第二个，又花费0.2秒不到，然后再执行第三个，又花费0.2秒不到，最后提交事务，一切ok没结果与我执行这个过程等待5分钟执行完后的结果一样。

[qjyh]

[Quote=引用 6 楼 oraclelogan 的回复:]

1，问下，你的单个merge之间的select语句的基表都是在一个数据库上吗？

2，建议改下业务规则，为什么要用merge呢，可以有很多方法替代mer…
[/Quote]

1：我这是一个数据汇总的程序，这部分是汇总一个小时内的用户ip数什么的的，是在同一个数据库中的；

2：我原来是用update的相关查询，但是速度完全受不了，解释计划cost在200以上，同样的环境执行一次得要2分钟；
我是java程序员，原来没有搞过oracle，都是这几天突击自学的，对有些东西可能都不是太了解；能不能麻烦你解释下你说的merge的替代物是什么？性能如何？

[hotyxm]

[Quote=引用 8 楼 qjyh 的回复:]
引用 6 楼 oraclelogan 的回复:

1，问下，你的单个merge之间的select语句的基表都是在一个数据库上吗？

2，建议改下业务规则，为什么要用merge呢，可以有很多方法替代mer…


1：我这是一个数据汇总的程序，这部分是汇总一个小时内的用户ip数什么的的，是在同一个数据库中的；

2：我原来是用update的相关查询，但是速度完全受不了，解释计划cost在200以上，同样的环境执行一次得要2分钟；
我是java程序员，原来…
[/Quote]

正如你所说，merge替代update的效果非常好，因为它只需要扫描一次全表就完成了操作。
但是需要确定的是，如果多次merge，而表中数据分布不合理的话，那么需要的时间是呈几何级数上升的。

检查一下group by

[sancuo]

问题解决了没？建议从头来过。
不知道你是否在线，可以联系我的MSN：[e-mail]