ORACLE OWI介紹

Oracle資料庫對於做那些做運維的DBA是非常友好的，在Oracle的資料庫中，有著大量的效能檢視可以供DBA來進行效能診斷，也有AWR,ADDM這些極易上手的效能診斷工具幫助DBA分析資料庫效能問題。但是就我看到的，依然存在著不少的DBA還是以一種毫無章法的方式最佳化著Oracle的效能問題。記得一次在杭州的面試另我印象深刻，面試我的是一位DBA老者，他說為什麼一個根據主鍵更新的update語句執行了很久，而同樣的根據這個主鍵的查詢語句卻很快。我當時的回答是：首先需要知道update所在會話在等待什麼，然後興致沖沖的講了什麼是OWI、什麼是響應時間模型，但是看得出來老者對於我的回答並不滿意，就在我繼續滔滔不絕的說著等待事件時，他打斷了我，說：其實是因為鎖導致的。我當時覺得有點尷尬，我問：你是如何定位到是鎖導致的，他面露得意的說：根據他多年的經驗透過猜測最後驗證確實是鎖導致的。可能在其他非ORACLE資料庫裡，經驗的重要性是首屈一指的，但是在ORACLE裡，經驗雖然重要，但是又不重要，經驗這個詞，有猜測的成分在。ORACLE提供了大量的效能檢視、診斷工具方便我們去做調優、做診斷，讓最佳化的工診斷性作變得有據可依，而不僅僅是隻靠試錯、猜測+驗證。還是拿面試的問題來說，如果瞭解OWI理論的話，僅僅只需要檢視會話的等待事件，就能迅速的定位到問題的根源。本章會介紹OWI的相關理論，並且會提到很早以前ORACLE提供的基於命中率最佳化的方法，以及這種方法的缺陷和侷限性。

1.1 命中率最佳化方法

命中率是10G之前主流的最佳化方法，我剛開始做DBA時，經常有帶我的老人告訴我這樣的準則，要保持buffer cache的命中率在95%以上，共享池的命中率在95%以上，保持latch的命中率在98%以上等等，只有達到這樣的命中率，DBA的調優工作才被認可。我們以buffer cache hit ratio為例來說明命中率的含義，當程式訪問資料時，如果資料已經存在在buffer cache中了，那麼就可以直接使用記憶體中的資料，而不用透過磁碟物理讀取，這樣的一次訪問稱為一次命中，否則的話，server程式就要先把資料從磁碟讀入記憶體才能使用，這樣的訪問就是未命中。那麼命中率的計算公式我們也很容易的得出來：命中率=命中總次數/總的訪問次數。那麼為什麼記憶體的命中率高一般來說就代表“效能好”呢？因為一般認為磁碟是非常慢的，而CPU和記憶體都是極快的裝置，對於CPU來說，最快獲取資料的方式是從其自身的cache中，其次從記憶體，從磁碟獲取往往都是最慢的。但是目前絕大多數的資料庫系統都還做不到將其所有的資料都容納在記憶體之中，所以資料庫系統往往會根據一定的演算法比如LRU來將不被經常訪問的資料寫回（如果是髒資料）磁碟，或者直接淘汰（如果不是髒資料）來挪出地方供其他資料塊使用，如果稍後需要這些資料，就會再從磁碟讀入記憶體。如果演算法不合理或者記憶體過於緊張，就會有大量的IO發生，進而影響效能。早期Oracle的效能最佳化原則基本都是圍繞著命中率展開的，你可以看看8I,9I時候的資料庫文件，裡面定義了N多個命中率相關的指標，明確的告訴你，什麼指標應該達到什麼樣的命中率才算理想。這一時期的DBA最佳化資料庫的思想往往是圍繞著提升硬體來展開的，比如增加記憶體，增大記憶體後，會提升命中率，進而提高效能。聽起來似乎也是有道理的，但是命中率最佳化論，有諸多的缺陷，在理解OWI調整思想之前，我們先看看舊的這種最佳化方式存在的問題和侷限性。

