v$session_event中的SID与v$session不一致的BUG
减小字体
增大字体总述：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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Versions Affected
~~~~~~~~~~~~~~~~~
This problem is introduced 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 on to Oracle9i
The fix to this bug is addressed in Oracle Server patchset 9.2.0.3 and above
Platforms Affected
~~~~~~~~~~~~~~~~~~
GENERIC
Description
~~~~~~~~~~~
The V$SESSION_EVENT and GV$SESSION_EVENT views in Oracle Server 9.2.0.1 and
9.2.0.2 will return misleading information as the SID column has incorrect
value (i.e., V$SESSION_EVENT.SID actually has value V$SESSION.SID - 1)
Hence, any joins between V$SESSION_EVENT and V$SESSION will return information
for the wrong session unless V$SESSION.SID
问题的本质是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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Versions Affected
~~~~~~~~~~~~~~~~~
This problem is introduced 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 on to Oracle9i
The fix to this bug is addressed in Oracle Server patchset 9.2.0.3 and above
Platforms Affected
~~~~~~~~~~~~~~~~~~
GENERIC
Description
~~~~~~~~~~~
The V$SESSION_EVENT and GV$SESSION_EVENT views in Oracle Server 9.2.0.1 and
9.2.0.2 will return misleading information as the SID column has incorrect
value (i.e., V$SESSION_EVENT.SID actually has value V$SESSION.SID - 1)
Hence, any joins between V$SESSION_EVENT and V$SESSION will return information
for the wrong session unless V$SESSION.SID
