存储过程优化问题

路遥遥兮

各位大侠，存储过程如下：
create or replace procedure proc_backup(
tablename in  varchar2,
workdt in varchar2,
keyvalue in varchar2,
tablenamehis in varchar2,
resul out number,
increment out number
) is
increm number;
w_sql varchar2(32000) :=
    'declare
    print varchar2(50);
    counter int ;
    increm number  ;
    w_rec '||tablename||'%rowtype;
    my_rowid UROWID;
    CURSOR c_tbl IS Select * from '||tablename|| '  Where '||WORKDt||' < '''||keyvalue||''' for updat
  /*以下都会删除，插入操作*/
...............................................
execute immediate w_sql using out increm ;
.............................................
有百万条数据，这样运行的速度很慢，请高手多多指教，愚人感觉，动态sql是可以优化，还请多多赐教

Yong Huang

Event? Version? Plan?

路遥遥兮

oracle 10g

路遥遥兮

LOOP
       counter :=0;
       Open c_tbl;
       FOR counter IN 0..8000 LOOP
            begin                  
                   fetch c_tbl into w_rec;
                   if c_tbl%NOTFOUND then
                   raise NO_DATA_FOUND;
                   end if;
                   Insert into '||tablenamehis||'  values w_rec;
                   increm := increm+1;
                   Delete from '||tablename||' where current of c_tbl;
                   exception
                          when DUP_VAL_ON_INDEX then         
                          NULL;
                        
                          when others then
                          raise;
            end;         
       END LOOP;
       CLOSE c_tbl;
       commit;
    END LOOP;


循环会消耗plsql与sql的切换时间，大家看看，怎样少用循环，或者不要，，，，

路遥遥兮

自己结吧，采用每次取N条，然后 块插入，块删除，这些操作放在一个动态sql，然后循环执行这个语句，速度可以明显提高

蜗Niubility

对于大批量数据订正，都是采用分批提交来提高性能。
在已经使用INDEX进行更新或者删除的时候，瓶颈一般在REDO上面，通过批量把小事务拼装为大事务减少对REDO的压力。
10GR2还可以通过修改COMMIT_WRITE参数进一步进行优化

sundog315

能用一条SQL搞定的事情，最好不要自己写循环处理，自己写循环有点类似于强制nest loop，而且增加了很多运行时切换

若是为了减少对UNDO的影响，倒是可以进行拆分，不过，也可以采用bulk\for all方式进行批量处理，只是要控制好PGA的消耗

路遥遥兮

楼上有道理，一开始的时候，自己写的循环，返现效率很低，换成bulk方式，效率多倍的提高。

Yong Huang

> 通过批量把小事务拼装为大事务减少对REDO的压力

You mean reducing pressure on UNDO?

路遥遥兮

还请高手解释 reducing pressure on UNDO