总述:Oracle 9.2.0.1在察看会话等待事件时显示错位TOAD等工具也没有修正这会造成通过session browser察看会话时看到等待事件及合计实际是其他会话
问题本质是Oracle 9.2.0.1v$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 _disibledevent=>


*** 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网站WebSite对此问题给出了介绍说明并在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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Versions Affected
~~~~~~~~~~~~~~~~~
This problem is roduced in Oracle Server 9.2.0.1 and is present in 9.2.0.2
An attempt is made here in this article to increase the visibility of
[BUG:2429929] which many customers are facing as they move _disibledevent=>AND e.event like \'&WAITEVENT_TO_CHECK\'
AND e.time_waited > \'&WAIT_TIME_THRESHOLD\'
Workaround
~~~~~~~~~~
Use join predicate V$SESSION_EVENT.SID = V$SESSION.SID - 1 in SQL queries
Patches
~~~~~~~
Fix to this bug is addressed in Oracle Server patch 9.2.0.3 and above
In order to ensure the highest level of support, Oracle strongly recommends
you to apply the latest patch available for your platform
References
~~~~~~~~~~
[BUG:2429929] SID VALUES IN V$SESSION AND V$SESSION_EVENT DOES NOT MATCH
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 9.2.0.2
2. V$SESSION_EVENT 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.