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