bfc99 发表于 2014-12-26 15:26
这种时间差(首次执行要几十秒,再次执行只要零点几秒)我认为不是软硬解析的差异所致,有可能是存储过程 ...
代码如下:
CREATE OR REPLACE PROCEDURE "SPR_PM_AC_TEMP_MIN"(p_Begintime DATE,
p_Endtime DATE,
p_Redo NUMBER) AS
-- 定义变量
v_Proc_Name VARCHAR2(32) := 'SPR_PM_AC_TEMP_MIN'; -- 存储过程名称
v_Timegranu NUMBER := (p_Endtime - p_Begintime) * 24 * 3600; -- 数据统计粒度,单位:秒
BEGIN
--数据统计
--1、统计性能数据
BEGIN
-- 删除旧数据
DELETE FROM Temp_Pm_Ac_Min WHERE Begintime = p_Begintime;
-- 写入新数据
INSERT INTO Temp_Pm_Ac_Min
(Moi,
Begintime,
Endtime,
City_Id,
City_Name,
Ac_Name,
Ac_Ip,
Ac_Mac,
Vendor,
--系统性能
Cpuavgusage, -- CPU平均利用率
Cpurtusage, -- CPU实时利用率
Memavgusage, -- 内存平均利用率
Memrtusage, -- 内存实时利用率
Onlineusers, -- 在线用户数
--认证
Authrequestnum, -- 认证请求数
Authsuccnum, -- 认证成功数
Authfailnum, -- 认证请求失败次数,2011-11-15增加,朱必冬
Authsuccrate, -- 认证成功率
Userlostconnectioncnt, -- 用户异常掉线的总次数
Userlostconnection_Rate, --在线用户异常掉线率
Inacroamingsucccnt, -- 同控制域内终端漫游成功的次数
Outacroaminginsucccnt, -- 跨控制域终端漫游转入成功的次数
Outacroamingoutsucccnt, -- 跨控制域终端漫游转出成功的次数
Portalauthreqcount, --AC收到Portal服务器的鉴权请求数
Portalchallengereqcount, --AC收到Portal服务器的Challenge请求数
Portalauthrespcount, --AC响应Portal服务器鉴权请求的次数
Portalchallengerespcount, --AC响应Portal服务器Challenge请求的次数
Radiusauthrespcount, --AC收到Radius的认证请求响应次数
Radiusauthresp_Rate, --认证网络成功率
Apconnectcount, -- AC当前连接的AP数量,2011-11-15增加,朱必冬
Sysbackupcnt) -- AC主备切换次数,2011-08-03增加,董岳
--缓存数据
WITH Basek_Ac AS
(SELECT * FROM Tpa_Basek_Wnc WHERE TIMESTAMP = p_Begintime)
SELECT b.Moi,
p_Begintime,
p_Endtime,
b.City_Id,
b.City_Name,
b.Ac_Name,
b.Ac_Ip,
b.Ac_Mac,
b.Vendor,
--系统性能
a.Cpuavgusage, -- CPU平均利用率
a.Cpurtusage, -- CPU实时利用率
a.Memavgusage, -- 内存平均利用率
a.Memrtusage, -- 内存实时利用率
a.Onlineusers, -- 在线用户数
--认证
a.Authrequestnum, -- 认证请求数
a.Authsuccnum, -- 认证成功数
a.Authfailnum, -- 认证请求失败次数,2011-11-15增加,朱必冬
a.Authsuccrate, -- 认证成功率
a.Userlostconnectioncnt, -- 用户异常掉线的总次数
a.Userlostconnection_Rate, --在线用户异常掉线率
a.Inacroamingsucccnt, -- 同控制域内终端漫游成功的次数
a.Outacroaminginsucccnt, -- 跨控制域终端漫游转入成功的次数
a.Outacroamingoutsucccnt, -- 跨控制域终端漫游转出成功的次数
a.Portalauthreqcount, --AC收到Portal服务器的鉴权请求数
a.Portalchallengereqcount, --AC收到Portal服务器的Challenge请求数
a.Portalauthrespcount, --AC响应Portal服务器鉴权请求的次数
a.Portalchallengerespcount, --AC响应Portal服务器Challenge请求的次数
a.Radiusauthrespcount, --AC收到Radius的认证请求响应次数
a.Radiusauthresp_Rate, --认证网络成功率
a.Apconnectcount, -- AC当前连接的AP数量,2011-11-15增加,朱必冬
a.Sysbackupcnt --AC主备切换次数,2011-08-03增加,董岳
FROM (SELECT Basek_Ac.Moi AS Moi,
--系统性能
Basek_Ac.Cpuavgusage, -- CPU平均利用率
Basek_Ac.Cpurtusage, -- CPU实时利用率
Basek_Ac.Memavgusage, -- 内存平均利用率
Basek_Ac.Memrtusage, -- 内存实时利用率
Basek_Ac.Onlineusers, -- 在线用户数
Basek_Ac.Apconnectcount, -- AC当前连接的AP数量,2011-11-15增加,朱必冬
--认证
Basek_Ac.Authrequestnum, -- 认证请求数
Basek_Ac.Authsuccnum, -- 认证成功数
Basek_Ac.Authfailnum, -- 认证请求失败次数,2011-11-15增加,朱必冬
Sf_Div(Basek_Ac.Authsuccnum,
Basek_Ac.Authrequestnum) AS Authsuccrate, -- 认证成功率
Basek_Ac.Userlostconnectioncnt, -- 用户异常掉线的总次数
Sf_Div(Basek_Ac.Userlostconnectioncnt,
Basek_Ac.Authsuccnum) AS Userlostconnection_Rate, -- 在线用户异常掉线率
Basek_Ac.Inacroamingsuccrate AS Inacroamingsucccnt, -- 同控制域内终端漫游成功的次数
Basek_Ac.Outacroaminginsucccnt, -- 跨控制域终端漫游转入成功的次数
Basek_Ac.Outacroamingoutsucccnt, -- 跨控制域终端漫游转出成功的次数
Basek_Ac.Portalauthreqcount, --AC收到Portal服务器的鉴权请求数
Basek_Ac.Portalchallengereqcount, --AC收到Portal服务器的Challenge请求数
Basek_Ac.Portalauthrespcount, --AC响应Portal服务器鉴权请求的次数
Basek_Ac.Portalchallengerespcount, --AC响应Portal服务器Challenge请求的次数
Basek_Ac.Radiusauthrespcount, --AC收到Radius的认证请求响应次数
Sf_Div(Basek_Ac.Radiusauthrespcount,
Basek_Ac.Authrequestnum) AS Radiusauthresp_Rate, --认证网络成功率
Basek_Ac.Sysbackupcnt --2012-08-03,董岳 增加AC切换次数字段统计
FROM Basek_Ac) a,
Mv_Ac_Info b
WHERE a.Moi = b.Moi;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
Spm_Log_Error(v_Proc_Name,
'TEMP_PM_AC_MIN_1',
p_Begintime,
SQLERRM);
END;
--2、统计接口性能数据,从Tpa_Basek_Interface中统计
BEGIN
MERGE INTO Temp_Pm_Ac_Min t
USING (SELECT Devid,
SUM(Ifoutoctets) AS Eth_Throughput_Up, --上联口上行流量
SUM(Ifinoctets) AS Eth_Throughput_Down, --上联口下行流量
Round(SUM(Ifoutoctets) / v_Timegranu * 8,
2) AS Eth_Speed_Up, --上联口上行速率
Round(SUM(Ifinoctets) / v_Timegranu * 8,
2) AS Eth_Speed_Down, --上联口下行速率
SUM(Ifinucastpkts) AS Ifinucastpkts,
SUM(Ifinnucastpkts) AS Ifinnucastpkts,
SUM(Ifindiscards) AS Ifindiscardpkts,
SUM(Ifinerrors) AS Ifinerrors,
SUM(Ifoutucastpkts) AS Ifoutucastpkts,
SUM(Ifoutnucastpkts) AS Ifoutnucastpkts,
SUM(Ifoutdiscards) AS Ifoutdiscardpkts,
SUM(Ifouterrors) AS Ifouterrors,
SUM(Ifupdwntimes) AS Ifupdwntimes
FROM Tpa_Basek_Interface
WHERE TIMESTAMP = p_Begintime
AND If_Uplink_Interface = 1
GROUP BY Devid) a
ON (t.Moi = a.Devid AND t.Begintime = p_Begintime)
WHEN MATCHED THEN
UPDATE
SET t.Eth_Throughput_Up = a.Eth_Throughput_Up,
t.Eth_Throughput_Down = a.Eth_Throughput_Down,
t.Eth_Speed_Up = a.Eth_Speed_Up,
t.Eth_Speed_Down = a.Eth_Speed_Down,
--接口
t.Ifinucastpkts = a.Ifinucastpkts,
t.Ifinnucastpkts = a.Ifinnucastpkts,
t.Ifindiscardpkts = a.Ifindiscardpkts,
t.Ifinerrors = a.Ifinerrors,
t.Ifoutucastpkts = a.Ifoutucastpkts,
t.Ifoutnucastpkts = a.Ifoutnucastpkts,
t.Ifoutdiscardpkts = a.Ifoutdiscardpkts,
t.Ifouterrors = a.Ifouterrors,
t.Ifupdwntimes = a.Ifupdwntimes;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
Spm_Log_Error(v_Proc_Name,
'TEMP_PM_AC_MIN_2',
p_Begintime,
SQLERRM);
END;
--3、AC认证用户, 从 tpa_basek_ac_auth 统计
BEGIN
MERGE INTO Temp_Pm_Ac_Min a
USING (SELECT Moi,
Portalonlineusernum, --Portal认证当前在线用户数
Authfreeonlineusernum, --免认证当前在线用户数
Associateauthonlineusernum, --WEP/PSK关联认证当前在线用户数
Autoauthonlineusernum, --SIM/PEAP认证当前在线用户数
Portaluserlostconnectioncnt, --Portal认证用户异常掉线的总次数
Authfreeuserlostconnectioncnt, --免认证用户异常掉线的总次数
Associateauthuserlostconncnt, --WEP/PSK关联认证用户异常掉线的总次数
Autoauthuserlostconnectioncnt, --SIM/PEAP认证用户异常掉线的总次数
Sf_Div(Portaluserlostconnectioncnt,
Portalauthsuccnt + Portalonlineusernum +
Portaluserlostconnectioncnt) AS Portaluserlostconncnt_Rate, --Portal认证用户异常掉线率(%)
Sf_Div(Associateauthuserlostconncnt,
Associateauthsuccnt + Associateauthonlineusernum +
Associateauthuserlostconncnt) AS Assocauthuserlostconncnt_Rate, --WEP/PSK认证用户异常掉线率(%)
Portalauthreqcnt, --Portal认证请求数
Portalauthsuccnt, --Portal认证成功数
Portalauthreqfailcnt, --Portal认证请求失败数
Sf_Div(Portalauthsuccnt,
Portalauthreqcnt) AS Portalauthsuccnt_Rate, --Portal认证成功率(%)
Associateauthreqcnt, --WEP/PSK关联认证请求数
Associateauthsuccnt, --WEP/PSK关联认证成功数
Associateauthreqfailcnt, --WEP/PSK关联认证请求失败数
Sf_Div(Associateauthsuccnt,
Associateauthreqcnt) AS Associateauthsuccnt_Rate --WEP/PSK关联认证成功率(%)
FROM Tpa_Basek_Ac_Auth
WHERE TIMESTAMP = p_Begintime) b
ON (a.Moi = b.Moi AND a.Begintime = p_Begintime)
WHEN MATCHED THEN
UPDATE
SET a.Portalonlineusernum = b.Portalonlineusernum,
a.Authfreeonlineusernum = b.Authfreeonlineusernum,
a.Associateauthonlineusernum = b.Associateauthonlineusernum,
a.Autoauthonlineusernum = b.Autoauthonlineusernum,
a.Portaluserlostconnectioncnt = b.Portaluserlostconnectioncnt,
a.Authfreeuserlostconnectioncnt = b.Authfreeuserlostconnectioncnt,
a.Associateauthuserlostconncnt = b.Associateauthuserlostconncnt,
a.Autoauthuserlostconnectioncnt = b.Autoauthuserlostconnectioncnt,
a.Portaluserlostconncnt_Rate = b.Portaluserlostconncnt_Rate,
a.Assocauthuserlostconncnt_Rate = b.Assocauthuserlostconncnt_Rate,
a.Portalauthreqcnt = b.Portalauthreqcnt,
a.Portalauthsuccnt = b.Portalauthsuccnt,
a.Portalauthreqfailcnt = b.Portalauthreqfailcnt,
a.Portalauthsuccnt_Rate = b.Portalauthsuccnt_Rate,
a.Associateauthreqcnt = b.Associateauthreqcnt,
a.Associateauthsuccnt = b.Associateauthsuccnt,
a.Associateauthreqfailcnt = b.Associateauthreqfailcnt,
a.Associateauthsuccnt_Rate = b.Associateauthsuccnt_Rate;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
Spm_Log_Error(v_Proc_Name,
'TEMP_PM_AC_MIN_3',
p_Begintime,
SQLERRM);
END;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
Spm_Log_Error(v_Proc_Name,
'--',
p_Begintime,
SQLERRM);
END Spr_Pm_Ac_Temp_Min;