專家分享oralce資料庫業務最佳化心得

做了一大半年的業務資料庫最佳化了，有一點感想，寫出來自勵一下。 公司開發的產品基本上都使用ORACLE資料庫，而且業務系統中儲存和使用的資料量很大，使用業務系統的使用者也很多。在系統忙時，大約會有一千個使用者同事訪問資料庫系統，因此經常會有使用者抱怨系統慢，點選查詢後，系統出現假當機現象，後臺執行ORACLE系統的小型機的IDLE值偏低(甚至出現IDLE為0的情況，這時使用者會驚呼資料庫系統會癱掉，雖然小型型機在IDLE值為0的情況下也不一定會癱機，但這無疑會增加使用者的擔憂)，系統執行在一個不安全的狀態等等。

[@more@]

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

公司的客戶很多，大多數情況會在公司遠端處理客戶的資料庫問題。作為問題的定位者，一定要搞清楚實際執行的資料庫系統到底出現了什麼問題。現場的維護人員和使用者最喜歡使用的形容詞是:系統很慢;資料庫鎖表了等等。使用者向外面傳達的信心往往是非常模糊的，在開始接觸這個問題時候起，我們就要引導使用者去幫外面瞭解系統真實的情況。有幾個東西是一定要看看的，一個是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語句的執行資訊，可以透過如下語句選擇一下物理讀和記憶體讀較高的語句:

SELECT

t.HASH_VALUE,

t.EXECUTIONS,

t.DISK_READS,

round(t.DISK_READS/t.EXECUTIONS) AS perDiskReads,

t.BUFFER_GETS,

round(t.BUFFER_GETS/t.EXECUTIONS) AS perBufferReads,

t.ELAPSED_TIME,

round(t.ELAPSED_TIME/t.EXECUTIONS) AS perElayTime,

t.CPU_TIME,

round(t.CPU_TIME/t.EXECUTIONS) AS perCpuTime,

t.FIRST_LOAD_TIME,

t.SQL_TEXT

FROM v$sql t

WHERE (t.DISK_READS/t.EXECUTIONS >

500 OR t.BUFFER_GETS/t.EXECUTIONS > 20000)

AND t.EXECUTIONS > 0

ORDER BY 6 DESC;

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

對於找到的SQL語句我們可以逐一分析其執行計劃，結合涉及到的表的資料量，我們可以估算或者測試該語句的執行效率，分析表WHERE條件中涉及的欄位(術語叫做謂詞)，該欄位資料分佈如何，選擇性是否好，是否有索引。這是一個非常繁雜和瑣碎的工作，但從這些瑣碎的工作中，我們能發現那些SQL執行時選擇的索引不對，哪些表缺少相應的索引導致了全表掃描，哪些語句條件不夠導致對分割槽表進行了全表掃描。總之，對於一個給定的SQL，我們結合其表資料量的大小和分佈，SQL中使用的查詢條件，能夠找到一個效能最優的執行方式，透過調整索引、使用ORACLE提示，使ORACLE系統按照最優的方式來執行SQL。如何去分析和確定ORACLE的執行方式，一個最普遍的原則就是儘量根據其謂詞(查詢條件)，使用選擇性最好的索引(當然，對於一些小表，可以考慮使用全表掃描的方式效能會更好)。對於SQL的執行方式，需要在工作中不斷積累經驗。