V$SYSTEM_EVENT

SQL> desc v$system_event

Name Type

-------------------- ---------------------

EVENT VARCHAR2(64)

TOTAL_WAITS NUMBER 等待总次数

TOTAL_TIMEOUTS NUMBER

TIME_WAITED NUMBER 等待总时间=time_waited_micro/10000

AVERAGE_WAIT NUMBER =time_waited/total_waits

TIME_WAITED_MICRO NUMBER (单位：microseconds,1/1,000,000秒)

*EVENT_ID NUMBER

*WAIT_CLASS_ID NUMBER

*WAIT_CLASS# NUMBER

*WAIT_CLASS VARCHAR2(64)

有些event有timeout属性（哪里可以查询到？v$session_wait中的P1-3?），例如free buffer waits有一个Timeout的时间为100centisecond，当一个session处于free buffer waits事件超过100centisecond后，total_timeouts列会+1，同时，该Session开始第二个free buffer waits等待。而有一些event，特别是I/O相关的，都没有timeout的属性，这意味着session会一直处于等待状态知道获取所需要的资源。

V$system_event使用时注意：

l V$system_event提供的是instance级的事件统计信息，没有session级的信息；instance启动的时间越长，其累计的time_waited列值越大；

l 我们需要关注time_waited列，而非total_waits列。有些event如latch free会有非常高的total_waits, 但是每一次的等待时间都非常短，而有些event如enqueue虽然total_waits很小，但是每一次的等待时间却很长；

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

select b.wait_class, a.*, c.startup_time

from v$system_event a,

v$event_name b,

v$instance c

where a.event = b.name

order by b.wait_class, a.time_waited desc;

WAIT_CLASS EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT STARTUP_TIME

User I/O db file sequential read 195100 50336 0.26 2008-7-16 11:17:16

User I/O db file scattered read 32096 16345 0.51 2008-7-16 11:17:16

特别注意和User I/O及System I/O相关的event，这些event反映了你系统的速度和IO Cost。

SQL> SELECT NAME FROM V$EVENT_NAME

2 WHERE WAIT_CLASS='User I/O' or WAIT_CLASS='System I/O';

获取阶段数据：可以使用statspack获取，也可以通过脚本来获取：

-- Assumption is that you have TOOLS tablespace in your database.
-- Create Begin and End tables to store V$SYSTEM_EVENT contents for
-- time T1 and T2 to compute delta.
-- ===================================
-- You only need to create these tables once.
-- ===================================
create table begin_system_event tablespace tools

as select * from   v$system_event where 1=2;

create table end_system_event tablespace tools
as select * from   v$system_event where 1=2;

-- Take a snapshot of V$SYSTEM_EVENT information at time T1
truncate table begin_system_event;
insert into begin_system_event
select * from   v$system_event;

-- Wait n seconds or n minutes, and then take another snapshot
-- of V$SYSTEM_EVENT at time T2
truncate table end_system_event;
insert into end_system_event
select * from   v$system_event;

-- Report the ‘delta’ numbers for wait events between times T2 and T1
select t1.event,
       (t2.total_waits - nvl(t1.total_waits,0)) "Delta_Waits",
       (t2.total_timeouts - nvl(t1.total_timeouts,0)) "Delta_Timeouts",
       (t2.time_waited - nvl(t1.time_waited,0)) "Delta_Time_Waited"
from   begin_system_event t1,
       end_system_event t2
where t2.event = t1.event(+)
order by (t2.time_waited - nvl(t1.time_waited,0)) desc;

ITPub博客

Oracle Wait Interface学习笔记(2) OWI Componets (3)

最新文章