【轉載】[效能分析]Oracle資料庫效能模型

[轉載地址:]http://www.hellodb.net/2010/06/db-performance-analysis.html

最近一直在思考一個問題：如何為一個資料庫建立效能模型？作為一名DBA來說，我們面臨的一個巨大挑戰是：如何保證資料庫的效能可以滿足快速變化的應用的需求，如何在資料量和訪問量持續增長的情況下，保證應用的響應時間和資料庫的負載處在合理的水平下。我們可能會經常面對以下的問題：某個SQL每秒要執行100次，響應時間是多少？某個應用釋出後，對資料庫的影響如何？所以，評估應用對資料庫所產生的影響，優化應用並預測風險，保證資料庫的可用性和穩定性，這是應用DBA真正有價值的地方。

響應時間為中心：

如果要選擇一個評價系統優劣的效能指標，毫無疑問應該是響應時間。響應時間是客戶體驗的第一要素，所有的優化都應該為降低響應時間而努力。對於資料庫系統也是如此，我們優化系統，優化SQL，最終目標都是為了降低響應時間，單位時間內可以處理更多的請求。

資料庫時間模型：

響應時間一般分為服務時間(Service time)和等待時間(Wait time)，服務時間指程式佔用CPU的時間，包括前臺程式(Server process)和後臺程式(Backgroud process)，我們一般只關注前臺程式佔用的CPU time。等待時間包括很多型別，一般最常見的是IO等待和併發等待，IO等待包括sequential read，scattered read和log file sync等等，而併發等待主要是latch和enqueue。SQL execute elapsed time指使用者程式執行SQL的響應時間，包含CPU time和wait time。

以下是Oracle資料庫的時間模型：

在Oracle系統中，我們可以利用AWR或Statspack報告，看到資料庫的時間資訊：

Statistic Name	Time (s)	% of DB Time
sql execute elapsed time	3,062.17	91.52
DB CPU	2,842.08	84.95
parse time elapsed	25.87	0.77
PL/SQL execution elapsed time	11.75	0.35
sequence load elapsed time	7.55	0.23
hard parse elapsed time	5.06	0.15
connection management call elapsed time	3.13	0.09
hard parse (sharing criteria) elapsed time	0.04	0.00
repeated bind elapsed time	0.01	0.00
PL/SQL compilation elapsed time	0.00	0.00
DB time	3,345.74
background elapsed time	204.91
background cpu time	72.30

DB time是整個資料庫使用者程式消耗的總時間，是從第一項到第十項時間的總和（從sql execute elapsed time到PL/SQL compilation elapsed time），但是我們會發現這十項時間的總和比DB Time要大一些，這是因為部分時間資訊有重疊的部分，比如SQL execute elapsed time就包括了很大一部分DB cpu的時間。而background elapsed time和background cpu time則是Oracle後臺程式消耗的時間和cpu time。

資料庫響應時間分析：

資料庫系統的響應時間由四個要素決定：CPU，IO，記憶體和網路，其中CPU和IO是最重要的因素。與之相比，記憶體與網路則簡單很多，因為通常情況下，對於一個調優的系統來說，記憶體訪問的延遲時間非常小（100 ns以下，1 ms=1000000 ns）相比較CPU和IO幾乎可以忽略。而網路延遲則通常是一個常數，比如在一個資料中心的情況下，網路的延遲一般在3ms以下，如果存在多資料中心的情況，網路延遲可能會超過20ms，所以對於一個分散式系統來說，網路延遲是必須要考慮的問題。

在這裡，我們不考慮分散式系統，並且忽略記憶體的訪問延遲，重點分析CPU和IO，我們看以下資料庫的AWR片段：

Wait Class	Waits	%Time -outs	Total Wait Time (s)	Avg wait (ms)	%DB time
DB CPU			3,351		87.21
User I/O	257,450	0	350	1	9.12
Commit	127,672	0	90	1	2.35
Cluster	53,770	0	10	0	0.27
Concurrency	25,652	7	9	0	0.24
System I/O	3,623	0	6	2	0.15
Network	2,069,001	0	5	0	0.14
Application	679	0	5	7	0.13
Other	20,828	78	4	0	0.10
Configuration	2,353	0	2	1	0.06

我們看到這個系統中DB CPU佔整個DB time的87.21%，User I/O佔整個DB time的9.12%，commit相關的IO等待佔2.35%（主要是log file sync），CPU和IO佔用了整個DB time的96.68%。由於DB CPU所佔的比例很高，所以這個資料庫系統是CPU intensive型別，這裡的DB CPU主要是執行SQL的服務時間。

我們再看另外的一個資料庫的AWR片段：

Wait Class	Waits	%Time -outs	Total Wait Time (s)	Avg wait (ms)	%DB time
Commit	817,470	0	5,232	6	67.49
User I/O	238,850	0	1,083	5	13.97
DB CPU			1,071		13.82
Configuration	4,150	1	403	97	5.20
Concurrency	42,626	27	31	1	0.40
System I/O	23,742	0	6	0	0.07
Network	1,838,062	0	2	0	0.03
Application	125	0	0	2	0.00
Other	2,026	82	0	0	0.00

