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

原创 2005年05月20日 12:12:00
总述: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 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 Occurrence
~~~~~~~~~~~~~~~~~~~~~~~~
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.

ORACLE sid,pid,spid和v$session中的saddr,paddr和taddr总结

概念上: 1.spid (system process id)  是操作系统层面的进程id . 2.pid(process id)  这个是基于oracle的进程id个人理解为就是oracle给自己的...

性能诊断与调优之V$--V$SESSION_EVENT

1、V$SESSION_EVENT View The V$SESSION_EVENT view contains aggregated wait event statistics by sessio...

使用SpringSession时cookies跨域导致Session不一致问题的解决

SpringSession默认使用Cookies保存和传递SessionId,在单WebApp情况下,SessionId可以正常传递,但在多WebApp时SessionId则无法正常保存和传递,仍然会...

采用nginx_upstream_jvm_route模块解决tomcat多节点session不一致问题

这种方式不需要修改web工程只需要对nginx下载nginx_upstream_jvm_route插件,修改tomcat和nginx配置,就能解决session问题。由于这种方式不会把session存...

上传组件 uploadify 导致在firefox下 session 不一致问题 ie正常

纠结了一天的问题 用的是strtus1.2 上传的action和其他action取到的session不一样 其中一个方法是上传 得到的request对象是MultipartRequestWrap...

JSP验证码与session不一致处理方法

今天在调试项目的时候发现,在提交表单的时候的验证码有问题,问题是这样的:就是通过debug模式查看得知:jsp页面生成的验证码和表单输入的页面输入的一样,但是到后台执行的时候,你会发现他们是不一样的,...
  • ilvest
  • ilvest
  • 2017年03月22日 19:44
  • 445

jsp中生成的验证码和存在session里面的验证码不一致的处理

转自迷彩风情 今天在调试项目的时候发现,在提交表单的时候的验证码有问题,问题是这样的:就是通过debug模式查看得知:jsp页面生成的验证码和表单输入的页面输入的一样,但是到后台执行的时候,你会...

android.app.Fragment与android.support.v4.app.Fragment不一致

android.app.Fragment与android.support.v4.app.Fragment不一致的问题导致的错误解决方法

最常用的性能视图——v$system_event、v$session_event(3)!

v$system_event: v$system_event 提供了自实例启动后各个等待事件的概括。 SQL> desc v$system_event Name ...

V$SESSION_WAIT和V$SESSION_EVENT和Wait Events描述

V$SESSION_WAIT    这是一个寻找性能瓶颈的关键视图。它提供了任何情况下session在数据库中当前正在等待什么(如果session当前什么也没在做,则显示它最后的等待事件)。当系统存...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:v$session_event中的SID与v$session不一致的BUG
举报原因:
原因补充:

(最多只允许输入30个字)