· 命中率是被平均的

命中率在我們日常生活中也是一個非常常見的指標，某某某曾經說過，我們中國百分之九十九的奶粉都是合格的，另一位某某某曾經說過中國人平均擁有2套房。這種指標是不顧個體差異的。可能你的某幾個SQL百分之九十九都是物理讀，但是對不起，由於其他某些SQL的命中率極高，導致你的SQL的命中率也被提高了。

· 命中率不是徵兆學的，如果你的SQL由於等待鎖而一直在等待，這個時候命中率幫不了你任何的忙。但是OWI就能很容易根據“鎖”這個徵兆，定位到是產生了鎖爭用。

· 命中率往往透過提升硬體來解決資料庫效能問題，技術含量低。在DBA發現命中率低之後，很多會採用加大記憶體等方式來提升資料庫效能、提升命中率，而且透過提升硬體解決效能問題，容易造成問題反覆，治標不治本。

· 命中率不容易理解。當開發詢問你。他的任務為什麼遲遲沒有跑完的時候，你告訴他由於資料庫的命中率是85%，所以慢，這個可能實在是說服不了他，非常另他費解。就像你打電話給快餐店，為什麼你的午餐遲遲沒有送到，對方告訴你送餐的命中率是99%一樣，莫名其妙！

· 命中率高與系統效能沒有直接的因果關係。一個命中率高的系統不一定效能好，同樣，一個命中率低的系統，不一定效能就不好。試想一下，一個充斥著大量的鎖的系統，雖然命中率可能極高，但是系統的效能卻一塌糊塗。

· 命中率是與吞吐量、響應時間無關的

命中率的調優診斷方法對於效能分析和調整並不可靠，因此ORACLE才會引入了OWI調優思想。

1.1 開車與OWI

小王接到領導電話，需要下午2點從無錫新區到市中心參加一個重要會議，他預估了路程30分鐘可以到達，於是悠閒的吃過中飯，收拾好東西后，一點半開始出發，但是另他意外的是他足足花了50分鐘才到，他遲到了！他感覺到客戶對他的遲到產生了反感，他悔恨萬分自己錯估了時間。如果你是小王，會如何分析這次遲到的原因？

· 紅綠燈太多了？

· 剛好路上遇到交通事故，被迫換了路線？

· 堵車太嚴重？

· 開車速度太慢？

以上的分析是大部分人都能想出來的，也是非常合理的，既然實際到達的時間比預估的長，一定是發生了一些等待導致不能開車，或者是開車速度太慢。這其中其實隱藏了時間模型的方法論。在我們的例子裡就是：

總的路程時間=開車時間+等待時間，如下圖：

 

我用不同的顏色標識出了開車時間和等待時間，等待時間包含了兩次堵車的10分鐘，紅燈的3分鐘。假如再給小王一次機會，他該如何調整方案來保證半個小時可以到達呢？

· 降低開車時間，可以透過加快開車速度來實現，或者選擇較短的路程，如上圖，如果有直線的路程，可以明顯減少開車的路程，進而降低開車時間。

· 降低等待時間，比如是不是可以繞一個紅綠燈比較少的路，或者在行人、車輛比較少的情況下的出行。

Oracle的最佳化思想和這個如出一轍：

 

資料庫響應時間=服務時間+等待時間

服務時間是程式佔用CPU的時間，對應到我們上面的例子裡就是開車時間，等待時間是程式在繼續處理任務之前等待某些特定資源可用的時間，對應到我們上面的例子裡就是等待紅綠燈、堵車的時間。這個公式是基於這樣一個事實：在任何時刻，程式或是在CPU上進行任務計算，或是脫離CPU執行佇列處於等待狀態。作為DBA我們可以透過或縮短服務時間，或縮短等待時間，或縮短兩者來降低最終的響應時間。我們上面的例子裡，車要不是在正常的在道路上執行，要不是在等待紅綠燈、堵車、等待行人透過等等，我們可以透過減少開車時間（提升車速），減少等待時間來縮短到達目的地的時間。對應到資料庫裡，在會話級別，服務時間對應v$sesstat裡的CPU used when call started，等待時間對應v$session_event裡特定會話的所有前臺程式相關等待事件的time_waited之和。CPU used when call started又被細分為：CPU used for parsing，CPU used for recursive calls，CPU used for normal work。需要注意的是，CPU相關的統計資訊是在一個call結束後才會被統計更新，而統計資訊的資料將以實時的模式更新。因此一個耗時很長的SQL將不會更新他的CPU資訊直到其執行結束。

