- Oracle OWI等待事件视图(v$session_wait/v$session_event/v$system_event)
- 2013-04-16 10:19:38 作者:robinson_0612
-
Oracle OWI等待事件视图(v$session_wait/v$session_event/v$system_event)通常情况下,用户提交一条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缺省情况下为TYPICAL2、等待事件的相关视图可以从dict数据库字典中获得当前数据库的与等待事件相关的视图,如使用如下SQL来获得:SELECT *FROM dictWHERE 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_nameGROUP BY wait_classORDER 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_waitWHERE 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 YEVENT 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 YEVENT_ID NUMBER YWAIT_CLASS_ID NUMBER YWAIT_CLASS# NUMBER YWAIT_CLASS VARCHAR2(64) Y6、视图v$system_event显示自实例启动后所有Oracle会话遇到的所有等待时间的总和,包括等待的总次数,总超时,是所有会话的在所有等待事件上的汇总。SQL> desc v$system_event;Name Type Nullable Default Comments----------------- ------------ -------- ------- --------EVENT VARCHAR2(64) YTOTAL_WAITS NUMBER YTOTAL_TIMEOUTS NUMBER YTIME_WAITED NUMBER YAVERAGE_WAIT NUMBER YTIME_WAITED_MICRO NUMBER YEVENT_ID NUMBER YWAIT_CLASS_ID NUMBER YWAIT_CLASS# NUMBER YWAIT_CLASS VARCHAR2(64) Y7、演示等待事件[sql]-->查看当前数据库的总session数goex_admin@SYBO2SZ> select count(*) from v$session;COUNT(*)----------20--查看数据库当前的总等待事件数--下面的查询结果与session数的值相等,也即是一个session对应一个空闲或非空闲等待事件goex_admin@SYBO2SZ> select count(*) from v$session_wait;COUNT(*)----------20--开一个session 1 来update emp--session 1scott@SYBO2SZ> update emp set sal=sal+100 where deptno=20;5 rows updated.--开启另外一个session 2来尝试delete emp上的一些记录--session 2此时处于阻塞状态scott@SYBO2SZ> delete from emp where deptno=20;--可以看到当前数据库sid为1067上存在一个enq等待事件(1067为session 2的SID)goex_admin@SYBO2SZ> select sid,event,wait_class,wait_time,seconds_in_wait,state from v$session_wait where sid=1067;SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE------ ----------------------------------- ------------------ ---------- --------------- -------------------1067 enq: TX - row lock contention Application 0 960 WAITING--在sid为1067的session中,当TX锁等待获得资源后,再次查询该session,此时已经变成了空闲等待。goex_admin@SYBO2SZ> select sid,event,wait_class,wait_time,seconds_in_wait,state from v$session_wait where sid=1067;SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE------ ----------------------------------- ------------------ ---------- --------------- -------------------1067 SQL*Net message from client Idle 0 10 WAITING--下面的脚本查询的是基于v$session_event的等待事件goex_admin@SYBO2SZ> @wait_event_by_sidEnter value for sid: 1067Enter value for sid: 1067EVENT TIME_SPENT----------------------------------- ----------enq: TX - row lock contention 95489 -->总等待时间为95489,单位为百分之一秒,比seconds_in_wait更精确CPU used when call started 4--下面查询基于实例级别的等待事件--当输入enq,则所有enq相关的等待记录会列出goex_admin@SYBO2SZ> @wait_event_inst_lvlEnter value for input_event_name: enqEnter value for input_event_name: enqWAIT_CLASS EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT STARTUP_TIME-------------------- ------------------------------ ------------ -------------- ----------- ------------- -----------------Application enq: RO - fast object reuse 99 1 393 4 20130410 20:22:43Application enq: TX - row lock contention 382 379 111,476 292 20130410 20:22:43Concurrency enq: TX - index contention 3 0 28 9 20130410 20:22:43Configuration enq: HW - contention 27 1 592 22 20130410 20:22:43Other latch: enqueue hash chains 5 0 6 1 20130410 20:22:43Other enq: FB - contention 1 0 10 10 20130410 20:22:43Other enq: TX - contention 4 0 158 39 20130410 20:22:43Other enq: CF - contention 6 5 1,556 259 20130410 20:22:438 rows selected.--当输入ALL时,该实例的所有等待事件被列出goex_admin@SYBO2SZ> @wait_event_inst_lvlEnter value for input_event_name: ALLEnter value for input_event_name: ALLWAIT_CLASS EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT STARTUP_TIME-------------------- ------------------------------ ------------ -------------- ----------- ------------- -----------------Application SQL*Net break/reset to client 99 0 1 0 20130410 20:22:43Application enq: RO - fast object reuse 99 1 393 4 20130410 20:22:43Application enq: TX - row lock contention 382 379 111,476 292 20130410 20:22:43Commit log file sync 33,165 3,788 798,189 24 20130410 20:22:43Concurrency latch: library cache pin 5 0 0 0 20130410 20:22:43Concurrency latch: library cache lock 5 0 15 3 20130410 20:22:43Concurrency enq: TX - index contention 3 0 28 9 20130410 20:22:43Concurrency cursor: mutex S 6,737 0 29 0 20130410 20:22:43goex_admin@SYBO2SZ> @wait_event_inst_lvlEnter value for input_event_name: ALLEnter value for input_event_name: ALLWAIT_CLASS EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT STARTUP_TIME-------------------- ------------------------------ ------------ -------------- ----------- ------------- -----------------Application SQL*Net break/reset to client 99 0 1 0 20130410 20:22:43Application enq: RO - fast object reuse 99 1 393 4 20130410 20:22:43Application enq: TX - row lock contention 382 379 111,476 292 20130410 20:22:43Commit log file sync 33,165 3,788 798,189 24 20130410 20:22:43......................Concurrency latch: library cache pin 5 0 0 0 20130410 20:22:43Concurrency latch: library cache lock 5 0 15 3 20130410 20:22:43Concurrency enq: TX - index contention 3 0 28 9 20130410 20:22:43Concurrency cursor: mutex S 6,737 0 29 0 20130410 20:22:438、演示中使用到的脚本[sql]a、wait_event_by_sid.sqlrobin@SZDB:~/dba_scripts/custom/sql> more wait_event_by_sid.sqlSELECT event, time_waited AS time_spentFROM v$session_eventWHERE sid = &sidAND wait_class<>'Idle'AND event NOT IN('Null event','client message','KXFX: Execution Message Dequeue - Slave','PX Deq: Execution Msg','KXFQ: kxfqdeq - normal deqeue','PX Deq: Table Q Normal','Wait for credit - send blocked','PX Deq Credit: send blkd','Wait for credit - need buffer to send','PX Deq Credit: need buffer','Wait for credit - free buffer','PX Deq Credit: free buffer','parallel query dequeue wait','PX Deque wait','Parallel Query Idle Wait - Slaves','PX Idle Wait','slave wait','dispatcher timer','virtual circuit status','pipe get','rdbms ipc message','rdbms ipc reply','pmon timer','smon timer','PL/SQL lock timer','SQL*Net message from client','SQL*Net message to client','SQL*Net break/reset to client','SQL*Net more data to client','rdbms ipc message','WMON goes to sleep')UNION ALLSELECT b.name, a.VALUEFROM v$sesstat a, v$statname bWHERE a.statisti c# = b.statistic# AND b.name = 'CPU used when call started' AND a.sid = &sid;b、wait_event_inst_lvl.sqlrobin@SZDB:~/dba_scripts/custom/sql> more wait_event_inst_lvl.sqlSET LINES 160SET NUMWIDTH 18COL class FOR a15COL event FOR a30COL total_waits FOR 999,999,999COL total_timeouts FOR 999,999,999COL time_waited FOR 999,999,999,999COL average_wait FOR 999,999,999,999COL event FORMAT a30 WRAPCOL wait_class FORMAT a20SELECT b.wait_class,a.event,total_waits,total_timeouts,time_waited,average_wait,c.startup_timeFROM v$system_event a, v$event_name b, v$instance cWHERE a.event = b.nameAND UPPER (a.event) LIKEDECODE (UPPER ('&input_event_name'),'ALL', UPPER (a.event),UPPER ('%&input_event_name%'))AND b.wait_class<>'Idle'AND a.event NOT IN('Null event','client message','KXFX: Execution Message Dequeue - Slave','PX Deq: Execution Msg','KXFQ: kxfqdeq - normal deqeue','PX Deq: Table Q Normal','Wait for credit - send blocked','PX Deq Credit: send blkd','Wait for credit - need buffer to send','PX Deq Credit: need buffer','Wait for credit - free buffer','PX Deq Credit: free buffer','parallel query dequeue wait','PX Deque wait','Parallel Query Idle Wait - Slaves','PX Idle Wait','slave wait','dispatcher timer','virtual circuit status','pipe get','rdbms ipc message','rdbms ipc reply','pmon timer','smon timer','PL/SQL lock timer','SQL*Net message from client','SQL*Net message to client','SQL*Net break/reset to client','SQL*Net more data to client','rdbms ipc message','WMON goes to sleep')ORDER BY b.wait_class, a.time_waited;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变慢的真正原因。