我自定义了一个oracle函数,在某些语句中循环调用,这个函数初始运行时间在0.1秒左右;但运行过一段时间或者一起调用的位置比较多的时候会突然变慢,到运行时间2到3秒的运行时间;
求解答:
下面是函数体
create or replace function getNewCustomerdeal(aduserid in number,
daynum in number)
/*近一个月新客户成交量*/
return number is
num number(10);
begin
SELECT COUNT(lead.crm_lead_id) enum
into num
FROM crm_lead lead
left join (select nvl(dual1.nn, 0) ua11
from dual
left join (SELECT 7 nn
FROM AD_PublicHoliday a
WHERE a.Isactive = 'Y'
AND (a.holiday_endtime - a.holiday_starttime) >= 6
AND a.holiday_endtime <
trunc(SYSDATE + daynum)
AND a.holiday_endtime >=
trunc(SYSDATE + daynum) - 6
AND a.Holidaytype = 'A'
AND ROWNUM = 1) dual1 on 1 = 1) uu on 1 = 1
WHERE lead.isactive = 'Y'
AND lead.NAME NOT LIKE '%测%'
AND lead.NAME NOT LIKE '%test%'
AND lead.docstatus = 'I'
AND lead.businesstype = 'A'
AND lead.created < trunc(sysdate + 1 + daynum)
AND lead.notarisedate >=
add_months(trunc(sysdate +
(case when daynum = 0 then 0 when daynum < 0 then
daynum + 1 else daynum end) - uu.ua11,
'dd'),
-1)
AND LEAD.NOTARISEDATE < trunc(sysdate + 1 + daynum)
AND NOT EXISTS
(SELECT deal.crm_lead_id
FROM crm_lead deal
WHERE deal.isactive = 'Y'
AND deal.docstatus = 'I'
AND deal.businesstype = 'A'
AND deal.businesstype = 'A'
AND deal.NOTARISEDATE >=
add_months(trunc(sysdate + daynum, 'dd'), -24)
AND deal.NOTARISEDATE < trunc(sysdate + 1 + daynum)
and deal.created < trunc(sysdate + 1 + daynum)
AND deal.c_bpartner_id = lead.c_bpartner_id
AND deal.crm_lead_id <> lead.crm_lead_id
AND deal.notarisedate < lead.notarisedate
AND (CASE WHEN deal.mainarea IN ('A1', 'A5', 'A6') THEN
'A1,A5,A6,' WHEN deal.mainarea IN ('A4', 'A7') THEN
'A4,A7,' WHEN deal.mainarea IN ('A8') THEN 'A8,' WHEN
deal.mainarea IN ('A2', 'A3') THEN 'A2,A3,' WHEN
deal.mainarea IN ('A10', 'A11', 'A12') THEN 'A10,A11,A12,' END) LIKE
'%' || lead.mainarea || ',%')
and lead.manager_id = aduserid;
return num;
end;
上面sql的执行计划