性能诊断与调优之V$--V$SESSION_EVENT

1、V$SESSION_EVENT View
The V$SESSION_EVENT view contains aggregated wait event statistics by session for all sessions that are currently connected to the instance. This view contains all the columns present in the
V$SYSTEM_EVENT view and has the same meaning, but the context is session-level. It keeps track of the total waits, time waited, and maximum wait time of each wait event by session. The SID
column identifies individual sessions. The maximum wait time per event per session is tracked in MAX_WAIT column. You can get more information about the session and user by joining

V$SESSION_EVENT with V$SESSION using the SID column. 

V$SESSION_EVENT跟V$SYSTEM_EVENT类似，V$SESSION_EVENT记录了会话级的事件信息，里面加了区分session 的SID列，可能更进一步的通过SID列与V$SESSION相关联找出会话相关的所有信息。

V$SESSION_EVENT view 包括 如下 列：

SQL> desc V$SESSION_EVENT
Name              Type         Nullable Default Comments 
----------------- ------------ -------- ------- -------- 
SID               NUMBER       Y                         
EVENT             VARCHAR2(64) Y                         
TOTAL_WAITS       NUMBER       Y                         
TOTAL_TIMEOUTS    NUMBER       Y                         
TIME_WAITED       NUMBER       Y                         
AVERAGE_WAIT      NUMBER       Y                         
MAX_WAIT          NUMBER       Y                  Starting Oracle8       
TIME_WAITED_MICRO NUMBER       Y                  Starting Oracle9i       
EVENT_ID          NUMBER       Y                  Starting Oracle10g       
WAIT_CLASS_ID     NUMBER       Y                         
WAIT_CLASS#       NUMBER       Y                         
WAIT_CLASS        VARCHAR2(64) Y   

2、How to Use V$SESSION_EVENT View
The V$SESSION_EVENT view is useful when you know the SID of the session that is currently connected to the instance. Let’s say you get a call about a job that is running very slowly. You ask
for the USERNAME and find the SID from the V$SESSION view. (Don’t ask your user for the SID; you should be thankful if they can give you the USERNAME. Finding the exact SID can still be a
challenge if your application connects with a common USERNAME.) You then query the V$SESSION_EVENT view for the particular SID and order the result by the TIME_WAITED
column. You can easily pick out the bottlenecks that may be contributing to poor performance. Finding the major bottlenecks that are slowing a session down is only the first step in OWI

performance tuning. The next step is to determine the root cause, which can be rather difficult using data from the V$SESSION_EVENT view for two main reasons:

当某个用户反映在进行一项任务时反映特别慢，你可以根据他的用户名到V$SESSION中找到对应的SID,根据这个SID再到V$SESSION_EVENT找出所有的记录根据TIME_WAITED列排序，从而找出

性能瓶颈。不过找出主要的瓶颈仅是使用OWI进行性能调优的第一步，下面这两点说明了仅用V$SESSION_EVENT的数据确定瓶颈的根本原因是相当困难：

•  The V$SESSION_EVENT view does not keep track of the SQL statements that experience the bottlenecks. Without correlating bottlenecks to the SQL statements, you may not be able to

               minimize the bottlenecks even if you discover the worst bottleneck for a session. For example, you may discover that the db file scattered read wait event is the major performance bottleneck,

               but without capturing the SQL statement that performs the full table scans, you’ll have very little to offer to your unhappy customers.

               V$SESSION_EVENT视图中不包括SQL语句的跟踪信息，没有这些SQL语句的跟踪信息就不能精确的找出造成瓶颈的原因。例如。。。。

• It is hard to determine the root cause of a performance problem if you don’t have enough information about its symptom. For example, all latch waits are rolled up into the latch free wait event
  and you cannot tell which one of the several hundred latches the session waited on the most. However, in Oracle Database 10g Release 1 a handful of the latches are broken out  their individual wait events.

              该视图中也不包括足够多的性能症状信息，没有这些信息也就不确定造成问题的根源。例如。。。。         

As in all dynamic performance views, the session-level data of each session persists through the life of the session. Oracle does not keep a history for session-level wait event information. However, the

V$ACTIVE_SESSION_HISTORY view in Oracle Database10g Release 1 keeps about half an hour of session-level wait event history in memory that was sampled every second.

跟其他动态性能视图类似，在会话的生命周期内，Oracle不会永久的保存这些事件信息，从Oracle 10g release 1 版本开始这些历史信息将保存半个小时，每秒采样一次。