Oracle儲存過程優化小實踐

47328983發表於2011-04-02
原文網址 : http://blog.itpub.net/241379/viewspace-691542/
Oracle儲存過程優化

【TechTarget中國原創】近日，接觸到ORACLE 儲存過程的優化問題，業務過程非常簡單。該儲存過程的主要DML操作就是UPDATE，需要根據某個條件對資料表的某個欄位進行更新，其中使用到遊標。之後寫了個測試的例子，以下具體介紹。

　　測試環境：

　　OS：WINDOWS XP +Intel(R) 2CPU(1.60GHz) + 1GB

　　資料庫： ORACLE10g

　　測試資料：10萬行

　　儲存過程程式碼：

　　CREATE OR REPLACE PROCEDURE prc_update_0
　　IS
　　TYPE tab_id IS TABLE OF ROWID;
　　CURSOR cur_sky IS SELECT ROWID FROM TBL_USER_AUTH WHERE IMSI IS NOT NULL;
　　BEGIN
　　OPEN cur_sky ;
　　FETCH cur_sky BULK COLLECT INTO v_rowid ;
　　FORALL i IN 1..v_rowid.COUNT
　　UPDATE TBL_USER_AUTH SET MPXYZDATAE='nsofsofmslfd' WHERE ROWID = v_rowid(i);
　　COMMIT;
　　CLOSE cur_sky;
　　EXCEPTION
　　WHEN others THEN
　　ROLLBACK;
　　DBMS_OUTPUT.PUT_LINE('Update failed !');
　　END;
　　/
　　CREATE OR REPLACE PROCEDURE prc_update_1
　　IS
　　v_rowid ROWID;
　　CURSOR cur_sky IS SELECT ROWID FROM TBL_USER_AUTH WHERE IMSI IS NOT NULL;
　　BEGIN
　　OPEN cur_sky ;
　　LOOP
　　FETCH cur_sky INTO v_rowid ;
　　EXIT WHEN cur_sky%NOTFOUND;
　　UPDATE TBL_USER_AUTH SET MPXYZDATAE='nsofsofmslfd' WHERE ROWID = v_rowid;
　　END LOOP;
　　COMMIT;
　　CLOSE cur_sky;
　　EXCEPTION
　　WHEN others THEN
　　ROLLBACK;
　　DBMS_OUTPUT.PUT_LINE('Update failed !');
　　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萬行，當資料量達到千萬級的時候，執行時間的差距就非常明顯。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/241379/viewspace-691542/，如需轉載，請註明出處，否則將追究法律責任。

相關文章