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 的事件的描述最詳細。

Oracle動態效能檢視學習筆記(9)_v$system_event

相關文章