CREATE OR REPLACE FUNCTION Sp_Tmp_ManualReqStat(
iBeginTime DATE, -- 统计开始时间
iEndTime DATE, -- 统计结束时间
iTimeUnit NUMBER, -- 通计单位。1.时段;2.小时;3.天;4.周;5.旬;6.月;7.季度;8.半年;9.年
iAdmBeginTime DATE, -- 统计单位起始时间
iAdmEndTime DATE, -- 统计单位结束时间
iSubCCNo NUMBER, -- 子系统号
iUserName VARCHAR2, -- 用户姓名
iType INT, -- 统计类型。0 -- 时间, 1 -- 地区
iTypeid INT -- 类型 0--旧报表,1--新报表
) RETURN INTEGER
AS
UpTime DATE; -- 统计结束时间(结合BeginTRet归组数据用)
BeginTRet DATE; -- 循环时间
TempTime DATE; -- 下一步长的边界
STValue VARCHAR2(50); -- 统计时间索引(报表的统计时间段)
SerialNo NUMBER; -- 序号
tRet INT;
eNOData EXCEPTION;BEGIN -- 判断权限
-- t_Tmp_CCIDVDN 中有数据,说明权限成立
tRet := Sp_Tmp_CCVDNHelp(iUserName, iSubCCNo);
IF tRet = 1 THEN
RAISE eNOData;
END IF; -- 删除临时表中的数据
DELETE FROM t_Tmp_ManualReqStat
WHERE UserName = iUserName;
COMMIT; --置循环初始条件
BeginTRet := iBeginTime;
SerialNo := 0; --更新统计临时表
-- 按时间统计
IF iType = 0 THEN
WHILE BeginTRet < iEndTime LOOP
-- 调用sp_Utl_CalculateTimeBorder,为步长边界TempTime和StatisticalTimeValue赋值
Sp_Utl_CalculateTimeBorder(BeginTRet, iEndTime, iAdmBeginTime, iAdmEndTime,
iTimeUnit, TempTime, UpTime, STValue); -- 往临时表插入数据,起始时间为BeginTRet,结束时间为UpTime
INSERT INTO t_Tmp_ManualReqStat(
SID, -- 序号
StatTime, -- 时间段
--RequestNum, -- 占用次数
QueueFailNum, -- 排队挂机数
CallSuccNum, -- 通话次数
UserName
)
SELECT /*+ leading(a) use_hash(a,b,c,d,e) */
SerialNo,
STValue,
-- NVL(SUM(a.occupynum), 0),
NVL(SUM(a.QueueAbortNum), 0),
NVL(SUM(a.CallSuccessNum)+SUM(a.NoAckNum), 0),
iUserName
FROM t_DayLog_CallByService a,t_Tmp_CCIDVDN b, t_Tmp_City d, t_Tmp_CustLevel e
WHERE a.CURCCNo = b.CCID
AND a.VDN = b.VDN
AND LogDate >= BeginTRet
AND LogDate < UpTime
AND (DeviceType = 1 or DeviceType =2)
AND a.UserLevel = e.ID
AND a.curccno = e.subccno
AND a.vdn = e.vdn
AND a.CityID = d.ID
AND b.Username = iUserName
AND d.Username = iUserName
AND e.Username = iUserName;
IF iTypeid = 0 THEN
-- 统计用户按键请求转人工数
INSERT INTO t_Tmp_ManualReqStat(
SID, -- 序号
StatTime, -- 时间段
RequestNum, -- 请求数
UserName
)
SELECT /*+ leading(a) use_hash(a,b) */
SerialNo,
STValue,
NVL(SUM(PressCount), 0),
iUserName
FROM icdmain.t_Sce_ManualReqKeyLog a,t_Tmp_CustLevel b
WHERE a.userlevel=b.id
and a.LogDate >= BeginTRet
AND a.LogDate < UpTime
and b.subccno=1
and b.vdn=1
and b.username=iUserName;
ELSE
--dbms_output.put_line(BeginTRet);
--dbms_output.put_line(UpTime);
INSERT INTO t_Tmp_ManualReqStat(
SID, -- 序号
StatTime, -- 时间段
RequestNum, -- 请求数
UserName
)
SELECT /*+ leading(a) use_hash(a,b) */
SerialNo,
STValue,
NVL(SUM(PressCount), 0),
iUserName
FROM icdmain.t_Daylog_Autokeytrace a,t_Tmp_CustLevel b
WHERE a.USERGRADE=b.id
and a.LogDate >= BeginTRet
AND a.LogDate < UpTime
and (b.subccno=1 )
and b.vdn=1
AND a.operateresult IN (81,82)
and b.username=iUserName; INSERT INTO t_Tmp_ManualReqStat(
SID, -- 序号
StatTime, -- 时间段
RequestNum, -- 请求数
UserName
)
SELECT /*+ leading(a) use_hash(a,b) */
SerialNo,
STValue,
NVL(SUM(a.occupynum), 0),
iUserName
FROM t_DayLog_CallByService a,t_Tmp_CustLevel b
WHERE a.UserLevel = b.ID AND a.curccno = b.subccno
AND a.vdn = b.vdn
and a.LogDate >= BeginTRet
AND a.LogDate < UpTime
and b.username=iUserName
and (a.devicetype=1 or a.devicetype=2);
END IF;
BeginTRet := TempTime;
SerialNo := SerialNo + 1; END LOOP; END IF; COMMIT; RETURN 0;EXCEPTION
WHEN eNOData THEN
RETURN 1;
-- 不处理 WHEN OTHERS THEN 情况,出现异常时,直接在调用他的报表过程WHEN OTHERS THEN 处理。
END Sp_Tmp_ManualReqStat;
/
问题如下:昨天晚上数据库扩容,在数据迁移完成后,今天早上客户反应报表查询速度明显减慢。经过新数据库与旧数据库对比发现数据量一样,索引都以建立。一开始报表采用的是上面存储过程,后来我们发现瓶颈就在上面存储过程红色标注部分。后来将红色部分改成 and a.LogDate >= to_date(to_char(BeginTRet,'yyyymmdd hh24miss'),'yyyymmdd hh24miss')
AND a.LogDate < to_date(to_char(UpTime,'yyyymmdd hh24miss'),'yyyymmdd hh24miss')
,查询一切正常了。想问一下为什么会这样,两个数据库参数都是一样的。难到LOGDATE字段发生了隐式转换吗?造成全表扫描了!
改后只是格式化了BeginTRet和UpTime两个日期的格式。
不太理解请高手指点
iBeginTime DATE, -- 统计开始时间
iEndTime DATE, -- 统计结束时间
iTimeUnit NUMBER, -- 通计单位。1.时段;2.小时;3.天;4.周;5.旬;6.月;7.季度;8.半年;9.年
iAdmBeginTime DATE, -- 统计单位起始时间
iAdmEndTime DATE, -- 统计单位结束时间
iSubCCNo NUMBER, -- 子系统号
iUserName VARCHAR2, -- 用户姓名
iType INT, -- 统计类型。0 -- 时间, 1 -- 地区
iTypeid INT -- 类型 0--旧报表,1--新报表
) RETURN INTEGER
AS
UpTime DATE; -- 统计结束时间(结合BeginTRet归组数据用)
BeginTRet DATE; -- 循环时间
TempTime DATE; -- 下一步长的边界
STValue VARCHAR2(50); -- 统计时间索引(报表的统计时间段)
SerialNo NUMBER; -- 序号
tRet INT;
eNOData EXCEPTION;BEGIN -- 判断权限
-- t_Tmp_CCIDVDN 中有数据,说明权限成立
tRet := Sp_Tmp_CCVDNHelp(iUserName, iSubCCNo);
IF tRet = 1 THEN
RAISE eNOData;
END IF; -- 删除临时表中的数据
DELETE FROM t_Tmp_ManualReqStat
WHERE UserName = iUserName;
COMMIT; --置循环初始条件
BeginTRet := iBeginTime;
SerialNo := 0; --更新统计临时表
-- 按时间统计
IF iType = 0 THEN
WHILE BeginTRet < iEndTime LOOP
-- 调用sp_Utl_CalculateTimeBorder,为步长边界TempTime和StatisticalTimeValue赋值
Sp_Utl_CalculateTimeBorder(BeginTRet, iEndTime, iAdmBeginTime, iAdmEndTime,
iTimeUnit, TempTime, UpTime, STValue); -- 往临时表插入数据,起始时间为BeginTRet,结束时间为UpTime
INSERT INTO t_Tmp_ManualReqStat(
SID, -- 序号
StatTime, -- 时间段
--RequestNum, -- 占用次数
QueueFailNum, -- 排队挂机数
CallSuccNum, -- 通话次数
UserName
)
SELECT /*+ leading(a) use_hash(a,b,c,d,e) */
SerialNo,
STValue,
-- NVL(SUM(a.occupynum), 0),
NVL(SUM(a.QueueAbortNum), 0),
NVL(SUM(a.CallSuccessNum)+SUM(a.NoAckNum), 0),
iUserName
FROM t_DayLog_CallByService a,t_Tmp_CCIDVDN b, t_Tmp_City d, t_Tmp_CustLevel e
WHERE a.CURCCNo = b.CCID
AND a.VDN = b.VDN
AND LogDate >= BeginTRet
AND LogDate < UpTime
AND (DeviceType = 1 or DeviceType =2)
AND a.UserLevel = e.ID
AND a.curccno = e.subccno
AND a.vdn = e.vdn
AND a.CityID = d.ID
AND b.Username = iUserName
AND d.Username = iUserName
AND e.Username = iUserName;
IF iTypeid = 0 THEN
-- 统计用户按键请求转人工数
INSERT INTO t_Tmp_ManualReqStat(
SID, -- 序号
StatTime, -- 时间段
RequestNum, -- 请求数
UserName
)
SELECT /*+ leading(a) use_hash(a,b) */
SerialNo,
STValue,
NVL(SUM(PressCount), 0),
iUserName
FROM icdmain.t_Sce_ManualReqKeyLog a,t_Tmp_CustLevel b
WHERE a.userlevel=b.id
and a.LogDate >= BeginTRet
AND a.LogDate < UpTime
and b.subccno=1
and b.vdn=1
and b.username=iUserName;
ELSE
--dbms_output.put_line(BeginTRet);
--dbms_output.put_line(UpTime);
INSERT INTO t_Tmp_ManualReqStat(
SID, -- 序号
StatTime, -- 时间段
RequestNum, -- 请求数
UserName
)
SELECT /*+ leading(a) use_hash(a,b) */
SerialNo,
STValue,
NVL(SUM(PressCount), 0),
iUserName
FROM icdmain.t_Daylog_Autokeytrace a,t_Tmp_CustLevel b
WHERE a.USERGRADE=b.id
and a.LogDate >= BeginTRet
AND a.LogDate < UpTime
and (b.subccno=1 )
and b.vdn=1
AND a.operateresult IN (81,82)
and b.username=iUserName; INSERT INTO t_Tmp_ManualReqStat(
SID, -- 序号
StatTime, -- 时间段
RequestNum, -- 请求数
UserName
)
SELECT /*+ leading(a) use_hash(a,b) */
SerialNo,
STValue,
NVL(SUM(a.occupynum), 0),
iUserName
FROM t_DayLog_CallByService a,t_Tmp_CustLevel b
WHERE a.UserLevel = b.ID AND a.curccno = b.subccno
AND a.vdn = b.vdn
and a.LogDate >= BeginTRet
AND a.LogDate < UpTime
and b.username=iUserName
and (a.devicetype=1 or a.devicetype=2);
END IF;
BeginTRet := TempTime;
SerialNo := SerialNo + 1; END LOOP; END IF; COMMIT; RETURN 0;EXCEPTION
WHEN eNOData THEN
RETURN 1;
-- 不处理 WHEN OTHERS THEN 情况,出现异常时,直接在调用他的报表过程WHEN OTHERS THEN 处理。
END Sp_Tmp_ManualReqStat;
/
问题如下:昨天晚上数据库扩容,在数据迁移完成后,今天早上客户反应报表查询速度明显减慢。经过新数据库与旧数据库对比发现数据量一样,索引都以建立。一开始报表采用的是上面存储过程,后来我们发现瓶颈就在上面存储过程红色标注部分。后来将红色部分改成 and a.LogDate >= to_date(to_char(BeginTRet,'yyyymmdd hh24miss'),'yyyymmdd hh24miss')
AND a.LogDate < to_date(to_char(UpTime,'yyyymmdd hh24miss'),'yyyymmdd hh24miss')
,查询一切正常了。想问一下为什么会这样,两个数据库参数都是一样的。难到LOGDATE字段发生了隐式转换吗?造成全表扫描了!
改后只是格式化了BeginTRet和UpTime两个日期的格式。
不太理解请高手指点
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货