如何獲得會話級資料庫響應時間的資料？

如果想獲得完整會話級的資料庫響應時間資料，一般需要藉助資料庫LOGOFF觸發器來實現，但是你往往不需要這麼做。例如，你想測量：

資料庫響應時間模型更加接近終端使用者的體驗，也將資料庫效能調優提升到了一個新的高度。作為DBA在進行效能跟蹤診斷的時候，時刻應該把響應時間牢記心中。

為什麼關注響應時間比關注命中率重要？因為時間對於終端使用者的感受是最直接的，一位開發向你抱怨一個任務執行緩慢，或者網站的一位客戶投訴網頁開啟慢，他們都是對響應時間的不滿，他們根本不關心是因為某種命中率低或者你主機的記憶體、CPU資源、IO資源不足等等原因才來抱怨的。不要把自己當做DBA，把你當做是一個普通的網站客戶，網站的開啟速度慢，你會如何去抱怨？肯定也是響應時間！

再次看一下我們的響應時間公式：

資料庫響應時間=服務時間+等待時間

OWI最大的貢獻是透過各種等待事件的次數、等待的時間來解釋一個任務執行過程中，時間消耗的分佈，讓你非常容易定位到耗時最大的等待，進而有的放矢。響應時間模型是非常好理解的，即使一個非專業的DBA也極其容易理解其理論。比如你在十一假期排隊購買火車票，你買票的總時間=排隊時間+購買時間，如果想縮短購票總時間，可以透過縮短排隊時間、或縮短購買時間、或縮短兩者來進行嘗試，比如在晚上人少的時候排隊購買減少排隊時間，提前觀察一下哪位業務員賣票的速度快，技術熟練，然後選擇在她的業務視窗辦理購票。 

什麼是OWI？

OWI表達的內容跟等待時間相關。繼續以我們上面的開車為例，如果可以設計一個功能，能夠跟蹤小王當前開車的狀態，並且在他開車發生等待的情況下，提供給你一個介面，透過這個介面可以查詢當前他在發生什麼等待、發生在這個等待上的時間、次數，並且保留他本次路程裡，所有發生的等待的事件（紅綠燈、堵車、等待行人透過）、等待的次數、等待的總時間，那麼這個功能對應到ORACLE裡就叫OWI-oracle wait interface，翻譯為中文叫Oracle等待事件介面。我們舉一個資料庫的例子：

update test set a=1 where id=1;

commit;

我們開啟了一個事務並提交，這個過程裡，經過了兩次db file sequential read，共6ms，一次enq:TX-row lock contention ，共1s，一次log file sync，共6ms，共發生等待1012ms。這些等待在ORACLE裡稱為wait event，對這些等待進行計數（次數+時間），並且透過各種v$檢視進行展現的功能介面叫做OWI。OWI最早出現在ORACLE 7.0.1中，當時的等待事件只有100多個，之後ORACLE不斷完善此功能，對很多等待事件進行了細分，截止到目前最新的版本12cr1，這個等待事件已經擴大到了1567個，由此可見ORACLE對於OWI這麼一個功能的重視和信心。Oracle的等待事件之所以越來越多，一方面是由於新增的功能導致，另一方面也是把之前沒納入到等待事件中的系統呼叫重新開發，然後增加到新版本中來。比如開車過程中可能會遭遇堵車、紅綠燈、行人過馬路種種等待，一開始系統的OWI基於這三個等待來進行計數、展示，但是後來可能發現交通事故其實也是等待的一種，因此在下個版本釋出的時候，將其納入進來。

