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

(OWI学习笔记索引)

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博客 ” ，链接：http://blog.itpub.net/55472/viewspace-425555/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/55472/viewspace-425555/