PL/SQL调优求助

Question

if ni<>0 and nj=0 then
     nbjye:=0;
      nlxye:=0;
      nk:=0;
     declare 
       cursor jk_cur is
       select rtrim(w002),w003,w004,w005,rtrim(w012) from wxjkk
       where  trim(h001)=rtrim(nh001) and p011 is not null
       order by w003,w008;
 BEGIN
     OPEN JK_CUR;
     fetch  jk_cur into nw002,nw003,nw004,nw005,nw012;
   WHILE JK_CUR %FOUND LOOP
         nbjye:=nbjye+nw004;
         nlxye:=nlxye+nw005;
         nk:=nk+1;
       insert /* +APPEND */  into temp_fhtz values(nw003,nw012,nw002,nw004,nw005,0,0,nbjye,nlxye,nbjye+nlxye,nk);
 fetch  jk_cur into nw002,nw003,nw004,nw005,nw012;
  END LOOP;
  CLOSE JK_CUR;
  END;

Answer 1

没看出什么,如果大数据量的话建议使用BULK COLLECT,FORALL 
TYPE t_jzny IS TABLE OF char(6); 
b_jzny t_jzny; 
v_rowcount NUMBER; 
CURSOR cur_bulk
IS
SELECT ny
FROM <table>
WHERE ROWNUM <= 6;
…………………
-- 初始化数组
OPEN cur_bulk;FETCH cur_bulk
BULK COLLECT INTO b_jzny;CLOSE cur_bulk;
………………………
FORALL v_i IN 1 .. v_rowcount
Insert into <table_name>
……………..
values
(
………….
B_jzny(v_i);
…………..
)

Answer 2

