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       缺省情况下为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;     Name              Type         Nullable Default Comments     ----------------- ------------ -------- ------- --------     EVENT             VARCHAR2(64) Y                             TOTAL_WAITS       NUMBER       Y                             TOTAL_TIMEOUTS    NUMBER       Y                             TIME_WAITED       NUMBER       Y                             AVERAGE_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 

7、演示等待事件

-->查看当前数据库的总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 1
scott@SYBO2SZ> update emp set sal=sal+100 where deptno=20;

5 rows updated.

--开启另外一个session 2来尝试delete emp上的一些记录     Author: Robinson
--session 2此时处于阻塞状态                             Blog  : http://blog.csdn.net/robinson_0612    
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_sid
Enter value for sid: 1067
Enter value for sid: 1067

EVENT                               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_lvl
Enter value for input_event_name: enq
Enter value for input_event_name: enq

WAIT_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:43
Application          enq: TX - row lock contention           382            379     111,476           292 20130410 20:22:43
Concurrency          enq: TX - index contention                3              0          28             9 20130410 20:22:43
Configuration        enq: HW - contention                     27              1         592            22 20130410 20:22:43
Other                latch: enqueue hash chains                5              0           6             1 20130410 20:22:43
Other                enq: FB - contention                      1              0          10            10 20130410 20:22:43
Other                enq: TX - contention                      4              0         158            39 20130410 20:22:43
Other                enq: CF - contention                      6              5       1,556           259 20130410 20:22:43

8 rows selected.

--当输入ALL时,该实例的所有等待事件被列出
goex_admin@SYBO2SZ> @wait_event_inst_lvl
Enter value for input_event_name: ALL
Enter value for input_event_name: ALL

WAIT_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:43
Application          enq: RO - fast object reuse              99              1         393             4 20130410 20:22:43
Application          enq: TX - row lock contention           382            379     111,476           292 20130410 20:22:43
Commit               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:43
Concurrency          latch: library cache lock                 5              0          15             3 20130410 20:22:43
Concurrency          enq: TX - index contention                3              0          28             9 20130410 20:22:43
Concurrency          cursor: mutex S                       6,737              0          29             0 20130410 20:22:43

goex_admin@SYBO2SZ> @wait_event_inst_lvl
Enter value for input_event_name: ALL
Enter value for input_event_name: ALL

WAIT_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:43
Application          enq: RO - fast object reuse              99              1         393             4 20130410 20:22:43
Application          enq: TX - row lock contention           382            379     111,476           292 20130410 20:22:43
Commit               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:43
Concurrency          latch: library cache lock                 5              0          15             3 20130410 20:22:43
Concurrency          enq: TX - index contention                3              0          28             9 20130410 20:22:43
Concurrency          cursor: mutex S                       6,737              0          29             0 20130410 20:22:43

8、演示中使用到的脚本

a、wait_event_by_sid.sql
robin@SZDB:~/dba_scripts/custom/sql> more wait_event_by_sid.sql
SELECT event, time_waited AS time_spent
  FROM v$session_event
 WHERE sid = &sid
       AND 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 ALL
SELECT b.name, a.VALUE
  FROM v$sesstat a, v$statname b
 WHERE a.statistic# = b.statistic# AND b.name = 'CPU used when call started' AND a.sid = &sid;

b、wait_event_inst_lvl.sql
robin@SZDB:~/dba_scripts/custom/sql> more wait_event_inst_lvl.sql
SET LINES 160
SET NUMWIDTH 18
COL class FOR a15
COL event FOR a30
COL total_waits FOR 999,999,999
COL total_timeouts FOR 999,999,999
COL time_waited FOR 999,999,999,999
COL average_wait FOR 999,999,999,999
COL event FORMAT a30 WRAP
COL wait_class FORMAT a20

  SELECT b.wait_class,
         a.event,
         total_waits,
         total_timeouts,
         time_waited,
         average_wait,
         c.startup_time
    FROM v$system_event a, v$event_name b, v$instance c
   WHERE a.event = b.name
         AND UPPER (a.event) LIKE
                DECODE (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变慢的真正原因。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏王硕

原 在PostgreSQL中秒级完成大表添加带有not null属性并带有default值的实验

652130
来自专栏沃趣科技

ASM 翻译系列第十八弹:ASM Internal ASM file number 5

原作者:Bane Radulovic 译者: 魏兴华 审核: 魏兴华 ASM file number 5 本章讲述ASM的5号文件,5号文件是ASM...

36360
来自专栏杨建荣的学习笔记

关于Flashback的小测试(r10笔记第15天)

对于Oracle的Flashback来说,在11g里面有了一个很细微的变化,可以说是一个很不错的福利,那就是开启闪回不需要重启数据库至mount状态下,...

34340
来自专栏乐沙弥的世界

Oracle 基于用户管理恢复的处理

Oracle支持多种方式来管理数据文件的备份与恢复来保证数据库的可靠与完整。除了使用RMAN工具以及第三方备份与恢复工具之外,基于

6520
来自专栏C++

Windows核心编程:第4章 进程

14530
来自专栏乐沙弥的世界

启用用户进程跟踪

仅仅需要标识该会话并为该会话启用跟踪(专用模式为一对一模式,即一个用户进程对应一个服务器进程)

9720
来自专栏乐沙弥的世界

Linux/Unix shell 监控Oracle实例(monitor instance)

    使用shell脚本实现对Oracle数据库的监控与管理将大大简化DBA的工作负担,如常见的对实例的监控,监听的监控,告警日志的监控,以及数据库的备份,A...

10010
来自专栏Golang语言社区

【Go 语言社区】Go实现个留言本

一、分析 要实现这个下面是几个重要的东西。 1.数据库操作(go怎么操作数据库)、 2.输出js,css,图片等 3.html显示 ...

37690
来自专栏乐沙弥的世界

RAC 环境下修改归档模式

    RAC环境下的归档模式切换与单实例稍有不同,主要是共享存储所产生的差异。在这种情况下,我们可以将RAC数据库切换到非集群状态下,仅仅在一个实例上来实施归...

10820
来自专栏杨建荣的学习笔记

Oracle 12cR2中的ADG会话保留特性

Oracle 12cR2中有一个不错的特性,那就是Active Data Guard会话保留,原本的叫法是Preserving Active Data Guar...

30450

扫码关注云+社区

领取腾讯云代金券