总述：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->16 Asg->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->11 Asg->NEW OWNER)
*** 06/25/02 06:43 pm *** (CHG: Asg->NEW OWNER)
*** 06/25/02 06:43 pm ***
*** 07/19/02 02:57 pm *** (CHG: Asg->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->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->80)
*** 08/22/02 07:19 am *** (CHG: Confirmed Flag->Y)
*** 08/22/02 07:19 am *** (CHG: Fixed->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 05:41 am ***
*** 02/26/03 11:41 am ***
*** 03/18/03 08:43 pm ***
*** 03/19/03 05:53 am ***
*** 03/28/03 12:37 pm ***
*** 03/28/03 01:26 pm ***
*** 03/28/03 05:29 pm ***
*** 04/28/03 08:20 pm ***
*** 04/28/03 08:22 pm ***
*** 07/18/03 10:14 am ***
*** 09/02/03 12:58 pm ***
Oracle网站对此问题给出了说明，并在9.2.0.3中修复：
文档 ID: 注释:208105.1
主题: ALERT: SID Values in V$SESSION and V$SESSION_EVENT Do Not Match
类型: ALERT
状态: PUBLISHED
内容类型: TEXT/PLAIN
创建日期: 22-AUG-2002
上次修订日期: 08-APR-2003
ALERT: SID Values in V$SESSION and V$SESSION_EVENT Do Not Match
1Versions Affected
2~~~~~~~~~~~~~~~~~
3This problem is introduced in Oracle Server 9.2.0.1 and is present in 9.2.0.2
4An attempt is made here in this article to increase the visibility of
5[BUG:2429929] which many customers are facing as they move on to Oracle9i
6The fix to this bug is addressed in Oracle Server patchset 9.2.0.3 and above
7Platforms Affected
8~~~~~~~~~~~~~~~~~~
9GENERIC
10Description
11~~~~~~~~~~~
12The V$SESSION_EVENT and GV$SESSION_EVENT views in Oracle Server 9.2.0.1 and
139.2.0.2 will return misleading information as the SID column has incorrect
14value (i.e., V$SESSION_EVENT.SID actually has value V$SESSION.SID - 1)
15Hence, any joins between V$SESSION_EVENT and V$SESSION will return information
16for the wrong session unless V$SESSION.SID - 1 is used in join predicate
17This article is intended for customers who use Oracle's or third party
18provided performance measurement and diagnostics Tools / Scripts / SQL's
19This includes Oracle Enterprise Manager (OEM), UTLBSTAT-ESTAT, STATSPACK, etc
20Likelihood of Occurrence
21~~~~~~~~~~~~~~~~~~~~~~~~
22Customers are very unlikely to be aware of this bug as there is no error
23associated
24Comparing the output for below two SQL's in Oracle Server 9.2.0.1 and 9.2.0.2
25will reveal that the SID value in V$SESSION_EVENT is appearing to be one
26number less than in V$SESSION
27SQL> SELECT DISTINCT SID FROM V$SESSION
28/
29SQL> SELECT DISTINCT SID FROM V$SESSION_EVENT
30/
31Example:
32SQL> SELECT DISTINCT SID FROM V$SESSION;
33SID
34\----------
351
362
373
384
395
406
417
428
439
4410
4513
4632
4712 rows selected
48SQL> SELECT DISTINCT SID FROM V$SESSION_EVENT;
49SID
50\----------
511
522
533
544
555
566
577
588
599
6012 <<<
6131 <<< SID's 12 & 31 doesn't exists in v$session
6211 rows selected
63Possible Symptoms
64~~~~~~~~~~~~~~~~~
65Customers will receive conflicting results when performing a query like the one
66below. This type of query is often run when trying to trace
67sessions using a disproportionate amount of resources
68Finding the SID/SERIAL# of a user showing a particular type of wait:
69SELECT s.sid , s.serial#, s.status, s.server, s.username,
70e.event, e.time_waited
71FROM v$session_event e, v$session s
72WHERE e.sid=s.sid
73AND e.event like '&WAITEVENT_TO_CHECK'
74AND e.time_waited > '&WAIT_TIME_THRESHOLD'
75Workaround
76~~~~~~~~~~
77Use join predicate V$SESSION_EVENT.SID = V$SESSION.SID - 1 in SQL queries
78Patches
79~~~~~~~
80Fix to this bug is addressed in Oracle Server patchset 9.2.0.3 and above
81In order to ensure the highest level of support, Oracle strongly recommends
82you to apply the latest patchset available for your platform
83References
84~~~~~~~~~~
85[BUG:2429929] SID VALUES IN V$SESSION AND V$SESSION_EVENT DOES NOT MATCH
86Rediscovery Information
87~~~~~~~~~~~~~~~~~~~~~~~
88To be seeing this bug the following must be true:
891\. You are on a release that is 9.2.0.1 or 9.2.0.2
902\. V$SESSION_EVENT will have a missing SID when compared to V$SESSION
913\. The wait information is out of sequence. Session 2's waits will be
92reported under session 1, session 3's waits under session 2 and so on.