如果语句中可以使用多个分析函数的话，尝试一下下面的语句。
这样就不需要循环通过游标计算了。
我这里没有oracle环境，不能测试 :(insert /* +APPEND */  into temp_fhtz
select w003,rtrim(w012),rtrim(w002),w004,w005,0,0,sum(w004) over(order by w003,w008),
sum(w005) over(order by w003,w008),sum(w004+w005) over(order by w003,w008),row_number() over(order by w003,w008) from wxjkk
where  trim(h001)=rtrim(nh001) and p011 is not null;

Answer 3

还可以加nologging减少日志生成
insert /* +APPEND */ into temp_fhtz nologging
select ....

Answer 4

完整的过程如下，请高手来优化！！！！ CREATE OR REPLACE  PROCEDURE "DBO".""  (
 nh001 varchar,
 nfwxzbm varchar,
 nbegindate1 varchar,
 nenddate1 varchar,
 nret IN out int,
 ncur out cur.cur,
 nrowcount IN out int
 --0023?????á×?
)
----????
as
   nw002 varchar(20);
   nw003 DATE;nw004 number;nw005 number;nw012 varchar(20);
   nz002 varchar(20);nz003 DATE;nz004 number;nz005 number;nz012 varchar(20);
   nbjye number;nlxye number;ni int;nj int;nk int; nh013 varchar(20);
   nbegindate date;
   nenddate date;
begin
 delete from temp_fhtz;
  nbegindate:=to_date(nbegindate1,'yyyy-mm-dd');
  nenddate:=to_date(nenddate1,'yyyy-mm-dd');
  select rtrim(h013) into nh013 from house where trim(h001)=trim(nh001);
  select  count(*) into ni from wxjkk where trim(h001)=rtrim(nh001) and p011 is not null;
  select  count(*) into nj from wxzqk where trim(h001)=rtrim(nh001) and p011 is not null;
  
 SAVEPOINT TRAN;
 ---------------------------------------------------------------------------------------------------------------------------------------------
   if ni<>0 and nj=0 then
     nbjye:=0;
      nlxye:=0;
      nk:=0;
     declare 
       cursor jk_cur is
       select rtrim(w002),w003,w004,w005,rtrim(w012) from wxjkk
       where  trim(h001)=rtrim(nh001) and p011 is not null
       order by w003,w008;
 BEGIN
     OPEN JK_CUR;
     fetch  jk_cur into nw002,nw003,nw004,nw005,nw012;
   WHILE JK_CUR %FOUND LOOP
         nbjye:=nbjye+nw004;
         nlxye:=nlxye+nw005;
         nk:=nk+1;
       insert /* +APPEND */  into temp_fhtz values(nw003,nw012,nw002,nw004,nw005,0,0,nbjye,nlxye,nbjye+nlxye,nk);
 fetch  jk_cur into nw002,nw003,nw004,nw005,nw012;
  END LOOP;
  CLOSE JK_CUR;
  END;
---------------------------------------------------------------------------------------------------------------------------------------------
   ELSIF nj<>0 and ni=0 THEN
     nbjye:=0;
     nlxye:=0;
     nk:=0;
     declare cursor zq_cur is
       select rtrim(z002),z003,z004,z005,rtrim(z012) from wxzqk
       where  trim(h001)=rtrim(nh001) and p011 is not null 
       order by z003,z008;
     BEGIN
     open zq_cur;
     fetch zq_cur into nz002,nz003,nz004,nz005,nz012;
     WHILE ZQ_CUR %FOUND LOOP
       nbjye:=nbjye-nz004;
      nlxye:=nlxye-nz005;
      nk:=nk+1;
      insert /* +APPEND */ into temp_fhtz values(nz003,nz012,nz002,0,0,nz004,nz005,nbjye,nlxye,nbjye+nlxye,nk);
    fetch zq_cur into nz002,nz003,nz004,nz005,nz012;
    END LOOP;
     close zq_cur;
   END;
---------------------------------------------------------------------------------------------------------------------------------------------
   elSIf ni<>0 and nj<>0 THEN
     nbjye:=0;
     nlxye:=0;
     nk:=0;
     declare cursor jk1_cur is
       select rtrim(w002),w003,w004,w005,rtrim(w012) from wxjkk
       where  trim(h001)=rtrim(nh001) and p011 is not null
              order by w003,w008;
      cursor zq1_cur is
       select rtrim(z002),z003,z004,z005,rtrim(z012) from wxzqk
       where  trim(h001)=rtrim(nh001) and p011 is not null 
       order by z003,z008;
    begin
     open jk1_cur;
     open zq1_cur;
     ni:=ni-1;
     nj:=nj-1;
     fetch  jk1_cur into nw002,nw003,nw004,nw005,nw012;
     fetch  zq1_cur into nz002,nz003,nz004,nz005,nz012;
     while (jk1_cur %found)OR(zq1_cur %found) loop
      if nw003 <= nz003 then
         if ni>0 then
           nbjye:=nbjye+nw004;
           nlxye:=nlxye+nw005;
           nk:=nk+1;
           insert /* +APPEND */  into temp_fhtz values(nw003,nw012,nw002,nw004,nw005,0,0,nbjye,nlxye,nbjye+nlxye,nk);
           fetch  jk1_cur into nw002,nw003,nw004,nw005,nw012;
            ni:=ni-1;
         elsif ni=0 then
            nbjye:=nbjye+nw004;
             nlxye:=nlxye+nw005;
             nk:=nk+1;
           insert /* +APPEND */ into  temp_fhtz values(nw003,nw012,nw002,nw004,nw005,0,0,nbjye,nlxye,nbjye+nlxye,nk);
             ni:=ni-1;
          else
           nw003:=nw003+1;
         end if;
       else
         if nj>0 then
           nbjye:=nbjye-nz004;
           nlxye:=nlxye-nz005;
           nk:=nk+1;
           insert  into  temp_fhtz values(nz003,nz012,nz002,0,0,nz004,nz005,nbjye,nlxye,nbjye+nlxye,nk);
           fetch  zq1_cur into nz002,nz003,nz004,nz005,nz012;
           nj:=nj-1;
         elsif nj=0 then
            nbjye:=nbjye-nz004;
            nlxye:=nlxye-nz005;
            nk:=nk+1;
           insert into temp_fhtz values(nz003,nz012,nz002,0,0,nz004,nz005,nbjye,nlxye,nbjye+nlxye,nk);
            nj:=nj-1;
         else
         nz003:= nz003-1;
         end if;
       end if;
       if (ni<0) and (nj<0) then
        exit;
       END IF;
    END loop;
     close jk1_cur ;
     close zq1_cur  ;
   end;
 END IF;
-----------------------------------------------------------------------------------------------------------------------------------------------
open ncur for
 select  '' as w003,
 nh013 as w012,
 max((select mc from bm_char where bm = '0023')) as w002,
 decode(sum(w004),null,0,sum(w004)) as w004,
 decode(sum(w005),null,0,sum(w005)) as w005,
 decode(sum(z004),null,0,sum(z004)) as z004,
 decode(sum(z005),null,0,sum(z005)) as z005,
 decode(sum(w004)-sum(z004),null,0,sum(w004)-sum(z004)) as bjye,
 decode(sum(w005)-sum(z005),null,0,sum(w005)-sum(z005))as lxye,
 decode(sum(w004)-sum(z004)+sum(w005)-sum(z005),null,0,sum(w004)-sum(z004)+sum(w005)-sum(z005)) as xj,
 0 as xh
 from temp_fhtz where  w003 < nbegindate
 union
  select to_char(w003,'yyyy-mm-dd'),w012,w002,w004,w005,z004,z005,bjye,lxye,xj,xh  from temp_fhtz
  where w003>= nbegindate and w003<= nenddate order by xh;
  select count(*)+1 into nrowcount from temp_fhtz
  where w003>= nbegindate and w003<= nenddate order by xh;
  nret:=0;
  return;
END;

Answer 5

BEGIN
 DELETE FROM temp_fhtz;
  nbegindate:=to_date(nbegindate1,'yyyy-mm-dd');
  nenddate:=to_date(nenddate1,'yyyy-mm-dd');
  SELECT rtrim(h013) INTO nh013 FROM house WHERE TRIM(h001)=TRIM(nh001);
  SELECT  COUNT(*) INTO ni FROM wxjkk WHERE TRIM(h001)=rtrim(nh001) AND p011 IS NOT null;
  SELECT  COUNT(*) INTO nj FROM wxzqk WHERE TRIM(h001)=rtrim(nh001) AND p011 IS NOT null;
  
 SAVEPOINT TRAN;
 ---------------------------------------------------------------------------------------------------------------------------------------------
   IF ni<>0 AND nj=0 THEN
       INSERT /* +APPEND */ INTO temp_fhtz 
       SELECT w003,rtrim(w012),rtrim(w002),w004,w005,0,0,
       SUM (w004) OVER (ORDER BY w003,w008) AS nbjye,
       SUM (w005) OVER (ORDER BY w003,w008) AS nlxye,
       SUM (w004+w005) OVER (ORDER BY w003,w008),
       COUNT(*) OVER (ORDER BY w003,w008)
       FROM wxjkk
       WHERE TRIM(h001)=rtrim(nh001) 
       AND p011 IS NOT NULL
       ORDER BY w003,w008;
---------------------------------------------------------------------------------------------------------------------------------------------
   ELSIF nj<>0 AND ni=0 THEN
       INSERT /* +APPEND */ INTO temp_fhtz 
       SELECT z003,rtrim(z012),rtrim(z002),0,0,z004,z005,
       FIRST_VALUE (w004) OVER (ORDER BY w003,w008)
       - SUM (w004) OVER (ORDER BY w003,w008) AS nbjye,
       FIRST_VALUE (w005) OVER (ORDER BY w003,w008)
       - SUM (w005) OVER (ORDER BY w003,w008) AS nlxye,
       FIRST_VALUE (w004) OVER (ORDER BY w003,w008) 
       + FIRST_VALUE (w005) OVER (ORDER BY w003,w008)
       - SUM (w004+w005) OVER (ORDER BY w003,w008),
       COUNT(*) OVER (ORDER BY w003,w008)
       FROM wxjkk
       WHERE TRIM(h001)=rtrim(nh001) 
       AND p011 IS NOT NULL
       ORDER BY w003,w008;
---------------------------------------------------------------------------------------------------------------------------------------------.................

Answer 6

................
OPEN ncur FOR
SELECT  '' AS w003,
nh013 AS w012,
(SELECT MAX(mc) FROM bm_char WHERE bm = '0023') AS w002,
NVL(SUM(w004),0) AS w004,
NVL(SUM(w005),0) AS w005,
NVL(SUM(z004),0) AS z004,
NVL(SUM(z005),0) AS z005,
NVL(SUM(w004-z004),0) AS bjye,
NVL(SUM(w005-z005),0) AS lxye,
NVL(SUM(w004-z004+w005-z005),0) AS xj,
0 AS xh
FROM temp_fhtz 
WHERE w003 < nbegindate
UNION
SELECT to_char(w003,'yyyy-mm-dd'),w012,w002,w004,w005,z004,z005,bjye,lxye,xj,xh  
FROM temp_fhtz
WHERE w003>= nbegindate 
AND w003<= nenddate 
ORDER BY xh;SELECT COUNT(*)+1 
INTO nrowcount 
FROM temp_fhtz
WHERE w003>= nbegindate 
AND w003<= nenddate 
ORDER BY xh;nret:=0;
return;
END;

Answer 7

这句话的性能如何？
SELECT w003,rtrim(w012),rtrim(w002),w004,w005,0,0,
SUM (w004) OVER (ORDER BY w003,w008) AS nbjye,
SUM (w005) OVER (ORDER BY w003,w008) AS nlxye,
SUM (w004+w005) OVER (ORDER BY w003,w008),
COUNT(*) OVER (ORDER BY w003,w008)
FROM wxjkk
WHERE TRIM(h001)=rtrim(nh001) 
AND p011 IS NOT NULL
ORDER BY w003,w008

Answer 8

如果写入temp_fhtz的数据不多，不要用/*+ append */。如果必须要用/*+ append */，那么在开始前要用动态sql来truncate temp_fhtz。