分享程序员开发的那些事...
更新时间：2022-08-18 10:37:33
通常情况下，用户提交一条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变慢的真正原因。
更多参考
PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录
dbms_xplan之display_cursor函数的使用
Oracle 表空间与数据文件
Oracle 密码文件
Oracle 参数文件
Oracle 联机重做日志文件(ONLINE LOG FILE)
Oracle 控制文件(CONTROLFILE)
Oracle 归档日志
Oracle 回滚(ROLLBACK)和撤销(UNDO)
Oracle 数据库实例启动关闭过程
Oracle 10g SGA 的自动化管理
Oracle 实例和Oracle数据库(Oracle体系结构)