求oracle 查询调用存储过程非常慢的解决方案,高手赐教
gettransacttime 内部函数
执行里面的查询,执行起来2秒select oprt.F_ID, oprt.f_dispatchdate,
gettransacttime (oprt.f_dispatchdate,TO_DATE ('2009-5-5 17:14:02','YYYY-MM-DD HH24:MI:SS')) Beginusetime, --得到案件到目前为止处理的时间(分)
( NVL (oprt.f_timelimit, 0) * 60 + NVL (oprt.f_timeslowminute, 0) + NVL (oprt.f_timelapseminute, 0) ) f_timelimit --总共处理分钟数
from oprt_s_main oprt where oprt.f_moveend IN (SELECT f_code
FROM sys_s_state sta WHERE sta.f_node IN (6))
当嵌套后,执行起来将近40秒
select F_ID,f_dispatchdate,Beginusetime,f_timelimit from
(
select oprt.F_ID, oprt.f_dispatchdate,
gettransacttime (oprt.f_dispatchdate,TO_DATE ('2009-5-5 17:14:02','YYYY-MM-DD HH24:MI:SS')) Beginusetime, --得到案件到目前为止处理的时间(分)
( NVL (oprt.f_timelimit, 0) * 60 + NVL (oprt.f_timeslowminute, 0) + NVL (oprt.f_timelapseminute, 0) ) f_timelimit --总共处理分钟数
from oprt_s_main oprt where oprt.f_moveend IN (SELECT f_code
FROM sys_s_state sta WHERE sta.f_node IN (6))
) t where f_timelimit between Beginusetime and (Beginusetime + 120 )
去除where条件,执行起来2秒
select F_ID,f_dispatchdate,Beginusetime,f_timelimit from
(
select oprt.F_ID, oprt.f_dispatchdate,
gettransacttime (oprt.f_dispatchdate,TO_DATE ('2009-5-5 17:14:02','YYYY-MM-DD HH24:MI:SS')) Beginusetime, --得到案件到目前为止处理的时间(分)
( NVL (oprt.f_timelimit, 0) * 60 + NVL (oprt.f_timeslowminute, 0) + NVL (oprt.f_timelapseminute, 0) ) f_timelimit --总共处理分钟数
from oprt_s_main oprt where oprt.f_moveend IN (SELECT f_code
FROM sys_s_state sta WHERE sta.f_node IN (6))
) t
将内部函数gettransacttime 去掉,改成下面这样,执行起来2秒
select F_ID,f_dispatchdate,Beginusetime,f_timelimit from
(
select oprt.F_ID, oprt.f_dispatchdate,
10 Beginusetime, --得到案件到目前为止处理的时间(分)
( NVL (oprt.f_timelimit, 0) * 60 + NVL (oprt.f_timeslowminute, 0) + NVL (oprt.f_timelapseminute, 0) ) f_timelimit --总共处理分钟数
from oprt_s_main oprt where oprt.f_moveend IN (SELECT f_code
FROM sys_s_state sta WHERE sta.f_node IN (6))
) t where f_timelimit between Beginusetime and (Beginusetime + 120 )
希望高手多多指教,这个问题困扰小弟一个下午了,小弟在此先谢过,虽然找出个大概原因,但不知道如何解决