通常情況下,使用者送出一條SQL語句,總會存在這樣或那樣的等待事件。也就是說由于所需資源被占用導緻程序不得不處于等待狀态。Oracle為我們提供了擷取這些等待事件的可用視圖。根據這些視圖可以得知哪些事件導緻該SQL語句效率低下而采取相應的修改或調整。本文基于Oracle 10g描述了如何通過視圖v$session_wait,v$session_event,以及v$system_event去擷取等待事件的相關資訊。
1、等待事件相關參數
timed_statistics
指定了在搜集資料庫統計資訊時,與時間相關的統計資訊是否會被收集。該參數影響等待事件中時間相關的值,且受制于statistics_level。
statistics_level
指定了收集資料庫以及作業系統統計資訊的級别。
該參數值的範圍STATISTICS_LEVEL = { ALL | TYPICAL | BASIC },可修改級别ALTER SESSION, ALTER SYSTEM
預設情況下為TYPICAL
2、等待事件的相關視圖
可以從dict資料庫字典中獲得目前資料庫的與等待事件相關的視圖,如使用如下SQL來獲得:
SELECT *
FROM dict
WHERE table_name LIKE '%V$EVENT%' OR table_name LIKE '%V$%WAIT%';
幾個重要的等待事件視圖
下面這三個視圖提供了不同粒度級的等待事件統計和計時資訊。
v$SESSION_WAIT(最低粒度的等待事件)
v$SESSION_EVENT(該視圖提供上一視圖等待事件的彙總)
v$SYSTEM_EVENT(該視圖提供自執行個體啟動以來所有等待事件的彙總)
幾個視圖的粒度關系
V$SESSION_WAIT ⊂ v$SESSION_EVENT ⊂ v$SYSTEM_EVENT
下面将逐一描述這些視圖
3、視圖v$event_name
該視圖列出了目前Oracle版本中所有等待事件的描述資訊。如等待事件的ID,名字,參數,類别等等
SELECT COUNT (*) FROM v$event_name;
SELECT wait_class, COUNT (*)
FROM v$event_name
GROUP BY wait_class
ORDER BY 2;
4、視圖v$session_wait
該視圖顯示的基于會話級的等待資訊,為每個會話目前正在等待的事件或資源提供詳細資訊。
也就是說在視圖v$session_wait中,每一個連接配接到的執行個體的session都對應一行活動的或不活動的資訊。
視圖的p1,p2,p3辨別了對應的session正在等待的具體資源,p1text,p2text,p3text則是對p1,p2,p3的文字描述。
從Oracle 10g R1啟,該視圖的所有資訊可以直接從v$session獲得。
1)、state字段有四種含義﹕
a、Waiting:目前SESSION正等待這個事件。SECONDS_IN_WAIT表示已經等待的時間。
b、Waited unknown time:參數timed_statistics值為false時,無法确定等待時間。
c、Wait short time:表示發生了等待,且低于1厘秒,此情形下wait_time列顯示為-1。
d、Waited known time:如果session等待然後得到了所需資源,那麼将從waiting進入本狀态,并且時間發送給wait_time列
2)、wait_time值也有四種含義:
a、值>0:最後一次等待時間(機關:厘秒),目前未在等待狀态。
b、值=0:session正在等待目前的事件。
c、值=-1:最後一次等待時間小于1個統計機關,目前未在等待狀态。
d、值=-2:時間統計狀态未置為可用,目前未在等待狀态。
3)、wait_time和seconds_in_wait字段值與state相關:
a、如果state值為Waiting,則wait_time值無效。seconds_in_wait值為實際的等待時間(機關:秒)。
b、如果state值為Wait unknow time或者Wait short time,那麼wait_time值和Seconds_in_wait值都無效。
c、如果state值為Waiting known time,則wait_time值就是實際等待時間(機關:秒),seconds_in_wait值無效。
4)、指定session目前的等待事件
SELECT *
FROM v$session_wait
WHERE sid = &input_sid;
5、視圖v$session_event
該視圖記錄了每個session的每一個等待事件的總等待時間,已等待時間以及最大等待時間。
也就是說該視圖是對每個session自啟動以來所經曆的所有等待的彙總。
v$session_event視圖的SID列提供了基于sid來獲得等待事件資訊。
該視圖相當于v$system_event的一個子集,兩者都提供基于等待時間的統計資訊
其差異是v$session_event提供的是會話級的統計資訊,v$system_event提供的是執行個體級别統計資訊
SQL> desc v$session_event;
Name Type Nullable Default Comments
----------------- ------------ -------- ------- --------
SID NUMBER Y
EVENT VARCHAR2(64) Y 事件名稱
TOTAL_WAITS NUMBER Y 目前事件總等待次數
TOTAL_TIMEOUTS NUMBER Y 自會話初次等待之後未能成功獲得所請求資源的次數
TIME_WAITED NUMBER Y 等待該事件所耗用的總時間(百分之一秒)
AVERAGE_WAIT NUMBER Y 平均等待時間(重要參考列,百分之一秒)
MAX_WAIT NUMBER Y 最大等待時間(百分之一秒)
TIME_WAITED_MICRO NUMBER Y
EVENT_ID NUMBER Y
WAIT_CLASS_ID NUMBER Y
WAIT_CLASS# NUMBER Y
WAIT_CLASS VARCHAR2(64) Y
6、視圖v$system_event
顯示自執行個體啟動後所有Oracle會話遇到的所有等待時間的總和,包括等待的總次數,總逾時,是所有會話的在所有等待事件上的彙總。
SQL> desc v$system_event;
EVENT VARCHAR2(64) Y
TOTAL_WAITS NUMBER Y
TOTAL_TIMEOUTS NUMBER Y
TIME_WAITED NUMBER Y
AVERAGE_WAIT NUMBER Y
7、示範等待事件
8、示範中使用到的腳本
9、小結
a、參數timed_statistics與statistics_level影響等待事件時間片的收集
b、v$event_name資料庫字典(此處不是動态視圖)提供了所有等待事件相關的名稱,類别,ID等
c、v$session_wait為目前執行個體的session提供目前正在等待的事件或資源(單一事件,一個session對應一條記錄)
d、v$session_event提供了每個session的每一個等待事件的總等待時間,已等待時間以及最大等待時間
e、v$system_event提供的是自執行個體啟動以來所有等待時間的總和,包括等待的總次數,總逾時,是所有會話的在所有等待事件上的彙總。
f、如果使用者說某個session響應比較慢,排除Cpu過度繁忙之外,一定是該session 處于某個等待事件而導緻session變慢。
g、根據上述描述的相關視圖,可以快速定位指定session變慢的真正原因。
更多參考
<a href="http://blog.csdn.net/robinson_0612/archive/2010/12/15/6078622.aspx">PL/SQL --> 遊标</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/12/22/6092066.aspx">PL/SQL --> 隐式遊标(SQL%FOUND)</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/7536926">批量SQL之 FORALL 語句</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/7545597">批量SQL之 BULK COLLECT 子句</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/7372061">PL/SQL 集合的初始化與指派</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/7372061">PL/SQL 聯合數組與嵌套表</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/7387647">PL/SQL 變長數組</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/6084390">PL/SQL --> PL/SQL記錄</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/7422254">SQL tuning 步驟</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/7406672">高效SQL語句必殺技</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/6893477">父遊标、子遊标及共享遊标</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/6904229">綁定變量及其優缺點</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/6866925">dbms_xplan之display_cursor函數的使用</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/6866870">dbms_xplan之display函數的使用</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/6860007">執行計劃中各字段各子產品描述</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/6837771">使用 EXPLAIN PLAN 擷取SQL語句執行計劃</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/6931886">Oracle ROWID</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/7422254">NULL 值與索引(一)</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/7438397">NULL 值與索引(二)</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/6030407">啟用 AUTOTRACE 功能</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/6851973">函數使得索引列失效</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/6923627">Oracle 綁定變量窺探</a>
<a href="http://blog.csdn.net/robinson_0612/article/details/6923670">Oracle 自适應共享遊标</a>