插入排序哈希CLUSTER表出现ORA-7445错误
第一次测试排序哈希CLUSTER就碰到ORA-7445错误。不过也难怪，一般新特性的bug都比较多。
数据库版本10.2.0.3 for Windows x86：
SQL> CONN YANGTK/YANGTK
已连接。
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> CREATE CLUSTER C_HASH_SORT
2 (TYPE VARCHAR2(19), CREATED DATE SORT)
3 HASHKEYS 5806 SIZE 100;
簇已创建。
SQL> CREATE TABLE T_HASH_SORT
2 (OBJECT_ID, OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, STATUS)
3 CLUSTER C_HASH_SORT (OBJECT_TYPE, CREATED)
4 AS SELECT OBJECT_ID, OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, STATUS
5 FROM DBA_OBJECTS;
表已创建。
SQL> SELECT COUNT(*) FROM DBA_OBJECTS;
COUNT(*)
----------
71194
SQL> SELECT COUNT(*) FROM T_HASH_SORT;
COUNT(*)
----------
46461
SQL> DELETE T_HASH_SORT;
已删除46461行。
SQL> COMMIT;
提交完成。
SQL> INSERT INTO T_HASH_SORT
2 SELECT OBJECT_ID, OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, STATUS
3 FROM DBA_OBJECTS;
INSERT INTO T_HASH_SORT
*
第 1 行出现错误:
ORA-03113: 通信通道的文件结束
利用CREATE AS SELECT方式创建HASH SORT CLUSTER TABLE后，发现表中的记录数与DBA_OBJECTS中的不符。删除表中记录，执行INSERT语句，出现了ORA-3113错误，后台alert文件中出现了ORA-7445错误：
Errors in file e:\oracle\admin\ytk102\udump\ytk102_ora_4960.trc:
ORA-07445: 出现异常错误: 核心转储 [ACCESS_VIOLATION] [_kdtwrp+174] [PC:0x1D0E8D2] [ADDR:0x0] [UNABLE_TO_READ] []
对应的trace文件中信息如下：
ksedmp: internal or fatal error
ORA-07445: 出现异常错误: 核心转储 [ACCESS_VIOLATION] [_kdtwrp+174] [PC:0x1D0E8D2] [ADDR:0x0] [UNABLE_TO_READ] []
Current SQL statement for this session:
INSERT INTO T_HASH_SORT
SELECT OBJECT_ID, OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, STATUS
FROM DBA_OBJECTS
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
_kdqs_create_queue_ CALLrel _kdtwrp+0 942A1EC
id+1027
_kdqs_enqueue_buf+6 CALLrel _kdqs_create_queue_
87 id+0
_kdqs_enqueue_flush CALLrel _kdqs_enqueue_buf+0 7C5C4C0 32F13ACC 1 942B6F8
+1146 942B894 942B890
_kdtFlushBuf+4982 CALLrel _kdqs_enqueue_flush 7C5C4C0
+0
_insflush+527 CALLrel _kdtFlushBuf+0 7B99D9C
_insrow+480 CALLrel _insflush+0 7B99D9C 0 1 942D0F0 0
_insdrv+2229 CALLrel _insrow+0 7B99D9C 942D0F0 0
_inscovexe+452 CALLrel _insdrv+0 7B99D9C
_insExecStmtExecIni CALL??? 00000000 2A699A74 2A68F50C 942D718
Engine+55
_insexe+349 CALLrel _insExecStmtExecIni 2A699A74 2A68F50C 942D718
Engine+0
_opiexe+22152 CALLrel _insexe+0 2A69986C 942D718
由于这是10g的新功能，存在bug很正常。于是找了一个10.2.0.3版本的数据库，看看是否出现同样的问题：
SQL> CREATE CLUSTER C_HASH_SORT
2 (TYPE VARCHAR2(19), CREATED DATE SORT)
3 HASHKEYS 5806 SIZE 100;
Cluster created.
SQL> CREATE TABLE T_HASH_SORT
2 (OBJECT_ID, OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, STATUS)
3 CLUSTER C_HASH_SORT (OBJECT_TYPE, CREATED)
4 AS SELECT OBJECT_ID, OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, STATUS
5 FROM DBA_OBJECTS;
Table created.
SQL> SELECT COUNT(*) FROM DBA_OBJECTS;
COUNT(*)
----------
71466
SQL> SELECT COUNT(*) FROM T_HASH_SORT;
COUNT(*)
----------
45819
SQL> TRUNCATE TABLE T_HASH_SORT;
TRUNCATE TABLE T_HASH_SORT
*
ERROR at line 1:
ORA-03292: Table to be truncated is part of a cluster
SQL> DELETE T_HASH_SORT;
45819 rows deleted.
SQL> INSERT INTO T_HASH_SORT
2 SELECT OBJECT_ID, OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, STATUS
3 FROM DBA_OBJECTS;
INSERT INTO T_HASH_SORT
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kdqs_get_new_page_reuse_link], [], [], [], [], [], [], []
问题可以重现，不过这次错误信息不同了，变成了ORA-600错误了。
ORA-00600: internal error code, arguments: [kdqs_get_new_page_reuse_link], [], [], [], [], [], [], []
Current SQL statement for this session:
INSERT INTO T_HASH_SORT
SELECT OBJECT_ID, OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, STATUS
FROM DBA_OBJECTS
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+31 call ksedst1() 000000000 ? 000000001 ?
7FBFFF48C0 ? 7FBFFF4920 ?
7FBFFF4860 ? 000000000 ?
ksedmp()+610 call ksedst() 000000000 ? 000000001 ?
7FBFFF48C0 ? 7FBFFF4920 ?
7FBFFF4860 ? 000000000 ?
ksfdmp()+21 call ksedmp() 000000003 ? 000000001 ?
7FBFFF48C0 ? 7FBFFF4920 ?
7FBFFF4860 ? 000000000 ?
kgerinv()+161 call ksfdmp() 000000003 ? 000000001 ?
7FBFFF48C0 ? 7FBFFF4920 ?
7FBFFF4860 ? 000000000 ?
kgeasnmierr()+163 call kgerinv() 006618E20 ? 2A972D1168 ?
7FBFFF4920 ? 7FBFFF4860 ?
000000000 ? 000000000 ?
kdqs_check_page()+4 call kgeasnmierr() 006618E20 ? 2A972D1168 ?
06 7FBFFF4920 ? 7FBFFF4860 ?
000000000 ? 000000001 ?
kdqs_new_page_cb()+ call kdqs_check_page() 006618E20 ? 7FBFFF5D10 ?
40 7FBFFF5E00 ? 7FBFFF4860 ?
000000000 ? 000000001 ?
ktspfpblk()+395 call kdqs_new_page_cb() 7FBFFF5D10 ? 7FBFFF56F0 ?
7FBFFF5E00 ? 7FBFFF4860 ?
000000000 ? 000000001 ?
根据错误信息的描述，怀疑问题可能和HASHKEYS设置过小有关：
SQL> DROP CLUSTER C_HASH_SORT INCLUDING TABLES;
Cluster dropped.
SQL> CREATE CLUSTER C_HASH_SORT
2 (TYPE VARCHAR2(19), CREATED DATE SORT)
3 HASHKEYS 70000 SIZE 100;
Cluster created.
SQL> CREATE TABLE T_HASH_SORT
2 (OBJECT_ID, OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, STATUS)
3 CLUSTER C_HASH_SORT (OBJECT_TYPE, CREATED)
4 AS SELECT OBJECT_ID, OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, STATUS
5 FROM DBA_OBJECTS;
Table created.
SQL> SELECT COUNT(*) FROM DBA_OBJECTS;
COUNT(*)
----------
71466
SQL> SELECT COUNT(*) FROM T_HASH_SORT;
COUNT(*)
----------
7756
SQL> DELETE T_HASH_SORT;
7756 rows deleted.
SQL> INSERT INTO T_HASH_SORT
2 SELECT OBJECT_ID, OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, STATUS
3 FROM DBA_OBJECTS;
INSERT INTO T_HASH_SORT
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kdqs_get_new_page_reuse_link], [], [], [], [], [], [], []
看来和HASHKEYS的设置没有多大关系，而且由于HASHKEYS设置变大，CREATE TABLE AS SELECT插入的记录数反而减少了。
检查语法后发现CREATE TABLE的时候，没有指定列的SORT熟悉，添加后，10.2.0.3环境错误依旧，倒是10.2.0.1的windows环境，错误信息又发生了变化：
SQL> DROP CLUSTER C_HASH_SORT INCLUDING TABLES;
簇已删除。
SQL> CREATE CLUSTER C_HASH_SORT
2 (TYPE VARCHAR2(19), CREATED DATE SORT)
3 HASHKEYS 5806 SIZE 100;
簇已创建。
SQL> CREATE TABLE T_HASH_SORT
2 (OBJECT_ID, OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED SORT, STATUS)
3 CLUSTER C_HASH_SORT (OBJECT_TYPE, CREATED)
4 AS SELECT OBJECT_ID, OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, STATUS
5 FROM DBA_OBJECTS;
表已创建。
SQL> SELECT COUNT(*) FROM DBA_OBJECTS;
COUNT(*)
----------
71197
SQL> SELECT COUNT(*) FROM T_HASH_SORT;
COUNT(*)
----------
46461
SQL> INSERT INTO T_HASH_SORT
2 SELECT OBJECT_ID, OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, STATUS
3 FROM DBA_OBJECTS;
INSERT INTO T_HASH_SORT
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kdqs_read_link no meta-data], [], [], [], [],
[], [], []
在11g的Solaris上测试，发现和WINDOWS环境最后的错误信息一致：ORA-00600: 内部错误代码, 参数: [kdqs_read_link no meta-data], [], [], [], [], [], [], []。
尝试简化例子：
SQL> DROP CLUSTER C_HASH_SORT INCLUDING TABLES;
Cluster dropped.
SQL> CREATE CLUSTER C_HASH_SORT
2 (TYPE VARCHAR2(19), CREATED DATE SORT)
3 HASHKEYS 5806 SIZE 100;
Cluster created.
SQL> CREATE TABLE T_HASH_SORT
2 (OBJECT_TYPE VARCHAR2(19), CREATED DATE SORT, OBJECT_NAME VARCHAR2(30))
3 CLUSTER C_HASH_SORT (OBJECT_TYPE, CREATED);
Table created.
SQL> INSERT INTO T_HASH_SORT
2 SELECT OBJECT_TYPE, CREATED, OBJECT_NAME
3 FROM DBA_OBJECTS;
这次SQL没有报错，但是INSERT语句执行了超过2个小时，产生了几G的归档后，被我手工中止了。
检查发现这个插入的会话所有等待的时间基本上都与日志有关，基本上不是log file switch completion就是log buffer space。但是观察表空间的占用，却并发现表并没有持续增大，而是保持原始的大小没有变化。
看来是Oracle在处理插入的时候，由于要保证顺序，转移行的位置的过程出现了死循环，导致插入一直无法结束。
而如果将HASH列换成NUMBER类型，则不会出现问题：
SQL> DROP CLUSTER C_HASH_SORT INCLUDING TABLES;
Cluster dropped.
SQL> CREATE CLUSTER C_HASH_SORT
2 (ID NUMBER, CREATED DATE SORT)
3 HASH IS ID HASHKEYS 1000 SIZE 65;
Cluster created.
SQL> CREATE TABLE T_HASH_SORT
2 (ID NUMBER, CREATED DATE SORT, NAME VARCHAR2(30))
3 CLUSTER C_HASH_SORT (ID, CREATED);
Table created.
SQL> INSERT INTO T_HASH_SORT
2 SELECT OBJECT_ID, CREATED, OBJECT_NAME
3 FROM DBA_OBJECTS;
70730 rows created.
SQL> SELECT COUNT(*) FROM DBA_OBJECTS;
COUNT(*)
----------
70730
但是即使是将HASH列设置为NUMBER类型，CREATE TABLE AS SELECT方式创建的表仍然存在问题：
SQL> DROP TABLE T_HASH_SORT;
Table dropped.
SQL> CREATE TABLE T_HASH_SORT
2 (ID, CREATED SORT, NAME)
3 CLUSTER C_HASH_SORT (ID, CREATED)
4 AS SELECT OBJECT_ID, CREATED, OBJECT_NAME
5 FROM DBA_OBJECTS;
Table created.
SQL> SELECT COUNT(*) FROM T_HASH_SORT;
COUNT(*)
----------
0
看来HASH SORT CLUSTER的问题还真不是一点半点，从10gr1推出的新特性，居然在11g中还存在这么多的问题，看来基本上这个功能没有什么人使用。不过Oracle的测试也够差劲的，这么多的问题居然就发布出来了。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/4227/viewspace-608928/ ，如需转载，请注明出处，否则将追究法律责任。
