RAC环境中查询不到其他实例执行的SQL

来源：赛迪网    作者：廉子桥

问题：在检查SQL语句状态的时候，RAC环境中的一个实例可能无法查询到另一个实例上所执行的SQL。

解决方法:在下面这个示例中大家可以看到正在运行的SQL：

SQL> SELECT INSTANCE_NUMBER, INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
1 tradedb1

SQL> SELECT SQL_TEXT FROM V$SQL 
2 WHERE HASH_VALUE IN (SELECT SQL_HASH_VALUE FROM V$SESSION WHERE SID = 135);

SQL_TEXT
---------------------------------------
BEGIN dbms_mview.refresh('ORD_HIT_COMM', 'C'); END;

但在以下的示例中却无法查询到结果：

SQL> SELECT INSTANCE_NUMBER, INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
2 tradedb2

SQL> SELECT SQL_TEXT 
2 FROM GV$SQL 
3 WHERE INST_ID = 1
4 AND HASH_VALUE IN 
5 (
6 SELECT SQL_HASH_VALUE 
7 FROM GV$SESSION 
8 WHERE SID = 135 
9 AND INST_ID = 1
10 );

未选定行

相同的语句在第一个示例上可以得到正确的结果：

SQL> SELECT INSTANCE_NUMBER, INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
1 tradedb1

SQL> SELECT SQL_TEXT 
2 FROM GV$SQL A
3 WHERE A.INST_ID = 1
4 AND A.HASH_VALUE IN 
5 (
6 SELECT /*+ NO_MERGE(B) */ SQL_HASH_VALUE 
7 FROM GV$SESSION B
8 WHERE B.SID = 135 
9 AND B.INST_ID = 1
10 );


SQL_TEXT
----------------------------------
BEGIN dbms_mview.refresh('ORD_HIT_COMM', 'C'); END;

而且在实例2上，如果分布执行上面的语句，是可以得到结果的：

SQL> SELECT INSTANCE_NUMBER, INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
2 tradedb2

SQL> SELECT SQL_HASH_VALUE FROM GV$SESSION WHERE SID = 135 AND INST_ID = 1;

SQL_HASH_VALUE
--------------
1300018675

SQL> SELECT SQL_TEXT FROM GV$SQL WHERE HASH_VALUE = '1300018675';

SQL_TEXT
---------------------------------
BEGIN dbms_mview.refresh('ORD_HIT_COMM', 'C'); END;

此处可能是Oracle的执行计划出现了错误，尝试使用RULE提示，结果为正常：

SQL> SELECT /*+ RULE */ SQL_TEXT 
2 FROM GV$SQL A
3 WHERE A.INST_ID = 1
4 AND A.HASH_VALUE IN 
5 (
6 SELECT SQL_HASH_VALUE 
7 FROM GV$SESSION B
8 WHERE B.SID = 135 
9 AND B.INST_ID = 1
10 );

SQL_TEXT
-----------------------------------
BEGIN dbms_mview.refresh('ORD_HIT_COMM', 'C'); END;

如果不用IN的方式，而采用关联的写法，也可以得到正确结果：

SQL> SELECT SQL_TEXT 
2 FROM GV$SQL A, GV$SESSION B
3 WHERE A.INST_ID = 1
4 AND B.INST_ID = 1
5 AND B.SID = 135
6 AND A.HASH_VALUE = B.SQL_HASH_VALUE 
7 ;

SQL_TEXT
---------------------------------
BEGIN dbms_mview.refresh('ORD_HIT_COMM', 'C'); END;

总结

现在我们就可以断定是Oracle在MERGE视图GV$SESSION的时候出现了错误，这个错误是因为没有在METALINK上找到类似的信息。