常用的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表空間和數據文件的關系，否則拒絕申請
轉載于:https://www.cnblogs.com/tianlesoftware/archive/2011/07/19/3609638.html
總結
以上是生活随笔為你收集整理的常用的PL/SQL开发原则 by dbsanke的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 用sp_addlinkedserver建
- 下一篇： 从小屏到大屏，触控技术的现在与未来