ORACLE 10G 等待界面診斷
2024-08-29 13:36:43
供稿：網友
使用Oracle 10g中的等待界面診斷性能問題
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
