ORACLE 10G 等待介面診斷(轉)

使用Oracle 10g中的等待介面診斷效能問題[@more@]

　
　　John是Acme銀行的資料庫管理員，電話另一端是憤怒的使用者Bill，他抱怨他的資料庫會話被掛起來了，對這種抱怨大多數資料庫管理員是再熟悉不過了。John怎麼才能打消Bill的抱怨呢？
　　
　　Acme銀行的資料庫是Oracle資料庫10g，因此John有很多種選擇。自動資料庫診斷管理器（ADDM）是Oracle資料庫10g的新特性，它可以告訴John資料庫當前的整體狀態和效能，因此John一開始就用ADDM來確定Bill的會話正在經歷的是否是資料庫範圍內的問題。ADDM報告確認沒有能對Bill的會話產生這種影響的資料庫範圍內的問題，因此John繼續考慮下一種選擇。
　　
　　一種診斷會話級事件（如Bill的問題）的方法就是確定會話是否正在等待什麼事件，如檔案塊的讀操作或錶行上的鎖或栓。從Oracle7開始，Oracle就提供了多種機制來顯示資料庫內發生的等待，而且在最近幾年裡，這一產品在不斷地完善，加入了越來越多的診斷資訊。在Oracle資料庫10g中，提供了經過重大改進的等待事件資訊，使診斷一個會話的速度減慢的問題變得更加容易。本文告訴你如何使用Oracle資料庫10g中的等待事件來確定瓶頸問題。
　　
　　會話等待
　　資料庫管理員John如何才能確定是什麼引起了Bill的會話掛起呢？實際上，會話並沒有掛起；它正等待一個事件的發生，而這正是John要檢查的。
　　
　　John要繼續其調查可以使用Oracle企業管理器或者直接透過命令列訪問V$檢視。John有一套用於診斷這些類問題的指令碼，因此他使用命令列。
　　
　　John查詢V$SESSION檢視來看一下Bill的會話正在等待什麼。（請注意John過濾掉了所有空閒事件。）
　　
　　select sid, username, event, blocking_session,
　　seconds_in_wait, wait_time
　　from v$session where state in ('WAITING')
　　and wait_class != 'Idle';
　　
　　下面以垂直格式給出輸出結果
　　SID : 270
　　USERNAME : BILL
　　EVENT : enq: TX - row lock
　　contention
　　BLOCKING_SESSION : 254
　　SECONDS_IN_WAIT : 83
　　WAIT_TIME : 0
　　
　　看到這些資訊，John立刻得出結論，Bill的SID 270會話正在等待一個表上的鎖，而這個鎖正由254會話(BLOCKING_SESSION)佔用。
　　
　　但是John想知道哪條SQL語句引起了這個鎖的佔用。他可以透過連線V$SESSION和V$SQL檢視執行下面的查詢很容易地找到答案：
　　
　　select sid, sql_text
　　from v$session s, v$sql q
　　where sid in (254,270)
　　and (
　　q.sql_id = s.sql_id or
　　q.sql_id = s.prev_sql_id);
　　
　　程式碼清單1顯示了查詢結果。John看到（在清單1中）兩個會話都試圖更新同一行。除非會話254提交或回滾，否則會話270將一直等待這個鎖。他向Bill解釋了這一切，而Bill現在也不那麼生氣了，他認為應用程式中的有些東西出了問題，因此要求John結束會話254，以釋放鎖。
　　
　　等待類
　　John結束了妨礙Bill的會話後，Bill的會話可以繼續進行但是很慢。John決定檢查該會話中的其他問題。他又一次檢檢視是否有任何其他等待事件，但這次他特別檢查Bill的會話。
　　
　　在Oracle資料庫10g中，等待事件根據事件的型別分為不同的等待類。將事件分組使你能夠將精力集中在特定的類上，而排除那些不重要的事件，如空閒事件。John對V$SESSION_WAIT_CLASS檢視執行下面的查詢：
　　
　　select wait_class_id, wait_class,
　　total_waits, time_waited
　　from v$session_wait_class
　　where sid = 270;
　　
　　程式碼清單2給出輸出結果，它顯示出等待類，以及在每個類中會話等待事件的次數。它告訴John，自例項啟動後與該應用程式相關的等待，如那些由於行級鎖引起的等待出現了17760次，所花費的時間總共為281654釐秒(百分之一秒，cs)。John認為對於這個會話，TIME_WAITED的值太高了。他決定在該application（應用程式）等待類中尋找引起這些等待的原因。在V$SYSTEM_EVENT檢視中可以獲得每種等待的出現次數。他執行下面的查詢來確定application等待類（類id 4217450380）中的每種等待：
　　
　　select event, total_waits, time_waited
　　from v$system_event e, v$event_name n
　　where n.event_id = e.event_id
　　and wait_class_id = 4217450380;
　　
　　程式碼清單3列出了該查詢的輸出結果。該結果顯示，在application等待類的等待時間中，鎖的爭用（由事件enq: TX - row lock contention標識）佔了大部分。這與John有關係。是不是編寫得不好的應用程式有可能直接進入了產品資料庫，從而引起這些鎖的爭用問題？
　　
　　但是，作為一名經驗豐富的DBA，John沒有立即下這個結論。程式碼清單3中的資料僅僅表明使用者經歷了2275次與鎖爭用有關的等待，共計花費280856釐秒。有可能大多數等待只有1到2釐秒，所有等待時間可能僅僅是由一個長的等待引起的，在這種情況下，該應用程式並沒有問題。單個長的等待也許是出現了反常現象它扭曲了資料，而並不代表系統的真正工作負載。John如何確定是否是一個單一等待事件扭曲了該資料呢？
　　
　　Oracle 10g提供了一種新的檢視，V$EVENT_HISTOGRAM，它顯示等待時間週期以及會話等待某一特定時間週期的頻度。Jone對V$EVENT_HISTOGRAM執行下面的查詢語句：
　　
　　select wait_time_milli bucket, wait_count
　　from v$event_histogram
　　where event =
　　'enq: TX - row lock contention';
　　
　　輸出結果如下：
　　
　　BUCKET WAIT_COUNT
　　----------- ----------
　　1 252
　　2 0
　　4 0
　　8 0
　　16 1
　　32 0
　　64 4
　　128 52
　　256 706
　　512 392
　　1024 18
　　2048 7
　　4096 843
　　
　　V$EVENT_HISTOGRAM檢視顯示等待時間段以及在這期間會話等待某一特定事件--在本例中就是行級鎖爭用--的次數。例如，會話等待少於1毫秒（ms）的事件共252次，等待大於1毫秒少於16毫秒的事件1次，等等。WAIT_COUNT列值之和為2275，與程式碼清單3列出的事件enq:TX - row lock contention中顯示的值相同。V$EVENT_HISTOGRAM檢視顯示，大多數等待發生在256毫秒、512毫秒和4096毫秒的事件上，這就充分證明了該應用程式正在經歷鎖的爭用問題，而這個鎖的爭用問題就是導致Bill的會話速度減慢的原因。如果檢視顯示等待發生在1毫秒的範圍內，那麼John就不能這麼認為，因為這樣短時間的等待似乎是正常的。
　　
　　時間模型
　　剛剛給Bill解釋完他的初步發現，Lora就走了進來，也帶著類似的抱怨：她的SID 355會話非常慢。 John又一次透過對V$SESSION檢視執行下面的查詢來尋找該會話等待的事件：
　　
　　select event, seconds_in_wait,
　　wait_time
　　from v$session
　　where sid = 355;
　　
　　程式碼清單4列出的輸出結果顯示，Lora的會話中有各種各樣的等待事件，包括栓（latch）爭用，它表明一個應用程式的設計可能有問題。但是，John在給Lora提供修改應用程式的方法之前，他必須用事實來支援他的理論，即該應用程式設計的不好導致了Lora的會話效能低下。為了測試他的理論，他決定要確定Lora會話對資源的利用是否格外高，以及除了這個會話以外其他會話的速度是否也很慢。
　　
　　在Oracle資料庫10g的Time Model（時間模型）介面中，John可以輕鬆檢視在各種活動中會話所用時間的詳細情況。他對V$SESS_TIME_MODEL檢視執行下面的查詢語句：
　　
　　select stat_name, value
　　from v$sess_time_model
　　where sid = 355;
　　
　　碼清單5給出的輸出結果顯示了該會話在各個方面所花費的時間（單位：微秒）。從這個結果中John瞭解到，執行所有SQL查詢共花878088366微秒（執行），其中503996336微秒用於解析（解析花費的時間），即佔了SQL執行時間的57%，這表明導致速度慢的原因是解析操作過多。John告訴Lora這一資訊，她採納了應用程式設計小組的建議。
　　
　　OS統計資料
　　在仔細檢查使用者的效能問題時，John還需要排除主機系統是瓶頸的可能性。在採用Oracle 10g以前，他可以使用作業系統（OS）工具，如sar 和vmstat，並推斷出一些確定爭用問題的度量指標。在Oracle 10g中，在資料庫中自動採集OS級別的度量指標。為了檢視潛在的主機爭用問題，John對V$OSSTAT檢視執行下面的查詢：
　　
　　select * from v$osstat;
　　
　　程式碼清單6給出的輸出結果顯示了所採集的OS級別的各種度量指標元素。所有時間元素都以釐秒為單位。從程式碼清單6顯示的結果中John瞭解到，系統的一個CPU有51025805釐秒空閒（IDLE_TICKS）、2389857釐秒繁忙（BUSY_TICKS），這表明CPU有大約4%的時間繁忙。從中他得出結論，在主機中CPU不是瓶頸。請注意，如果主機系統有多於1個的CPU，則標頭中有AVG_字首的列，如AVG_IDLE_TICKS將顯示所有CPU的這些度量指標的平均值。
　　
　　活動會話的歷史
　　到目前為止，每當發生問題時使用者就向John諮詢，使他能實時地檢視效能狀況。沒過多久Janice又找到John，抱怨最近出現的效能問題。當John查詢V$SESSION檢視時，會話是空閒的，沒有正在等待的事件。John如何檢查Janice的會話出現問題時正在等待什麼事件呢？
　　
　　Oracle 10g在記憶體緩衝區內每秒採集一次活動會話的資訊。這個緩衝區被稱為活動會話歷史（Active Session History，ASH），可以在V$ACTIVE_SESSION_HISTORY動態效能檢視中檢視它，其中的資料在被新資料週期性地覆蓋前保留30分鐘。John得到Janice會話的SID和SERIAL#，然後對V$ACTIVE_SESSIO