Oracle效能調整之--DML語句效能調整

SQL語句分為五類:
1.SELECT語句(SELECT)
2.DML語句(UPDATE,DELETE,INSERT)
3.DDL語句(CREATE DROP ALTER)
4.DCL語句(GRANT REVOKE)
5.TCL語句(COMMIT ROLLBACK)
        這裡需要強調的是SELECT語句並不屬於DML語句的範疇。由於業務系統中往往書寫的SELECT語句較為複雜，而DML語句一般較為簡單，之前我在工作中的調優工作大部分是指的SELECT語句的調優，而忽略了對DML語句的調優。雖然DML語句本身非常的簡單，但是當併發增大、DML操作表的資料量較大就會導致效能的急劇下降。
        執行DML語句會用到UNDO表空間和線上Redo日誌檔案，如果執行大量的DML併發操作，這兩部分所在磁碟的IO會變得非常的大，當磁碟的IO接近100%的時候，DML操作就會變得非常的慢，這個時候系統可能會處在非常緩慢的狀態。
        對於作業系統而言，如果安裝系統所在的磁碟IO被撐滿，例如SWAP頻繁的發生交換導致IO一直處於100%左右的狀態，這個時候連線系統是非常慢的，CPU會大量的消耗用於等待IO，執行在作業系統上的資料庫或應用也會隨著作業系統IO的滿負荷執行而變得非常的慢。同樣的道理，執行DML語句會消耗UNDO表空間和線上Redo日誌所在磁碟的IO，如果這兩部分磁碟IO接近100%，那麼整個資料庫執行DML語句會變得非常的緩慢。如果UNDO和REDO所在磁碟和作業系統在一個磁碟系統中，作業系統也會變得非常慢，如果跟資料檔案在一個磁碟系統中，SELECT語句也會變慢。從這個角度來說，UNDO和Redo應該放在與資料庫檔案不同的磁碟系統上，並有獨立的磁碟IO，相互之間不應該受到影響。
        對於大表的DELETE或UPDATE操作要非常的謹慎,在業務執行期間儘量不要對大表執行大資料量的UPDATE和DELETE操作,對大表打資料量的DELETE操作可能會導致UNDO所在磁碟系統IO非常的高,也可能會導致REDO所在磁碟的IO非常的高,這樣會阻止其他會話或事務的DML語句的執行。並且即時你終止了客戶端的執行相關的操作依然在後臺程式，即便KILL SESSION也是沒有效果的，最終只有結束作業系統程式。對於這樣的情況我們應該採用分割槽表來解決大表資料的刪除和更新問題，表分割槽能為資料的管理帶來巨大的便利。並且儘量要在系統使用最不頻繁的時候來執行大資料量的更新或刪除操作。

綜上所述，對於DML語句的調優我們有如下的經驗：
1.UNDO和Redo應該放在最快的磁碟系統上，並且應該與其它資料儲存處於不同的磁碟系統，最好它們之間也處於不同的磁碟系統，避免相互影響。可以透過為UNDO表空間建立屬於不同磁碟系統的資料檔案來分擔UNDO表空間承受的IO壓力。線上Redo日誌檔案可以放在經過條帶化的儲存系統中，提高IO效能。
2.UNDO表空間和線上Redo日誌應該足夠的大，減少由於空間的問題導致的額外磁碟IO操作。
3.對於大資料量的表應該作為分割槽表來處理，便於UPDATE和DELETE執行的效率。
4.應當避免在高峰期間對大表進行打資料量的DELETE和UPDATE操作。

        以上的規則能夠有效地提高併發情況下DML語句執行的效率，減少DML語句的執行對其它操作帶來的影響。同時為了提高排序等工作的效率同樣應該設定足夠大的臨時表空間大小。
UNDO和Redo就好比作業系統的SWAP一樣，它只是個臨時的區域，並不需要太大的空間，但它是非常重要的，衡量UNDO和REDO最重要的指標就是IO效能。所以，對這種不需要太大空間，但對IO要求很高的空間來說，資料庫部署方案尤為重要，應該根據系統實際的情況來進行考慮。