业务系统慢的原因分析待指导
本帖最后由 reachl 于 2014-2-8 16:02 编辑老师您好：
俺所维护的业务系统当发现交易量较大时速度很慢,经跟踪AWR报表发现效率较低或执行时间较长的代码请您给予指正从何入手或者如何优化。
业务生产库运行在AIX系统的ORACLE RAC环境中。AIX的版本为6.1版本。ORACLE为10.2.0.2已升级至10.2.4.
AIX系统中内存为16G同时lsps -al为32G。
Session与Process为500.
代码如下：
create or replace procedure sSysSal_DecToDec
(pi_NowNum IN tStoCkBody.SerialNo%type,
pi_NextNum OUT tStoCkBody.SerialNo%type,
pi_Result OUT integer,
ps_Message OUT varchar2)
Is
cursor Cur_GetDecToDec is
select OrgCode,DepId,CkCode,PcNo,SaleNo,PageNo,LnNo,JzDate,PluID,XsCount,CKCOUNT,
Round(SsTotal*CKCOUNT/XsCount,2) As SsTotal,
Round(CxTotal*CKCOUNT/XsCount,2) As CxTotal,
Round(HyTotal*CKCOUNT/XsCount,2) As HyTotal,
Round(YsTotal*CKCOUNT/XsCount,2) As YsTotal,
(select JjMode from tSkuPluEx where OrgCode=A.OrgCode and PluID=A.PluID) as JjMode
from (select X.SaleNo,X.PageNo,X.LnNo,X.JzDate,X.XsCount,Y.OrgCode,Y.DepId,
Y.CkCode,Y.PcNo,Y.PluID,Y.KcCount,
case when (Y.KcCount-X.SumCount)>=0 then X.XsCount
else (Y.KcCount-X.SumCount)+X.XsCount end As CkCount,
X.SsTotal,X.CxTotal,X.HyTotal,X.YsTotal
from (select OrgCode,SaleNo,PageNo,LnNo,JzDate,DepId,PLUID,PluTypeF,XsCount,
sum(XsCount)
over(partition by OrgCode,DepId,PLUID,PluTypeF
order by OrgCode,SaleNo,PageNo,LnNo Asc) As SumCount,
SsTotal,CxTotal,HyTotal,YsTotal
from tSys_DecA01_Temp) X,
(select OrgCode,DepId,CkCode,PcNo,PluId,PluType,PluTypeF,
KcCount,JhDate,row_number()
over(partition by OrgCode,DepId,PLUID,PluTypeF
order by JhDate Asc) As NumCount
from tSys_DecB01_Temp) Y
where X.OrgCode=Y.OrgCode and X.DepId=Y.DepId and X.PluId=Y.PluId
and X.PluTypeF=Y.PluTypeF and Y.NumCount=1) A
where CkCount>0;
cursor Cur_GetSalKc(ms_Org tStkLsKc.OrgCode%type,
mi_Dep tStkLsKc.DepId%type,
ms_Ck tStkLsKc.CkCode%type,
ms_Pc tStkLsKc.PcNo%type) is
select A.KcCount,A.HCost,A.WCost,B.HJPrice,B.WJPrice
from tSys_DecB01_Temp A,tStkPc B
where A.OrgCode=ms_Org and A.DepId=mi_Dep
and A.CkCode=ms_Ck and A.PcNo=ms_Pc
and B.PcNo=ms_Pc;
vr_K Cur_GetSalKc%rowtype;
vb_IsExists boolean:=true;
Begin
/*=================变量初始化=================*/
pi_Result:=-1;
ps_Message:='——变量初始化失败！';
vb_IsExists:=true;
pi_NextNum:=pi_NowNum+1;
/*=================流水与库存对冲=================*/
ps_Message:='——零售销售数据与连锁库存对冲失败！';
while vb_IsExists
loop
ps_Message:='——取得零售销售数据与连锁库存的对冲数据失败！';
vb_IsExists:=false;
for vr_S in Cur_GetDecToDec
Loop
vb_IsExists:=true;
open Cur_GetSalKc(vr_S.OrgCode,vr_S.DepId,vr_S.CkCode,vr_S.PcNo);
fetch Cur_GetSalKc into vr_K;
close Cur_GetSalKc;
vr_K.HCost:=vr_S.CkCount*vr_K.HCost/vr_K.KcCount;
vr_K.WCost:=vr_S.CkCount*vr_K.WCost/vr_K.KcCount;
if vr_S.XsCount=vr_S.CkCount then
delete from tSys_DecA01_Temp
where OrgCode=vr_S.OrgCode
and SaleNo=vr_S.SaleNo
and PageNo=vr_S.PageNo
and LnNo=vr_S.LnNo;
else
update tSys_DecA01_Temp
set XsCount=XsCount-vr_S.CkCount,
SsTotal=SsTotal-vr_S.SsTotal,
CxTotal=CxTotal-vr_S.CxTotal,
HyTotal=HyTotal-vr_S.HyTotal,
YsTotal=YsTotal-vr_S.YsTotal
where OrgCode=vr_S.OrgCode
and SaleNo=vr_S.SaleNo
and PageNo=vr_S.PageNo
and LnNo=vr_S.LnNo;
end if;
if vr_K.KcCount-vr_S.CkCount=0 then
delete from tSys_DecB01_Temp
where OrgCode=vr_S.OrgCode
and DepId=vr_S.DepId
and CkCode=vr_S.CkCode
and PcNo=vr_S.PcNo;
else
update tSys_DecB01_Temp
set KcCount=KcCount-vr_S.CkCount,
HCost=HCost-vr_K.HCost,
WCost=WCost-vr_K.WCost
where OrgCode=vr_S.OrgCode
and DepId=vr_S.DepId
and CkCode=vr_S.CkCode
and PcNo=vr_S.PcNo;
end if;
update tStkLsKc
set KcCount=KcCount-vr_S.CkCount,
HCost=HCost-vr_K.HCost,
WCost=WCost-vr_K.WCost
where OrgCode=vr_S.OrgCode and DepId=vr_S.DepId
and CkCode=vr_S.CkCode and WlAreaCode='*'
and PcNo=vr_S.PcNo;
update tStkPc set XsCount=XsCount+vr_S.CkCount where PcNo=vr_S.PcNo;
/*=================冲减加权移动平均库存=================*/
ps_Message:='——冲减存货加权平均数据';
if vr_S.JjMode = '2' then
select KcCount,YKcHJPrice,YKcWJPrice,YKcHCost,YKcWCost
into vr_K.KcCount,vr_K.HJPrice,
vr_K.WJPrice,vr_K.HCost,vr_K.WCost
from tStkKcPrice
where OrgCode=vr_S.OrgCode
and PreDepID=PUB_DB.GetPreDepId_ORA(vr_S.OrgCode,vr_S.DepId)
and PluId=vr_S.PluId
and ExPluCode='*';
vr_K.HCost := vr_K.HCost*(vr_S.CkCount/vr_K.KcCount);
vr_K.WCost := vr_K.WCost*(vr_S.CkCount/vr_K.KcCount);
end if;
update tStkKcPrice
set KcCount=KcCount-vr_S.CkCount,
YKcHCost=YKcHCost-vr_K.HCost,
YKcWCost=YKcWCost-vr_K.WCost
where OrgCode=vr_S.OrgCode
and PreDepId=PUB_DB.GetPreDepId_ORA(vr_S.OrgCode,vr_S.DepId)
and PluId=vr_S.PluId
and ExPluCode='*';
insert into tSys_DecC01_Temp(SERIALNO,OrgCode,DepId,CkCode,PcNo,
SaleNo,PageNo,LnNo,JzDate,PluID,CKCOUNT,SsTotal,CxTotal,
HyTotal,YsTotal,HCost,WCost,HJPrice,WJPrice,JjMode)
values(pi_NextNum,vr_S.OrgCode,vr_S.DepId,vr_S.CkCode,vr_S.PcNo,
vr_S.SaleNo,vr_S.PageNo,vr_S.LnNo,vr_S.JzDate,vr_S.PluID,
vr_S.CKCOUNT,vr_S.SsTotal,vr_S.CxTotal,vr_S.HyTotal,
vr_S.YsTotal,vr_K.HCost,vr_K.WCost,vr_K.HJPrice,vr_K.WJPrice,vr_S.JjMode);
pi_NextNum:=pi_NextNum+1;
end Loop;
end Loop;
/*=================成功返回=================*/
pi_Result:=1;
ps_Message:='零售销售数据与连锁库存对冲成功！';
/*=================错误处理=================*/
Exception
when others then
begin
pi_Result:=-1;
if Cur_GetDecToDec%isopen then close Cur_GetDecToDec; end if;
if Cur_GetSalKc%isopen then close Cur_GetSalKc; end if;
ps_Message:=nvl(ps_Message,'零售销售数据与连锁库存对冲时发生未知错误：')||Sqlerrm ;
ps_Message:=substr(ps_Message,1,2000);
end;
End sSysSal_DecToDec;
阅读权限这么高，看不了呀 不好意思。我改了请帮助指导谢谢 喜爱截图是个极坏的习惯 给老师您添麻烦了！以后我改 请老师给予指导谢谢您 awr跨度是5小时的？ 是的！上述代码是通过AWR中截取！执行计划中也有所影响。
试问能否对代码能有所优化谢谢 压力不大，cost 不高有啥分析的？
页:
[1]