常用的PL/SQL開發原則 by dbsanke

 

       在微博上看到dbsnake 有一個培訓，因為是在北京舉辦的，過去不。所以問dbsnake 能否把相關的文檔傳我一份。 dbsnake 郵件給我時說有些東西沒有寫到文檔裡。 這個文檔也是dbsnake 對Oracle 的一個經驗的積累的過程，全部寫出來也不實際，所以有機會還是多參加這類培訓，這對技術提高還是很有協助的。在這個文檔裡dbsnake 整理了一個PL/SQL 的開發文檔。 貼過來，共用之。

 

常用的PL/SQL開發原則 bysnake：

       （1）廣泛使用綁定變數，特別是大量繫結，因為這可以有效避免sql的硬解析和PL/SQL引擎和SQL引擎的環境切換！

       （2）廣泛使用UROWID來處理DML語句

       （3）在你的預存程序中謹慎使用DDL語句，因為這可能會破壞你的transaction的連續性，更為嚴重的是可能會阻塞DML操作並可能會導致大量library cache latch爭用並且有可能會導致某些sql執行計畫的改變。

       （4）不要在預存程序裡不應該commit的地方commit，特別是當你的預存程序會被另外一個預存程序調用的時候，你考慮到了你這麼做會破壞調用你的父預存程序的transaction的連續性了嗎？

       （5）注意你面對的資料量，小資料量的處理方法和海量資料的處理方法是不一樣的！

       （6）迴圈的時候要注意清空臨時變數的值

       （7）注意“select into 變數”的問題，使用子begin語句封裝“select into 變數”以避免可能會出現的錯誤，這樣就可以避免要在“select into 變數”之前先執行一下select count(*)

       （8）不要讓oracle執行你的PL/SQL代碼時產生隱式轉換

       （9）在PL/SQL中定義varchar2變數的時候當你在不知道你所定義的變數的長度的時候可以將其定義為varchar2(4000)，這一點都不浪費！

       （10）如果你寫的一組預存程序有邏輯上的關聯，那我建議你要把這些預存程序封裝到一個package裡面

       （11）改正你的PL/SQL代碼裡的所有編譯時間編譯器提示出的warning

       （12）迴圈的時候一定要注意exit，否則就太可怕了！

       （13）處理顯式cursor的時候一定要注意fetch和exit，否則就太可怕了！如下是我處理的一個真實的案例，這個案例中暴露出的問題就很好的說明了處理cursor的時候一定要注意fetch和exit：

       今天下午1點多的時候接到同事的一封信，信裡提到我們的一個10gR2的開發環境連不進去了，報這樣的錯“ORA-00257：archiver error.Connect internalonly,until freed.”。很明顯是因為歸檔進行不下去而導致整個系統被hang住了。

       我上去一看，果然是存放歸檔日誌的目錄滿了。上述開發環境的存放歸檔日誌的目錄大小為10個G，alert log顯示從今天11點半開始到下午1點左右的這段時間系統整整產生了10個G的歸檔日誌，而且還在源源不斷的繼續產生。在繼續產生歸檔日誌的時候因為空白間滿了，所以導致整個系統被hang住。原因知道了，處理過程就很清晰了。

       只保留一個歸檔日誌，然後手工把所有其他的歸檔日誌rm掉，注意這裡不要一下子就把所有的歸檔日誌給rm掉，否則可能導致檔案系統的控制代碼無法釋放，進而這些歸檔日誌佔用的空間也無法釋放。然後run一個ADDM報告，看一下是什麼導致了在這麼短的時間內產生了這麼多的歸檔日誌。從ADDM報告中可以很清晰的看到產生這麼多歸檔日誌的根本原因是因為一個預存程序裡的幾條sql在短時間內執行了1400多萬次的緣故。

       用alter system kill session配合kill -9殺掉這個預存程序所在的session，以阻止它源源不斷的產生歸檔日誌。

       接著我分析了一下上述預存程序，發現根本原因在於在這個預存程序裡開啟了一個cursor，fetch這個cursor中的一條記錄到一個record裡，然後開啟一個迴圈，在迴圈裡run上述的那幾條sql語句。但致命的是在迴圈的末尾並沒有再fetch這個cursor中的另外一條記錄，這就導致了上述迴圈的條件永遠為真，所以上述sql如果不被我中斷的話就會永遠執行下去。這也就解釋了為什麼會在短時間裡產生了大量的歸檔日誌，並且undo tablespace的空間也被消耗殆盡。

後續處理過程這裡省略……

       （14）bulk collect into的時候不要一次collect太多的資料，建議一次collect的資料量在10000條以內，你可以用大量繫結內建的limit子句來限制或者使用rownum來限制

       （15）如果你使用了大量繫結，那為什麼要把時間浪費在寫諸如insertinto tablename(column1,column2,……,column100) values(value1(i),value2(i),……,value100(i))這樣的sql語句上面？如果有可能，就寫一個你自己的預存程序代碼產生器吧，讓它來幫你產生這樣的語句。你應該把精力集中在更有用的方面！

       （16）你希望你的代碼被並發執行嗎？如果你不希望或者你的代碼根本就不能夠被並發執行，那就想一個辦法控制並發吧！在應用程式層面控制就好，比如在update之前先嘗試對該記錄加for update nowait的鎖，或者利用DML語句當前讀的特性來避免並發都是不錯的主意

       （17）不要寫諸如insert into tablename1 select* from tablename2這樣的語句，你考慮到擴充性了嗎？假如以後tablename1或者tablename2增加或減少欄位了呢？

       （18）謹慎使用hint，除非你很清楚你在做什麼。比如說這裡你強制oracle使用了某個索引，假如以後這個索引的名字被改了，由此帶來的執行計畫的變更你怎麼辦？你考慮到這種情況了嗎？

       （19）注意關聯更新遺失資料的問題，update語句如果沒有指定where條件那就是對所有的資料做update操作，這個就太恐怖了！

       （20）用好暫存資料表，有時候暫存資料表很有用！特別是在根據一堆複雜條件去更新海量資料的時候

       （21）盡量避免在預存程序裡使用遞規！不是說不能用遞規（遞規在某些特定的情況下很有用），而是說在用遞規的時候一定要避免無限遞規的情況！

       （22）寫好你的PL/SQL代碼裡的注釋，這個很重要！不寫注釋並不代表你很厲害！

 

 

 

 

 

 

-------------------------------------------------------------------------------------------------------

Blog： http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群：62697716(滿);   DBA2 群：62697977(滿)  DBA3 群：62697850(滿)  

DBA 超級群：63306533(滿);  DBA4 群： 83829929  DBA5群： 142216823   

DBA6 群：158654907  聊天 群：40132017   聊天2群：69087192

--加群需要在備忘說明Oracle資料表空間和資料檔案的關係，否則拒絕申請