我們看到，Commit和User I/O佔DB time的81.46%，而DB CPU只佔13.82%，所以這個資料庫系統是IO instensive型別的。

Physical read

Physical read是指Oracle在buffer cache中沒有找到相應的block，需要從IO子系統讀取相應的block的過程，對應的IO稱為物理IO，物理讀數量代表物理IO讀取的block數量。因為一般IO子系統都是慢速的磁碟，所以物理IO對整體響應時間的影響非常大，如果發生大量的物理IO，整個系統的響應時間會變得很差。系統的IO子系統可能是檔案系統，裸裝置或者ASM，底層硬體可能是SAN儲存，NAS儲存或者普通SAS磁碟等等。為了提高響應時間，通常在物理磁碟與Oracle之間增加cache層，對於Oracle來說，物理IO並不一定是真正訪問磁碟，很可能是訪問檔案系統cache，儲存的cache等等。

不管IO subsystem是什麼，Oracle只關心物理IO的響應時間。通過AWR報告，我們可以看到物理IO的響應時間：

Event	Waits	%Time -outs	Total Wait Time (s)	Avg wait (ms)	Waits /txn	% DB time
db file sequential read	4,315,803	0	11,202	3	29.65	53.06
db file scattered read	320,148	0	1,434	4	2.20	6.79
direct path read	683,707	0	1,239	2	4.70	5.87
SQL*Net more data from client	145,678	0	791	5	1.00	3.75
log file sync	145,656	0	439	3	1.00	2.08

db file sequential read（單塊讀，隨機IO）的平均響應時間為3ms，db file scattered read（多塊讀，連續IO）的平均響應時間是4ms，logfile file sync的平均響應時間是3ms，前兩者的Wait class是User I/O，代表使用者程式讀操作的響應時間，logfile sync的wait class是Commit，代表lgwr程式寫redo的響應時間，因為使用者commit必須完成log file sync的操作，所以它也會直接影響使用者程式寫操作的響應時間。

關於物理IO的響應時間，我們有一個經驗值。對於Sequential read和Scattered read，我們認為小於10ms屬於正常狀態，而大於10ms則認為IO subsystem的響應延遲過大。所以我們在衡量儲存系統的效能時，只有響應時間在10ms以下的IO我們認為是有效的。這裡有一個有趣的現象，就是sequential read和scattered read的響應時間幾乎相差無幾，也就是說隨機IO讀取8K資料和連續IO讀取128K資料，響應時間差別很小，這是由磁碟的機械特性造成的，延遲時間=尋道時間+延遲時間，順序讀和離散讀的尋道時間一致，只是延遲時間有很小的差異，所以兩者的響應時間差異很小。

對於log file sync的響應時間，因為使用者commit必須完成log file sync，所以整個系統的寫操作的響應時間都取決於它的響應時間，而且從整個資料庫系統的角度去看，log file sync幾乎是序列的，所以這個響應時間對寫操作影響非常大，我們的經驗值是必須保證在5ms以下，如果超過5ms整個系統的寫操作都會受到嚴重的影響。

Logical read

Logical read是Oracle從buffer cache中讀取block的過程，對應的IO稱為邏輯IO，邏輯讀數量代表邏輯IO讀取的block數量。因為Oracle必須首先將block讀入buffer cache中(direct path read除外)，所以邏輯讀數量包含了物理讀數量。對於一個SQL來說，邏輯讀數量是衡量其效能的標準，而不是物理讀。雖然物理IO的響應延遲比邏輯IO大很多，但是物理讀數量會隨著執行次數而變化（頻繁讀取導致block被快取在buffer cache中）。對於一個系統也是如此，邏輯讀應該是資料庫效能評估模型的核心，我們需要建立邏輯讀與響應時間的對應關係。

每個邏輯讀的響應時間是多少，這是一個巨大的挑戰。因為每個邏輯讀背後隱藏了很多動作，可能包括物理讀，等待事件，CPU time等等。我對很多資料庫的AWR報告做了分析，期望根據經驗值建立一個簡化的模型。我們假設一個資料庫如果是充分調優的，除CPU time和IO以外的等待時間應該儘可能少（應小於DB time 10%）。在這個前提下，我們只關心CPU time和IO的影響，並將系統分為三類：CPU密集型，IO密集型和混合型：

1.IO密集型

User IO     85%

DB CPU        5%

每邏輯讀響應時間0.1-0.5ms

2.CPU密集型

DB CPU         85%

User IO        10%

每邏輯讀響應時間小於0.01ms

3.混合型

User I/O     60%

DB CPU         20%

每邏輯讀響應時間0.05-0.1ms

以上資料是根據很多個典型資料庫的AWR報告計算出來的經驗值，計算公式很簡單：DB time/邏輯讀=每邏輯讀響應時間。因為並沒有考慮硬體和OS上的差異，所以這個數值並不是特別準確，但我們還是可以發現一些規律：隨著IO所佔比例從10%增加到85%，響應時間也從小於0.01ms到0.5ms。

預測系統瓶頸

