Oracle資料庫性能模型

最近一直在思考一個問題：如何為一個資料庫建立性能模型？作為一名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資料庫的時間模型：

<a href="http://www.hellodb.net/wp-content/uploads/2010/06/Oracle_Time_Model.jpg"></a>

在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

PL/SQL compilation elapsed time

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

3,351

87.21

User I/O

257,450

350

1

9.12

Commit

127,672

90

2.35

Cluster

53,770

10

0.27

Concurrency

25,652

7

9

0.24

System I/O

3,623

6

2

Network

2,069,001

5

0.14

Application

679

0.13

Other

20,828

78

4

0.10

Configuration

2,353

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片段：

817,470

5,232

67.49

238,850

1,083

13.97

1,071

13.82

4,150

403

97

5.20

42,626

27

31

0.40

23,742

0.07

1,838,062

0.03

125

2,026

82

我們看到，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 /txn

% DB time

db file sequential read

4,315,803

11,202

3

29.65

53.06

db file scattered read

320,148

1,434

2.20

6.79

direct path read

683,707

1,239

4.70

5.87

SQL*Net more data from client

145,678

791

3.75

log file sync

145,656

439

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中找到這些資訊，并判斷自己的資料庫屬于哪種類型，瓶頸在哪裡，是否存在性能風險。當面對諸如“硬體是否能夠滿足性能需求”，“系統明年是否需要擴容”，“應用是否會對系統産生影響”此類問題時，我們可以用這些經驗值給出一個判斷。

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

3,547.99

92.34

3,350.65

27.42

0.71

11.16

0.29

7.61

0.20

5.50

3.02

0.08

0.02

failed parse elapsed time

3,842.21

victor666666

6 17th, 201010:00

我的了解如下：

一個性能好的系統（很多是理想狀态）=應用所需最小的邏輯讀（前提是I/O已達最佳）+無阻或少阻的響應時間