Oracle动态性能视图学习笔记(9)_v$system_event

目录

参考文档<>Chapter 24

##################################################################
1 Overview
##################################################################
This view is a summary of waits for an event by an instance.
While v$session_wait shows the current waits on the system,
v$system_event provides a summary of all the event waits on the instance since it started.
It is useful to get a historical picture of waits on the system. By taking two snapshots and doing the delta on the

waits,
you can determine the waits on the system in a givent time interval.

##################################################################
2 Userful Columns for v$session
##################################################################
1) Event: Name of the wait event

2) Total_Waits: Total number of waits for this event

3) Time_waited: Total time waited for this event(In hundredths of a second)

4) Average_wait: Average amount of time waited for this event by this session(In hundredths of a second)

5) Total_timeouts: Number of times the wait timed out

##################################################################
3 示例
##################################################################
1)Finding the Total Waits on the System

SELECT event, total_waits waits, total_timeouts timeouts,
time_waited total_time, average_wait avg
FROM V$SYSTEM_EVENT
ORDER BY 4 DESC;

比如，通过checkpoint completed、log file switch（checkpoint incomplete）可以查看检查点进程的性能。通过log file

parallel write、log file switch completed可以查看联机重做日志文件的性能。通过log file switch（archiving needed）事件

可以检查归档进程的性能。

找出瓶颈：
1。通过Statspack列出空闲事件。
2。检查不同事件的等待时间开销。
3。检查每条等待记录的平均用时，因为某些等待事件(比较log file switch completion)可能周期性地发生，但发生时却造成了严

重的性能损耗。

##################################################################
4 v$session_wait, v$session_event,v$system_event的差别
##################################################################

1) V$SESSION_WAIT is a current state view. It lists either the event currently being waited for or the event last

waited for on each session
当前会话

2) V$SESSION_EVENT lists the cumulative history of events waited for on each session. After a session exits, the

wait event statistics for that session are
removed from this view.
当前仍然连接的所有会话，当会话退出，数据被移除

3) V$SYSTEM_EVENT lists the events and times waited for by the whole instance (that is, all session wait events data

rolled up) since instance startup.
自系统启动以来的等待事件

Because V$SESSION_WAIT is a current state view, it also contains a finer-granularity of information than

V$SESSION_EVENT or V$SYSTEM_EVENT. It includes additional identifying data for the current event in three parameter

columns: P1, P2, and P3.

For example, V$SESSION_EVENT can show that session 124 (SID=124) had many waits on the db file scattered read event,

but it does not show which file and block number. However, V$SESSION_WAIT shows the file number in P1, the block

number read in P2, and the number of blocks read in P3 (P1 and P2 let you determine for which segments the wait

event is occurring).

V$SESSION_WAIT 的事件的描述最详细。

ITPub博客

Oracle动态性能视图学习笔记(9)_v$system_event

最新文章