對於資料庫來說，IO子系統對效能影響非常大，必須保證在一定的IO的壓力下，響應延遲控制在合理的範圍內（前面說的10ms和5ms）。因為每塊磁碟可以承受的IOPS是基本確定的，比如15K的SAS磁碟，在響應延遲不超過10ms的前提下，可以提供150個IOPS，如果不考慮cache的影響，整個儲存子系統的IOPS是比較容易計算的。我們可以在系統上線前，進行大量充分的測試，建立儲存IOPS與響應延遲的模型，這樣我們就可以預測出效能出現拐點的風險，提前做出擴容的判斷。在AWR報告中，我們可以得到每秒的物理IO的數量和響應時間，可以方便的實現效能監控和趨勢預警。

評估CPU的容量瓶頸相對簡單，Oracle中CPU time的計算是每個CPU耗費時間的總和，如果有16顆(核)CPU，1個小時理論上可以提供3600×16=57600s CPU time，不超過57600s CPU time我們可以認為不會在CPU上排隊，系統不會出現CPU瓶頸。但是需要注意的是，除了使用者程式使用CPU以外，作業系統也需要佔用CPU資源，用來管理記憶體和程式排程等。我們在OS上看到的CPU使用率中的sys部分就是系統佔用的CPU資源，所以應該考慮至少保留10-20%的CPU資源給OS使用。

併發訪問對資料庫的影響

Oracle是一個Disk-based database，設計的出發點就是大部分資料在外部儲存中，而只有小部分資料被cache在buffer中，它既不同於Memcache這類KV cache，也不同於timesten這類In-memory database。所以，就算是所有的資料都可以被cache在buffer中，在高併發訪問的情況下，也可能會出現大量的latch等待，最常見的情況就是cache buffer chain。當大量併發訪問同一塊資料時，就很可能會出現cache buffer chain的latch爭用，也就是我們常說的“熱點”。

需要注意的是：Oracle中的latch等待分為spin和sleep兩個部分，spin消耗cpu time，而sleep則是等待時間。所以大量的latch等待不僅僅會產生大量的等待時間，而且會消耗大量的CPU time。

Oracle是一個為併發操作而設計的資料庫，大量的併發讀寫請求，可能會帶來額外的效能消耗。比如讀取一部分頻繁修改的資料，Oracle為了保證一致性讀的需要，會利用undo資訊構造產生大量CR block，同時會產生大量的邏輯讀，這樣會消耗額外的CPU和響應時間。

儲存也可能存在熱點的問題，需要前期對儲存系統充分的優化，常見的手段是利用RAID技術，將資料分散在不同的磁碟上，防止出現“熱點”盤。Oracle ASM提供了Rebalance的功能，允許DBA將儲存中的的資料重新分佈，達到消除熱點的目的。

總之，Oracle是一個可以提供大量併發讀寫訪問的資料庫系統，但是在很多地方，Oracle又不得采用一些序列的控制手段，比如latch，enqueue和mutex，我們要做的就是儘量降低這些序列控制對資料庫整體效能的影響。

資料庫優化原則

基於響應時間的Oracle優化原則：儘量減少等待時間（Wait time），提高服務時間（Service time）。這也是基於Oracle等待事件的分析方法的基本原則：儘量消除各種等待事件對系統的影響，從而提高系統效能和響應時間。

如果資料庫系統除了CPU和IO以外的等待時間超過DB time的5%以上的話，可能存在某些效能問題，需要DBA採用等待事件的分析方法，對系統或應用進行優化。

–EOF–

後記：為什麼要寫這麼一個主題，因為最近和一位同事探討機器自動稽核SQL的問題，就想建立一個簡單的模型，用來開發一個SQL稽核工具，開發人員通過工具和預先建立好的模型，就可以確定這個SQL是否存在效能風險。之前我們在做SQL優化的時候，只是關注這個SQL本身是否優化，邏輯讀是多少。但是，很少有人把邏輯讀和響應時間之間的關係建立起來，我試圖想回答這個問題。

關於容量規劃和風險預測其實是一個很有意義的命題，但是我們很多時候都侷限在一些具體的技術細節中，而忽略了對整個系統容量的把握，事實上，這也是非常難的一件事。也許到目前為止，我根本沒有達到建立“模型”的程度，但是我試圖將這些方方面面的因素聯絡起來，提供一些有用的經驗值給大家，我覺得這個挺有意義。

在這篇文章中，我提到了幾個有意義的經驗值，這是我根據很多資料庫AWR中的資訊計算出來的，雖然不保證完全準確，但是我覺得基本是靠譜的。建議每個DBA都應該從AWR中找到這些資訊，並判斷自己的資料庫屬於哪種型別，瓶頸在哪裡，是否存在效能風險。當面對諸如“硬體是否能夠滿足效能需求”，“系統明年是否需要擴容”，“應用是否會對系統產生影響”此類問題時，我們可以用這些經驗值給出一個判斷。

關於這個命題，目前只是一個階段性的結果，我還會繼續思考。如果大家有興趣，歡迎和我一起探討這個話題。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15747463/viewspace-754180/，如需轉載，請註明出處，否則將追究法律責任。