优化 pl/sql 执行时间

Question

>我有一个需要很长时间才能执行的pl/sql过程(Oracle 11g((3 行为 195.000 小时(。

所以目标是加快速度。

代码使用bulk collect和for all来加速dmls，但是有一些计算是在大量收集的数据上完成的。这些都是在经典for loop内完成的。这个 for 循环还访问其他一些表(执行选择(，以便执行所需的计算。我认为这部分是减慢一切的部分。

考虑下面的代码(它是真实代码的修改和精简版本 - 只是为了给你一个正在发生的事情的要点(：

procedure long_runnig_task is 
         TYPE my_record is RECORD(key1  number, key2  number, key3 number , 
         key4 number, p1 number ,p2 number, place_holder1  number, 
         place_holder2  number,place_holder3  number );
         TYPE my_record_table  IS TABLE OF my_record;
         l_data my_record_table;
 cursor c is
          select key1, key2 , key3 ,key4 ,
          (select param from paramtable where param_id=1) p1,
          (select param from paramtable where param_id=2) p2,
          0 place_holder1 ,0 place_holder2, 0 place_holder3 from mytable where myflag=4;
begin
     open c;
        loop
            begin
            fetch c bulk collect into l_data limit 1000;
            savepoint mysp;
            FOR indx IN 1 .. l_data.COUNT
           loop 
              --computations per record 
              select max(amount) into myValue1 from table3 where 
              key1=l_data(indx).kay1 and 
              key2=l_data(indx).kay2 and key3=l_data(indx).kay3 and key4=l_data(indx).kay4;
              select amount into myValue2 from table4 where key1=l_data(indx).kay1 
              and 
              key2=l_data(indx).kay2 and key3=l_data(indx).kay3 and key4=l_data(indx).kay4;
              select amount into myValue3 from table5 where key1=l_data(indx).kay1 
              and 
              key2=l_data(indx).key2 and key3=l_data(indx).key3 and key4=l_data(indx).key4;
              l_data(indx).place_holder1 := myValue1;
              l_data(indx).place_holder2 := someFunction(myValue2,l_data(indx).p1);
              l_data(indx).palce_holder3 := myValue3*l_data(indx).p2;
       end loop;
  forall indx IN 1 .. l_data.COUNT 
     update table6 set v= l_data(indx).place_holder1 where  key1=l_data(indx).key1 
     and 
     key2=l_data(indx).key2 and key3=l_data(indx).key3 and key4=l_data(indx).key4;
  forall indx IN 1 .. l_data.COUNT 
     insert into table7(col1,col2,col3) values (l_data(indx).place_holder3,sysdate,l_data(indx).place_holder2/10); 
 exception when others then
            rollback to mysp;
            raise;
 end; 
exit when c%notfound;
end loop;

exception when others then
     rollback;
    raise; 
    end;

欢迎就如何优化上述性能提出任何建议。

谢谢。

Answer 1

这可以通过两个 sql 语句来完成：

BEGIN
  MERGE INTO table6 tgt
    USING (SELECT key1,
                  key2,
                  key3,
                  key4,
                  (SELECT MAX(amount)
                   FROM   table3 t3
                   WHERE  t3.key1 = mt.key1
                   AND    t3.key2 = mt.key2
                   AND    t3.key3 = mt.key3
                   AND    t3.key4 = mt.key4) place_holder1
           FROM   mytable mt
           WHERE  myflag = 4) src
      ON (tgt.key1 = src.key1
         AND tgt.key2 = src.key2
         AND tgt.key3 = src.key3
         AND tgt.key4 = src.key4)
  WHEN MATCHED THEN
    UPDATE SET tgt.v = src.place_holder1;
  INSERT INTO table7
    (col1,
     col2,
     col3)
  SELECT place_holder3 * mt.p2 val3,
         SYSDATE dt,
         somefunction(place_holder2, mt.p1) val2,
  FROM   (SELECT (SELECT param
                  FROM   paramtable
                  WHERE  param_id = 1) p1,
                 (SELECT param
                  FROM   paramtable
                  WHERE  param_id = 2) p2,
                 (SELECT amount
                         FROM   table4
                         WHERE  t4.key1 = mt.key1
                         AND    t4.key2 = mt.key2
                         AND    t4.key3 = mt.key3
                         AND    t4.key4 = mt.key4) place_holder2,
                        (SELECT amount
                         FROM   table5
                         WHERE  t5.key1 = mt.key1
                         AND    t5.key2 = mt.key2
                         AND    t5.key3 = mt.key3
                         AND    t5.key4 = mt.key4)/10 place_holder3
          FROM   mytable mt
          WHERE  myflag = 4);
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

注意：这确实假设对于 mytable 中的每个键 1、键 2、键 3 和键 4，表 3、表4 和表5 中都有一个对应的行。通过执行标量子查询，如果这些表中不存在行，则将返回 null，而不是在当前代码中生成错误 (no_data_found(。如果发生这种情况，您必须修改上述过程以生成错误。

我这样做的方法是获取游标 sql，然后将后续的 sql 语句作为标量子查询添加到游标的 sql 中。

然后我注意到插入和更新使用不同的值(更新使用place_holder1值，插入place_holder2和place_holder3值(适当修改(。然后，只需将它们转换为 MERGE 语句以进行更新，以及插入语句即可。