版本	等待時間總數（大約）
7.0.1	100
8i	220
9i	400
10gr2	874
11gr2	1152
12cr1	1567

OWI思想

OWI對程式在執行任務過程的狀態進行跟蹤，並且在程式遇到等待時，對其進行計數，以v$檢視的形式暴露給使用者診斷、分析。常見的等待有EQN鎖，latch相關，IO相關的等待，network相關等等。OWI記錄程式從開始到結束遭遇的所有的這些等待，DBA可以透過消除、減少這些等待來降低最終的響應時間。對應到我們最開始舉的開車的例子裡，可以透過消除紅綠燈、堵車、等待行人透過等時間來減少到達時間的道理一樣。作為ORACLE公司的開發、設計者，需要知道在哪些地方可能產生呼叫，然後在這些點上設定event計數，最終以各種檢視的形式給我們展現出來。例如還是拿開車為例，需要設計出紅路燈、堵車、等待行人透過、停車標誌、遇到車禍現場等等的等待點。在ORACLE中，如果一個程式在需要等待一個資源時，ORACLE就需要收集這個等待的統計，並且記錄到對應的檢視中。等待事件能夠迅速告訴我們阻擾效能的主要瓶頸，方便DBA找到解決問題的最佳辦法。例如如下這些統計：

很清楚的能夠看到資料庫系統最大的瓶頸在於log file sync，佔了整個DB TIME的65.89%.定位到問題後，後續就是如何解決、最佳化log file sync的問題了。

OWI帶來了一種新的監控、測量資料庫瓶頸的方法，同時透過一些檢視將這些資料展示表達出來。如果現在再有開發諮詢你為什麼他的任務執行緩慢，你可以從容應對，告訴他這個任務執行過程中，百分之八十的時間都在等待物理IO，百分之十的時間在等待日誌寫，其他時間為CPU時間，如果想最佳化任務，必須降低IO，降低log file sync。如下示例，取兩次會話v$session_event的快照，然後取delta，就可以很容易得出此會話發生的等待事件、等待次數、等待時間。

execute snap_stats.start_snap

declare

c number;

begin

select count(*) into c from a;

for i in 1 .. 100000 loop

update a set id=1 where rownum<2;

commit write immediate wait;

end loop;

end;

/

在上面的示例中，log file sycn,direct path read佔取了資料庫時間的絕大部分。最佳化的方向將需要朝向減少IO等待時間、降低log file sync。

    等待事件透過P1，P2，P3這三個參數列現當前等待的資源

   絕不要根據數量做出調整策略，而應該關注響應時間。因為沒有時間消耗的等待次數存在欺騙性。

我們簡單總結一下OWI的特點：

· OWI是量化的。OWI記錄了所有等待事件的等待次數、等待時間、平均等待時間。在沒有OWI的情況下遭遇系統緩慢，可能會透過種種猜測來進行試錯，活躍會話數過多？系統可能會有鎖等待？buffer cache命中率太低？硬解析過多？但是如果是透過OWI分析，那麼就可以理直氣壯的得出，分析近半個小時的等待事件，出現了大量的shared pool latch爭用，佔整個db time的百分之三十，而正常情況這一指標僅僅只佔百分之五，並且結合每秒硬解析數看，這個指標也非常高，因此可以推論出是由於硬解析過高導致的資料庫效能下降。由此可以看出，熟練使用OWI後，能夠擺脫以往最佳化效能問題時候的”猜測性“，將複雜的效能最佳化問題，解釋為任何人都容易理解的量化的值。

· OWI是更貼近”自然“的分析理論。拿我舉的開車的例子來說，大部分人都會想到細分出等待過程中的各個環節，然後找出最耗時的幾個環節，有目的性的進行最佳化。

· OWI是徵兆學的。