RAC：在子查询使用gv$视图，有时查询不出数据

今天写一个监控RAC中所有节点的脚本，发现在子查询中使用gv$视图，有时不能查询出数据．

 

环境，实验如下

 

SQL> conn  /  as sysdba;
Connected.
SQL> select * from v$version;
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

5 rows selected.

SQL> select inst_id,instance_name from gv$instance;
INST_ID INSTANCE_NAME
------- ----------------
      1 gxdb1
      2 gxdb2

2 rows selected.

SQL> select (select saddr
  2            from gv$session
  3           where inst_id = s.inst_id
  4             and sid = s.SID) saddr,
  5         s.INST_ID,
  6         s.SID
  7    from gv$session_wait s
  8   where rownum = 1;
SADDR           INST_ID     SID
---------------- ------- -------
                       2    2033

1 row selected.

 

注：SADDR没有数据

SQL> select (select saddr
  2            from gv$session
  3           where inst_id = s.inst_id
  4             and sid = s.SID) saddr,
  5         s.INST_ID,
  6         s.SID
  7    from gv$session_wait s
  8   where s.inst_id = 2
  9     and s.sid = 2033;
SADDR           INST_ID     SID
---------------- ------- -------
0700000CC2CD6D20       2    2033

 

这次有数据了

 

1 row selected.

SQL> select distinct (select saddr
  2                     from gv$session
  3                    where inst_id = s.inst_id
  4                      and sid = s.SID) saddr,
  5                  s.INST_ID,
  6                  s.SID
  7    from gv$session_wait s
  8   where s.inst_id = 2
  9     and s.sid = 2033;
SADDR           INST_ID     SID
---------------- ------- -------
                       2    2033

1 row selected.

 

使用了distinct 又看不到数据了．

 

还有一些其他情况也会看不到数据，不一一例举了．

 

这算不算是Oracle的bug呢．呵呵．．．

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/195110/viewspace-744411/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/195110/viewspace-744411/