Oracle資料庫業務最佳化心得

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

　　對於我現在所做的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的執行方式，需要在工作中不斷積累經驗，比如曾經在一次最佳化中發現對一個表安三個欄位查詢的非常多，因此決定建立該三個欄位的複合索引，但結果其語句執行效率卻更差。