磁碟排序對Oracle資料庫效能的影響
當建立同Oracle會話時,會在伺服器記憶體中劃分出一個專門用來排序的區域,從而為會話提供排序空間。但是,這個排序空間畢竟有限,若記錄數量超過這個排序空間的話,就需要進行磁碟排序。但是,我們都知道,磁碟排序的執行速度要比記憶體排序的執行速度慢1400倍。而且,磁碟排序會消耗臨時表空間的資源,並且可能影響到正在進行的其他SQL排序,因為Oracle必須為臨時表空間中的資料塊分配緩衝池。而且,過多的磁碟排序會導致空閒緩衝等待,以及將執行其他任務的資料塊從緩衝池中分頁出去。對於資料庫管理員來說,在記憶體中進行排序總是比磁碟排序更受歡迎。所以說,磁碟排序是影響Oracle資料庫效能的罪魁禍首。在資料庫最佳化的時候,我們應該想法設法降低資料庫的磁碟排序。為此,筆者有如下建議。
一、合理設定Sort_area_size引數
雖然說Oracle10G以後的資料庫會自動對記憶體進行管理。但是,在一些效能要求比較高或者排序頻率比較高的資料庫中,仍然有必要對一些影響記憶體分配的引數進行調整。其中,最重要的一個引數就是Sort_area_size。
Oracle資料庫會為所有的連結Oracle會話分配Sort_area_size這個引數。所以,對於擁有大量使用者的資料庫來說,如果增加這個引數的值,會讓磁碟排序的機率明顯降低,不過資料庫也要為此付出這個代價,很容易導致記憶體過載。但是,如果這個引數的值設定的過低的話,又會導致過多的磁碟排序。所以,這個引數並不是越大越好。因為這個引數如果設定的過大的話,其帶來的效能收益反而會降低。因為為了提高有限幾個查詢的速度,可能會浪費大量的記憶體。這無疑是我們資料庫管理員不希望看到的。
在實際工作中,我們往往需要在兩者之間進行一個均衡。設定一個合理的引數,儘量讓資料庫減少磁碟排序的機率,同時也不能使得伺服器記憶體過載。為此筆者有一個建議。資料庫管理員應該每隔一段時間增加這個引數的值,並使用Statspack工具定時監控記憶體排序與磁碟排序的資料。在起初進行調整的時候最好每個小時查詢一次。透過這些資料,我們就可以得到一個合理的引數值,在兩這之間取得一個均衡。
前期調整完成後,在後期仍然需要進行監控。因為後期隨著企業應用的改變,這個引數仍然需要根據實際情況進行調整,以提高資料庫的效能
二、儘量減少不必要的磁碟排序
在某些情況下,儘管資料庫管理員沒有直接透過Order By等語句對資料庫記錄進行排序,可是Oracle資料庫伺服器仍然會對查詢結果進行排序。因為這些語句需要起作用,必須要先對資料進行排序。所以,他們往往帶有隱性的排序功能。
我們在資料庫維護或者前臺應用程式設計的時候,要儘量的減少這種不必要的排序。如Distinct關鍵字,它的作用就是取消重複的記錄。但是,要實現這個目的的話,則資料庫必須要先對記錄進行排序,然後才能夠去除重複的記錄內容。故在設計的時候,儘量要避免使用Distinct關鍵字。其實,筆者在工作中,經常會碰到這種情況,某些記錄其實不存在重複記錄,但是程式開發人員為了保障資料的準確性,就在SQL語句中加入了Distinct關鍵字,從而造成了不必要的排序。
另外,在其他一些情況下,也會導致不必要的排序。如排序合併連線,也會導致不必要的排序。故無論何時,只要使用了排序合併連線,就會執行排序已連線關鍵值。故在資料庫與應用程式設計的時候,要儘量避免排序合併連線。其實,在許多情況下,巢狀迴圈連線反而使更好的選擇。因為這個巢狀迴圈連線,它更加有效而且不會導致不必要的排序以及不比要的全表掃描。
其次,有時候缺失索引也會導致一些並不要的排序。故資料庫管理員在平時的工作中,要儘量的減少這些不必要的排序,以讓寶貴的記憶體資源交給更重要的任務來適用,提高Oracle資料庫效能。
三、利用Statspack工具監控排序活動
Statspack工具是一款提高Oracle資料庫效能的很好的輔助工具。因為它可以幫助我們收集很多有用的資訊。故我們資料庫管理員也可以利Statspack工具對資料庫中的排序活動進行監控。
對於一個有經驗的資料庫管理員來說,對記憶體排序和磁碟排序保持必要的排需是非常必要的。因為我們無法左右使用者的行為;而使用者的行為又會有所調整。使用者在調整的過程中,有可能又會增加額外的磁碟排序。當然,也有可能磁碟排序的機率會減少。但是,通常情況下,隨著使用者交易資料的增加,這個磁碟排序的機率在理論上仍然是往上爬的。而實際上也是往上升的,只是這個升的速度沒有理論上那麼快而已。這主要是看資料庫管理員如何進行管理了。
根據筆者的瞭解,企業使用者的操作往往會有一個週期性的變化,如按年或者按月進行週期性的變化。資料庫管理員應該養成一個好習慣,每個月利用Statspack工具定期的對資料庫進行監控。特別是要監控資料庫的排序情況。Statspack工具還有額外的一個功能,就是自動監測與警告功能。也就是說,可以讓Statspack這個工具在磁碟排序數量超過一個預設定的閥值時,自動給資料庫管理員傳送一個警告,如透過郵件形式傳送給管理員等等。筆者透過監控發現,每到月底與月初的時候,磁碟排序的數量會大大的增加。這主要是因為在月底的時候,使用者會對當月的交易資料進行統計。所以當月底月初的時候,由於交易記錄比較多,所以,會有比較多的磁碟排序發生。在這種情況下,資料庫管理員有必要對相關引數進行調整。不過這個調整是暫時的調整,等到這個週期過去後,仍然要把引數調回來。只有如此,資料庫的整體效能才會有所保障。即不會因為記憶體過載而降低資料庫效能;也不會因為磁碟排序而給資料庫造成額外的負擔。
所以,雖然排序是SQL語句執行中很微小的一個部分,但是其對資料庫效能影響卻比較大,而且也是非常顯著的。可惜的是,排序是SQL調整中往往被忽視的地方。在Oracle資料庫中,排序對使用者來說是透明的。也就是說,排序對使用者很少有所限制,使用者可以根據自己的需要來對資料進行隨意地排序。但是,使用者並不知道,什麼樣的操作會降低資料庫的效能。故如何降低使用者的不合理操作而產生額外的排序,甚至是磁碟排序,這是資料庫管理員在平時工作中必須要考慮到的一個問題。透過以上三個方法,或許可以給資料庫管理員找到一些解決問題的思路。相信透過以上方法,可以最大程度的減少磁碟排序的發生,不再讓磁碟排序成為影響資料庫效能的罪魁禍首。
原文出處:來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29119536/viewspace-1246578/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 磁碟排序對Oracle資料庫效能的影響PT排序Oracle資料庫
- 容器化對資料庫的效能有影響嗎?資料庫
- 關於資料庫開啟大頁對效能的影響資料庫
- 修改系統時間對oracle資料庫的影響Oracle資料庫
- ORM框架和資料庫對系統效能影響的比較ORM框架資料庫
- [zt] segment size(strip size)對磁碟陣列效能的影響陣列
- MySQL資料庫的效能的影響分析及其優化MySql資料庫優化
- 變更OS時間對資料庫的影響資料庫
- 執行緒數目對資料庫的影響執行緒資料庫
- Oracle安裝過程對資料庫級語言設定的影響Oracle資料庫
- 聊聊虛擬化和容器對資料庫的影響資料庫
- 影響MongoDB資料庫效能的幾個重要因素MongoDB資料庫
- 載入資料順序對HASH SORT CLUSTER效能影響
- Backup And Recovery User's Guide-使用閃回資料庫-監控閃回資料庫對效能的影響GUIIDE資料庫
- 哪些方面會影響伺服器資料庫效能伺服器資料庫
- Oracle 物化檢視快速重新整理對效能的影響Oracle
- 影響資料庫效能與穩定性的幾個重要引數資料庫
- 軟體效能的設計(三)資料型別對軟體效能的影響 (轉)資料型別
- JAVA 異常對於效能的影響Java
- NVM作為主存上對資料庫管理系統的影響資料庫
- 【Mysql】master_info 與 relay_info對資料庫的影響MySqlAST資料庫
- Oracle 12.2.0.1.0 PDB丟失資料檔案對CDB的影響Oracle
- 【效能】Oracle表並行對邏輯遷移後查詢效能的影響Oracle並行
- 索引及排序對執行計劃的影響索引排序
- 表資料的儲存對索引的影響索引
- Oracle 11g 測試停庫對job的影響Oracle
- oracle 索引升降序及排序條件 對查詢計劃的影響Oracle索引排序
- 【Oracle】-【COMMIT對索引的影響】-從trace看COMMIT對索引的影響OracleMIT索引
- 主庫resetlogs對備庫的影響
- DB2 HADR對效能的影響DB2
- InnoDB 隔離模式對 MySQL 效能的影響模式MySql
- SYSAUX表空間滿對資料庫的影響以及解決措施UX資料庫
- 不停機 data guard 注意事項 (重建orapw對資料庫的影響)資料庫
- 驗證資料壓縮對DML的影響
- Oracle中rownum對錶的掃描方式效能上的影響深入探究Oracle
- Oracle資料庫效能Oracle資料庫
- 【知識分享】哪些方面會影響伺服器資料庫效能伺服器資料庫
- 分支對程式碼效能的影響和優化優化