Oracle數據庫業務優化心得

　　公司開發的產品基本上都使用ORACLE數據庫，而且業務系統中存儲和使用的數據量很大，使用業務系統的用戶也很多。在系統忙時，大約會有一千個用戶同事訪問數據庫系統，因此經常會有用戶抱怨系統慢，點擊查詢后，系統出現假死機現象，后臺運行ORACLE系統的小型機的IDLE值偏低(甚至出現IDLE為0的情況，這時用戶會驚呼數據庫系統會癱掉，雖然小型型機在IDLE值為0的情況下也不一定會癱機，但這無疑會增加用戶的擔憂)，系統運行在一個不安全的狀態等等。

　　對于我現在所做的ORACLE優化，其實還停留在SQL優化的層次(以前我的前輩曾給我說關于數據庫優化的三個層次:一是針對SQL的優化，如使用正確是索引，使用ORACLE提示等;二是針對數據庫對象的優化，如增加索引，微調表結構等;三針對業務的優化，需要更改業務邏輯或者表結果，此類優化一般代價比較大，一般很少針對正在運行的系統做類似的操作)。

　　公司的客戶很多，大多數情況會在公司遠程處理客戶的數據庫問題。作為問題的定位者，一定要搞清楚實際運行的數據庫系統到底出現了什么問題?，F場的維護人員和用戶最喜歡使用的形容詞是:系統很慢;數據庫鎖表了等等。用戶向外面傳達的信心往往是非常模糊的，在開始接觸這個問題時候起，我們就要引導用戶去幫外面了解系統真實的情況。有幾個東西是一定要看看的，一個是ORACLE系統的警告日志文件，在系統運行的有問題或者是忙時的幾份STATSPACK報告(一般間隔時間10到15分鐘)，UNIX下SAR命令的輸入結果(該命令可以按既定的時間間隔收集小型機系統CPU的使用情況)。通過這三個從現場收集的結果，我們基本可以了解現場數據庫的運行情況。

　　其中ORACLE系統的警告日志文件能讓我們了解ORACLE系統運行有沒有一些重大的問題。

　　STATSPACK報告中概括了數據庫系統的運行基本情況，其中關于如何解讀報告可以寫一本書了，不過我們首先要關注的是報告中有一段“Top 5 Timed Events”，這一段描述了當前ORACLE主要的等待事件是什么(關于ORACLE等待事件的概念可以參考相關的資料)。

　　SAR命令的輸入我們要關注三個輸出的分布情況USR、WIO、IDLE。其中SYS+USR+WIO+IDLE應該等于100%，USR占的比例高，一般說明SQL語句執行效率有問題，這種問題一般是由于索引選擇性不高、表連接順序和方式不對等等;WIO高一般說明SQL語句頻繁進行I/O操作。對于具體的問題，則需要對具體的SQL語句進行分析，在分析過程中，閱讀執行計劃是我們的一個重要的工具。

　　在對ORACLE系統的整體情況有了一定的了解之后，下一步需要分析的就是系統運行過程效率不高的SQL，這是對業務優化的一個起點。如果這時不能夠在實際系統中操作，了解SQL的運行過程是一個比較費時的過程的。不管怎么樣，對于我們懷疑有問題的SQL，首先要閱讀的就是該SQL的在實際系統中的執行計劃，語句涉及到的表的數據量，訪問表使用索引的選擇性如何，表連接的順序，多表之間的關聯關系等等。

　　對于ORACLE應用系統的優化，大方向上有一個順序，首先考慮優化業務系統、再考慮優化ORACLE系統本身的參數(如內存分配等)，再考慮操作系統本身的優化;在優化業務系統中，首先是首先相關的SQL，以SQL入手分析表是否缺少索引，表連接順序是否正確，使用的索引是否正確等，然后再考慮調整表結構，調整業務邏輯等等。因此，SQL語句是我們對一個ORACLE業務系統進行優化的敲門磚。

　　對于SQL優化，前面提到了，ORACLE的執行計劃是我們必須使用的工具。本來按ORACLE系統本身提供的方法獲取執行計劃是一件非常麻煩的事情，不過現在的可視化工具比如PL/SQL DEVELOP或者TOAD等都給我們提供了非常方便的手段來獲取SQL語句的執行計劃，不過我認為ORACLE本身提供的方法還是有必要會的，特別是在遠程處理問題的時候(我也不會，一定要學習一下)。

　　獲取有性能問題的SQL語句，我們主要有兩個途徑，一個是通過STATSPACK報告。報告中有兩節是我們需要重點關注的:《SQL ordered by Gets for》和《SQL ordered by Reads for DB》，這兩節中分別按語句讀取內存數據庫塊和讀取的物理數據庫塊(數據庫塊是指ORACLE的塊大小，一般是操作系統最小塊的整數倍)的數量倒序排列，如果其中的語句不全(太長)，可通過HASH_VALUE值到ORACLE的動態視圖V$SQLTEXT中獲取該SQL的全部語句。第二個是通過ORACLE系統的動態視圖，V$SQL，該視圖記錄了每個SQL語句的執行次數，物理讀和內存讀、執行時間等等很多SQL語句的執行信息，可以通過如下語句選擇一下物理讀和內存讀較高的語句:

　　這個語句查詢出來的SQL可能也不全，也可以通過HASH_VALUE在V$SQLTEXT中找到其全部的SQL。

　　對于找到的SQL語句我們可以逐一分析其執行計劃，結合涉及到的表的數據量，我們可以估算或者測試該語句的執行效率，分析表WHERE條件中涉及的字段(術語叫做謂詞)，該字段數據分布如何，選擇性是否好，是否有索引。這是一個非常繁雜和瑣碎的工作，但從這些瑣碎的工作中，我們能發現那些SQL執行時選擇的索引不對，哪些表缺少相應的索引導致了全表掃描，哪些語句條件不夠導致對分區表進行了全表掃描?？傊?，對于一個給定的SQL，我們結合其表數據量的大小和分布，SQL中使用的查詢條件，能夠找到一個性能最優的執行方式，通過調整索引、使用ORACLE提示，使ORACLE系統按照最優的方式來執行SQL。如何去分析和確定ORACLE的執行方式，一個最普遍的原則就是盡量根據其謂詞(查詢條件)，使用選擇性最好的索引(當然，對于一些小表，可以考慮使用全表掃描的方式性能會更好)。對于SQL的執行方式，需要在工作中不斷積累經驗，比如曾經在一次優化中發現對一個表安三個字段查詢的非常多，因此決定建立該三個字段的復合索引，但結果其語句執行效率卻更差。

　　呵呵，今天先寫這么多了，希望大家多多指教

原文轉自：http://www.anti-gravitydesign.com