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/