Oracle10g RAC环境下的bug。

在10g的RAC环境下，如果访问GV$SQL视图，可能会导致ORA-600错误：

SQL> select * from gv$sql where hash_value in (select sql_hash_value from gv$session where sid = 287);
ERROR:
ORA-00600: 内部错误代码, 参数: [kolaslGetLength-1], [], [], [], [], [], [], []

进一步定位错误，发现访问远端节点的记录时报错：

SQL> set autot trace stat
SQL> select * from gv$sql where inst_id = 1;

已选择1946行。

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
2642293 bytes sent via SQL*Net to client
1309466 bytes received via SQL*Net from client
8124 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1946 rows processed

SQL> select * from gv$sql where inst_id = 2;
ERROR:
ORA-00600: 内部错误代码, 参数: [kolaslGetLength-1], [], [], [], [], [], [], []

已选择15行。

统计信息
----------------------------------------------------------
21 recursive calls
3 db block gets
1 consistent gets
0 physical reads
672 redo size
26632 bytes sent via SQL*Net to client
11087 bytes received via SQL*Net from client
67 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
15 rows processed

SQL> set autot off
SQL> select instance_number from v$instance;

INSTANCE_NUMBER
---------------
1

如果查询COUNT(*)并不会出错：

SQL> select count(*) from gv$sql;

COUNT(*)
----------
4981

检查一下gv$sql的结构：

SQL> desc gv$sql
名称 是否为空? 类型
----------------------------------- -------- --------------------------------------------
INST_ID NUMBER
SQL_TEXT VARCHAR2(1000)
SQL_FULLTEXT CLOB
SQL_ID VARCHAR2(13)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
PX_SERVERS_EXECUTIONS NUMBER
END_OF_FETCH_COUNT NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(38)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
BUFFER_GETS NUMBER
APPLICATION_WAIT_TIME NUMBER
CONCURRENCY_WAIT_TIME NUMBER
CLUSTER_WAIT_TIME NUMBER
USER_IO_WAIT_TIME NUMBER
PLSQL_EXEC_TIME NUMBER
JAVA_EXEC_TIME NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(10)
OPTIMIZER_COST NUMBER
OPTIMIZER_ENV RAW(839)
OPTIMIZER_ENV_HASH_VALUE NUMBER
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
PARSING_SCHEMA_NAME VARCHAR2(30)
KEPT_VERSIONS NUMBER
ADDRESS RAW(8)
TYPE_CHK_HEAP RAW(8)
HASH_VALUE NUMBER
OLD_HASH_VALUE NUMBER
PLAN_HASH_VALUE NUMBER
CHILD_NUMBER NUMBER
SERVICE VARCHAR2(64)
SERVICE_HASH NUMBER
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
OUTLINE_CATEGORY VARCHAR2(64)
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
OUTLINE_SID NUMBER
CHILD_ADDRESS RAW(8)
SQLTYPE NUMBER
REMOTE VARCHAR2(1)
OBJECT_STATUS VARCHAR2(19)
LITERAL_HASH_VALUE NUMBER
LAST_LOAD_TIME VARCHAR2(38)
IS_OBSOLETE VARCHAR2(1)
CHILD_LATCH NUMBER
SQL_PROFILE VARCHAR2(64)
PROGRAM_ID NUMBER
PROGRAM_LINE# NUMBER
EXACT_MATCHING_SIGNATURE NUMBER
FORCE_MATCHING_SIGNATURE NUMBER
LAST_ACTIVE_TIME DATE
BIND_DATA RAW(2000)

SQL> set autot trace stat
SQL> select sql_fulltext from gv$sql;
ERROR:
ORA-00600: 内部错误代码, 参数: [kolaslGetLength-1], [], [], [], [], [], [], []

已选择26行。

统计信息
----------------------------------------------------------
21 recursive calls
5 db block gets
1 consistent gets
0 physical reads
672 redo size
25282 bytes sent via SQL*Net to client
18080 bytes received via SQL*Net from client
110 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
26 rows processed

发现包含一个CLOB字段，测试这个字段，果然出现问题。

看来，是在访问远端CLOB字段时出现的问题，想要避免这个错误就很简单了，在访问GV$SQL的时候不要读取这个字段就可以了。

SQL> select
2 inst_id,
3 sql_text,
4 sql_id,
5 sharable_mem,
6 persistent_mem,
7 runtime_mem,
8 sorts,
9 loaded_versions,
10 open_versions,
11 users_opening,
12 fetches,
13 executions,
14 px_servers_executions,
15 end_of_fetch_count,
16 users_executing,
17 loads,
18 first_load_time,
19 invalidations,
20 parse_calls,
21 disk_reads,
22 direct_writes,
23 buffer_gets,
24 application_wait_time,
25 concurrency_wait_time,
26 cluster_wait_time,
27 user_io_wait_time,
28 plsql_exec_time,
29 java_exec_time,
30 rows_processed,
31 command_type,
32 optimizer_mode,
33 optimizer_cost,
34 optimizer_env,
35 optimizer_env_hash_value,
36 parsing_user_id,
37 parsing_schema_id,
38 parsing_schema_name,
39 kept_versions,
40 address,
41 type_chk_heap,
42 hash_value,
43 old_hash_value,
44 plan_hash_value,
45 child_number,
46 service,
47 service_hash,
48 module,
49 module_hash,
50 action,
51 action_hash,
52 serializable_aborts,
53 outline_category,
54 cpu_time,
55 elapsed_time,
56 outline_sid,
57 child_address,
58 sqltype,
59 remote,
60 object_status,
61 literal_hash_value,
62 last_load_time,
63 is_obsolete,
64 child_latch,
65 sql_profile,
66 program_id,
67 program_line#,
68 exact_matching_signature,
69 force_matching_signature,
70 last_active_time,
71 bind_data
72 from gv$sql;

已选择4833行。

统计信息
----------------------------------------------------------
47 recursive calls
3 db block gets
3 consistent gets
0 physical reads
700 redo size
2476262 bytes sent via SQL*Net to client
4034 bytes received via SQL*Net from client
324 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4833 rows processed

将SQL_FULLTEXT字段去掉，发现不再报错。

查询了一个metalink，发现果然是Oracle的bug，oracle在Doc ID: Note:357016.1和Doc ID: Note:4634662.8里面有一些简单的描述，不过Oracle并没有详细的错误的原因。Oracle给出的有价值的信息包括BUG号和PATCH号：4634662，并说明这个bug可能会影响所有10g的RAC版本。Oracle将在11g和10.2.0.4中解决这个问题。