ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 插入排序哈希CLUSTER表出现ORA-7445错误

插入排序哈希CLUSTER表出现ORA-7445错误

原创 Linux操作系统 作者:yangtingkun 时间:2009-07-12 20:12:39 0 删除 编辑

第一次测试排序哈希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.1windows环境,错误信息又发生了变化:

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], [], [], [], [],
[], [], []

11gSolaris上测试,发现和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博客 ” ,链接:http://blog.itpub.net/4227/viewspace-608928/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10405232