ORA-600(kolaslGetLength-1)錯誤

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中解決這個問題。