v$session_event中的SID与v$session不一致的BUG[1]

总述：oracle 9.2.0.1在察看会话等待事件时显示错位。toad等工具也没有修正这一错误，会造成通过session browser察看会话时看到的等待事件及合计实际是其他会话的。
    问题的本质是oracle 9.2.0.1的v$session_event视图中的sid与v$session中的sid相差了1。这一bug在9.2.0.3后修复。在没有升级的情况下可以使用如下语句察看会话的等待事件累计：
    select b.sid,
       decode(b.username, null, substr(b.program, 18), b.username) username,event,
       a.total_waits,
       a.total_timeouts,
       a.time_waited,
       a.average_wait,
       a.max_wait,
       a.time_waited_micro
    from v$session_event a, v$session b
    where b.sid = a.sid + 1
    order by b.sid, a.time_waited desc

原bug说明如下：

bug 号     2429929
已归档     24-jun-2002     已更新     02-sep-2003
产品     oracle server - enterprise edition     产品版本     9.2.0.1.0
平台     hp tru64 unix     平台版本     5.1
数据库版本     9.2.0.1.0     影响平台     generic
优先级     severe loss of service     状态     development to q/a
基本 bug     n/a     修复产品版本     10i
问题陈述:
sid values in v$session and v$session_events does not match
 
*** 06/24/02 04:17 am ***
tar:
----
sms-tar de:2428765.999
problem:
--------
the sid value in v$session_event is appearing to be one number less than in
v$session
example:
sql> select distinct sid from v$session;
sid
----------
1
2
3
4
5
6
7
8
9
10
13
32
12 rows selected
sql> select distinct sid from v$session_event;
sid
----------
1
2
3
4
5
6
7
8
9
12 < < <
31 < < < sid's 12 & 31 doesn't exists in v$session
11 rows selected
diagnostic analysis:
--------------------
v$fixed_view_definition shows there is no change in view definition for
gv$session_event in 9.2 as compared to earlier releases like 9.0.1 and 8.x
workaround:
-----------
none
related bugs:
-------------
none
reproducibility:
----------------
yes
test case:
----------
sql> select distinct sid from v$session;
sql> select distinct sid from v$session_event;
compare both the output
stack trace:
------------
none
supporting information:
-----------------------
24 hour contact information for p1 bugs:
----------------------------------------
dial-in information:
--------------------
impact date:
------------
cannot upgrade production db to 9.2 unless this is fixed, since all
performance
diagnostic tools are affected
*** 06/24/02 10:01 pm *** (chg: sta-&gt;16 asg-&gt;new owner)
*** 06/24/02 11:26 pm ***
*** 06/24/02 11:47 pm ***
*** 06/24/02 11:48 pm ***
*** 06/24/02 11:49 pm *** (chg: sta-&gt;11 asg-&gt;new owner)
*** 06/25/02 06:43 pm *** (chg: asg-&gt;new owner)
*** 06/25/02 06:43 pm ***
*** 07/19/02 02:57 pm *** (chg: asg-&gt;new owner)
*** 07/19/02 02:57 pm ***
should be looked at by the vos owner first
*** 07/22/02 02:19 am *** (chg: devpri-&gt;2)
*** 07/23/02 07:19 am ***
*** 08/12/02 03:33 am ***
*** 08/19/02 02:41 am ***
*** 08/20/02 01:40 am ***
*** 08/21/02 02:20 am ***
*** 08/22/02 07:19 am *** (chg: sta-&gt;80)
*** 08/22/02 07:19 am *** (chg: confirmed flag-&gt;y)
*** 08/22/02 07:19 am *** (chg: fixed-&gt;10i)
*** 08/22/02 07:19 am ***
rediscovery information:
to be seeing this bug the following must be true :
1. you are on a release that is 9.2.0.1 or higher
2. v$session_events will have a missing sid when compared to v$session
3. the wait information is out of sequence. session 2's waits will be
reported under session 1, session 3's waits under session 2 and so on
]] [g]v$session_wait now returns the correct wait information for a specified
]] sessionid
*** 09/17/02 11:16 pm ***
*** 10/22/02 04:26 am ***
backported to 9.2.0.1.99
*** 10/24/02 11:37 am ***
*** 10/25/02 07:00 am ***
*** 10/25/02 07:00 am ***
*** 11/05/02 08:00 am ***
*** 11/07/02 11:29 am ***
*** 11/13/02 07:21 am ***
backported to 9.2.0.3
*** 11/20/02 02:14 pm ***
*** 11/22/02 05:36 am ***
*** 11/22/02 05:36 am ***
*** 11/26/02 07:20 am ***
*** 11/26/02 07:20 am ***
*** 11/27/02 09:14 am ***
*** 11/27/02 09:14 am ***
*** 01/17/03 05:39 am ***
*** 01/17/03