今天写一个监控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博客

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

最新文章