模板
教程
環境
性能
功能
管理
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。這一
總述：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 - 1 is used in join predicate
This article is intended for customers who use Oracle's or third party
provided performance measurement and diagnostics Tools / Scripts / SQL's
This includes Oracle Enterprise Manager (OEM), UTLBSTAT-ESTAT, STATSPACK, etc
Likelihood of Oclearcase/" target="_blank" >ccurrence
~~~~~~~~~~~~~~~~~~~~~~~~
Customers are very unlikely to be aware of this bug as there is no error
associated
Comparing the output for below two SQL's in Oracle Server 9.2.0.1 and 9.2.0.2
will reveal that the SID value in V$SESSION_EVENT is appearing to be one
number less than in V$SESSION
SQL> SELECT DISTINCT SID FROM V$SESSION
/
SQL> SELECT DISTINCT SID FROM V$SESSION_EVENT
/
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
Possible Symptoms
~~~~~~~~~~~~~~~~~
Customers will receive conflicting results when performing a query like the one
below. This type of query is often run when trying to trace
sessions using a disproportionate amount of resources
Finding the SID/SERIAL# of a user showing a particular type of wait:
SELECT s.sid , s.serial#, s.status, s.server, s.username,
e.event, e.time_waited
FROM v$session_event e, v$session s
WHERE e.sid=s.sid
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 patchset 9.2.0.3 and above
In order to ensure the highest level of support, Oracle strongly recommends
you to apply the latest patchset 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.
