Oracle存储过程优化小实践

柯2208

近日，接触到ORACLE 存储过程的优化问题，业务过程非常简单。该存储过程的主要DML操作就是UPDATE，需要根据某个条件对数据表的某个字段进行更新，其中使用到游标。之后写了个测试的例子，以下具体介绍。
　　测试环境：
　　OS：WINDOWS XP +Intel(R) 2CPU(1.60GHz) + 1GB
　　数据库： ORACLE10g
　　测试数据：10万行
　　存储过程代码：

CREATEORREPLACEPROCEDUREprc_update_0 　　IS 　　TYPEtab_idISTABLEOFROWID; 　　CURSORcur_skyISSELECTROWIDFROMTBL_USER_AUTHWHEREIMSIISNOTNULL; 　　BEGIN 　　OPENcur_sky; 　　FETCHcur_skyBULKCOLLECTINTOv_rowid; 　　FORALLiIN1..v_rowid.COUNT 　　UPDATETBL_USER_AUTHSETMPXYZDATAE='nsofsofmslfd'WHEREROWID=v_rowid(i); 　　COMMIT; 　　CLOSEcur_sky; 　　EXCEPTION 　　WHENothersTHEN 　　ROLLBACK; 　　DBMS_OUTPUT.PUT_LINE('Updatefailed!'); 　　END; 　　/ 　　CREATEORREPLACEPROCEDUREprc_update_1 　　IS 　　v_rowidROWID; 　　CURSORcur_skyISSELECTROWIDFROMTBL_USER_AUTHWHEREIMSIISNOTNULL; 　　BEGIN 　　OPENcur_sky; 　　LOOP 　　FETCHcur_skyINTOv_rowid; 　　EXITWHENcur_sky%NOTFOUND; 　　UPDATETBL_USER_AUTHSETMPXYZDATAE='nsofsofmslfd'WHEREROWID=v_rowid; 　　ENDLOOP; 　　COMMIT; 　　CLOSEcur_sky; 　　EXCEPTION 　　WHENothersTHEN 　　ROLLBACK; 　　DBMS_OUTPUT.PUT_LINE('Updatefailed!'); 　　END; 　　/

　　测试结果：
　　运行次数
　　时间(s) 1 2 3 4 5 6 7 8 9 10 avg
　　prc_update_0 5 4 4 7 4 4 8 8 4 8 5.6
　　prc_update_1 9 11 9 10 13 9 9 9 11 9 9.9
　　从以上结果可以看出：
　　对于游标的循环，BULK COLLECT对性能的改善有很大的提高。实验数据只有10万行，当数据量达到千万级的时候，运行时间的差距就非常明显。