[原創]Oracle資料庫效能最佳化的方案[有刪節]

本方案在實際的問題處理中有著不錯的效果，只使用了方案中的部分措施，已經提高了幾百倍的效能。特別是一個關於三張百萬資料的表聯集查詢並經過業務運算後，再單個事務提交130萬行資料寫入的預存程序，由原來的幾小時無法執行完成，變成119秒完成。因此這裡給初學者分享經驗的同時，希望得到資料庫高手們的進一步指點。

1      問題
XXXXXXX系統是一個業務資料處理集中，單事務中有很多百萬層級大批量的資料操讀寫和查詢操作。

目前效能比較低，部分運算步驟需要長時間運行。超過30分鐘以上，甚至個別過程運算1.5小時無結果。

 

2      目的
本文試圖做一些特定分析，並針對性提出效能的最佳化和改進方案；

 

3      分析
3.1     分析方法
l         使用效能檢測日誌

l         使用Oracle管理主控台的鎖查看機制

l         SQL狀態跟蹤

l         資料庫scheme瀏覽

 

3.2     分析結果
l         目前整個運算邏輯已經全部使用Oracle預存程序。

l         使用顯式遊標。並盡量使用了大量操作方式提高效能。

l         資料庫索引建立不夠合理。

l         目前一期的資料就會在多個單表刪除或者插入26萬到105萬的資料

l         沒有資料曆史表，也沒有後台自動運行機制。

 

4      改進措施
4.1     優先使用的方法
4.1.1              索引的建立
l         字典類表的資料量比較小，查詢中使用較多，需要把查詢時相關的欄位全部建立索引。適合資料量小於5000以下的情況。

l         經過測試發現，實際啟動並執行資料多為字串型。並且字串的前半部分內容幾乎一樣，後半部分差異較大。因此推薦索引類型為——反向B樹索引。

l         大資料量的表因為往往在一次事務中，提交百萬層級的資料。建議建立在insert操作前DROP索引，insert後建立新的索引。雖然索引的建立和刪除也消耗時間。但中間的空白塊的消除，會對效能提高有好處。

【備忘：大資料量的表建立索引一定要盡量少和高效】

 

4.1.2              物化視圖的使用
在資料需要變更時再REFRESH視圖，節省每次都動態查詢帶來的額外開銷。

【備忘：可以視情況，為物化視圖建立索引，進一步提高檢索速度】

 

4.1.3              暫存資料表的使用
和物化視圖類似，但有幾個特別的特點。

l         是減少REDO日誌的開銷。

l         是不需要實體儲存體到硬碟上，減少磁碟操作時間。

l         如果是需要每次重建資料的場合，不需要刪除資料的時間開銷。

【關聯措施：參見4.2.1】

 

4.1.4              經濟的使用欄位大小設計。
避免資料庫空間浪費帶來的效能開銷。

 

4.1.5              避免不必要的欄位函數處理後的關聯查詢
注意建立函數索引或者取消函數處理後的關聯。避免全表掃描。

 

4.2     資料庫伺服器的改進措施
4.2.1              建立使用9.20以上版本的Oracle資料庫
因為這樣可以較好的支援暫存資料表，使用暫存資料表大幅度減少REDO日誌的開銷。

 

4.2.2              使用RAID策略，成倍加快硬碟IO操作
可以同時分寫不同的資料庫硬碟，並行操作。

 

4.2.3              將大的資料表分散建立在多個硬碟上，提高並行能力
分區建表。

 

4.2.4              建立單獨的資料索引空間
提高索引大資料量時候的效能。

 

4.3     進一步改進的方法
4.3.1              曆史資料數列表的建立，分散單表的資料壓力
隨著系統使用時間的發展，資料量還會進一步加大。不分表將無法支援。另外業務特性決定了每期的關聯不十分緊密，適合建立曆史資料表。

由於資料庫設計的改動將導致處理邏輯的變化，帶來開發與測試的一定工作量。

【提示：曆史表的一個附加的好處是，遇到資料整期清除的時候，可以使用TRUNCATE，效能將會比目前使用的DELETE提高許多倍，而且資料量越大，索引越多，效果差別越大】

 

4.3.2              資料表建議重新以效能為優先重新設計
目前全是INSERT、SELECT、DELETE，應該合理使用UPDATE。

 

4.3.3              建立後台運行機制
做後台運行機制，並給出運行大致進度比例。

 

4.3.4              單無意義欄位標識代替聯合欄位標識
發現有多個大表都使用六到八個欄位作為資料唯一的標識，建議用單個無意義欄位代替。這個涉及開發測試工作，所以放在進一步的工作建議裡。

 

5      最後補充
從系統長遠使用考慮，建議相關人員能逐步完成所有的最佳化工作。