环境为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左右；
有如下package的Procedure(代码附后)，过程内部为三个merge into 语句，现将三个语句复制出来依次执行（在同一个事物中），三段执行都很快，总时间不超过0.5秒，但是如果调用这个过程，则第一个merge花费1秒不到，而后两个merge花费2分钟以上；将三个merge删除，使得该过程中仅余一个merge执行，发现仅余第一个merge是花费1秒不到，但是如果是后两个的任一个的话则花费2分钟左右；
不知道这个问题改如何处理，从哪方面着手，请各位达人指点。注：实际执行环境中join操作的各个表中约有1w条左右的数据，user_、ip都是主键，channel、day_与ip或者user_共同组成主键；解释计划的耗费均在70左右；
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
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'));
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;
你单独执行才0.5秒，但是merge 3次的话，就是0.5秒的几何倍数增长啦。如果是在同一个ip库上的话，应该不会花费这么长的时间的。1，问下，你的单个merge之间的select语句的基表都是在一个数据库上吗？2，建议改下业务规则，为什么要用merge呢，可以有很多方法替代merge的功能啊！
1：我这是一个数据汇总的程序，这部分是汇总一个小时内的用户ip数什么的的，是在同一个数据库中的；2：我原来是用update的相关查询，但是速度完全受不了，解释计划cost在200以上，同样的环境执行一次得要2分钟；
我是java程序员，原来没有搞过oracle，都是这几天突击自学的，对有些东西可能都不是太了解；能不能麻烦你解释下你说的merge的替代物是什么？性能如何？
不知道你是否在线，可以联系我的MSN：[email protected]
正如你所说，merge替代update的效果非常好，因为它只需要扫描一次全表就完成了操作。
但是需要确定的是，如果多次merge，而表中数据分布不合理的话，那么需要的时间是呈几何级数上升的。检查一下group by