reachl

0 积分	1 好友	1 主题

发消息

[RAC性能调优] 业务系统慢的原因分析待指导

1^#

发表于 2014-2-8 13:37:10 | 查看: 4670| 回复: 8

本帖最后由 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;

分析表.JPG (184.44 KB, 下载次数: 248)