收集模式统计信息出现ORA-03001错误

今天在做嵌套循环连接测试的时候，收集模式统计信息出现ORA-03001错误。问题重现如下：

SQL> conn test/test
Connected.
SQL> create table drive(id int);

Table created.

SQL> insert into drive select  rownum from all_objects where rownum<=10;

10 rows created.

SQL> commit;

Commit complete.

SQL> create table inner_table as select * from all_objects where rownum<=100000;

Table created.

SQL> exec dbms_stats.gather_schema_stats(user,degree=>2);
BEGIN dbms_stats.gather_schema_stats(user,degree=>2); END;

*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 13591
ORA-06512: at "SYS.DBMS_STATS", line 13937
ORA-06512: at "SYS.DBMS_STATS", line 14015
ORA-06512: at "SYS.DBMS_STATS", line 13974
ORA-06512: at line 1

SQL> ! oerr ora 3001
03001, 00000, "unimplemented feature"
// *Cause:  This feature is not implemented.
// *Action:  None.

但看错误，根本无从下手。

考虑启用errorstack的跟踪来找到出现问题的SQL语句。

SQL> alter session set events '3001 trace name errorstack level 3';

Session altered.

SQL> exec dbms_stats.gather_schema_stats(user);
BEGIN dbms_stats.gather_schema_stats(user); END;

*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 13591
ORA-06512: at "SYS.DBMS_STATS", line 13937
ORA-06512: at "SYS.DBMS_STATS", line 14015
ORA-06512: at "SYS.DBMS_STATS", line 13974
ORA-06512: at line 1

在跟踪文件里可以看到如下信息：

Wed Aug 18 15:27:48 2010
Errors in file /u01/oracle/admin/uatdb2/udump/uatdb2_ora_18481228.trc:
ORA-03001: unimplemented feature

打开跟踪文件：

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
ORACLE_HOME = /u01/oracle/product/db10gr2
System name:    AIX
Node name:      SXZYXDB002
Release:        3
Version:        5
Machine:        00C79F504C00
Instance name: uatdb2
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 18481228, image: (TNS V1-V3)

*** 2010-08-18 15:25:20.399
*** ACTION NAME:() 2010-08-18 15:25:20.394
*** MODULE NAME:(SQL*Plus) 2010-08-18 15:25:20.394
*** SERVICE NAME:(SYS$USERS) 2010-08-18 15:25:20.394
*** SESSION ID:(1066.21407) 2010-08-18 15:25:20.394
KGX cleanup...
KGX Atomic Operation Log 7000004ed16f518
 Mutex 7000004ed3e5ee8(1066, 0) idn 0 oper EXAM
 Cursor Parent uid 1066 efd 5 whr 26 slp 0
 oper=DEFAULT pt1=1105a58f0 pt2=1105a54e8 pt3=1104af6a8
 pt4=0 u41=0 stt=0
*** 2010-08-18 15:27:48.552
ksedmp: internal or fatal error
ORA-03001: unimplemented feature
Current SQL statement for this session:
select /*+ parallel_index(t,"ABC_IDX",2) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index_ffs(t,"ABC_IDX") */
count(*) as nrw,count(distinct sys_op_lbid(60478,'L',t.rowid)) as nlb,count(distinct hextoraw(sys_op_descend(1)||sys_op_descend("OBJECT_NAME"))) as ndk,
sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "TEST"."ABC" t where 1 is not null or "OBJECT_NAME" is not null
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
7000004f38a8a20      9598  package body SYS.DBMS_STATS
7000004f38a8a20     10157  package body SYS.DBMS_STATS
7000004f38a8a20     10792  package body SYS.DBMS_STATS
7000004f38a8a20     13408  package body SYS.DBMS_STATS
7000004f38a8a20     13546  package body SYS.DBMS_STATS
。。。。。。。。。。。。。。
。。。。。。。。。。。。。。。。

可以看到ORACLE在执行到下面这条SQL语句报错了
select /*+ parallel_index(t,"ABC_IDX",2) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index_ffs(t,"ABC_IDX") */
count(*) as nrw,count(distinct sys_op_lbid(60478,'L',t.rowid)) as nlb,count(distinct hextoraw(sys_op_descend(1)||sys_op_descend("OBJECT_NAME"))) as ndk,
sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "TEST"."ABC" t where 1 is not null or "OBJECT_NAME" is not null

这条SQL语句是收集索引ABC_IDX的统计信息。nrw 相当于user_indexes.num_rows,nlb相当于user_indexes.leaf_blocks,ndk=user_indexes.num_distinct,clf=clustering_factor

单独拿出来在SQLPLUS下执行了一下 ，也报错。
SQL> select /*+ parallel_index(t,"ABC_IDX",2) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index_ffs(t,"ABC_IDX") */
  2  count(*) as nrw,count(distinct sys_op_lbid(60478,'L',t.rowid)) as nlb,count(distinct hextoraw(sys_op_descend(1)||sys_op_descend("OBJECT_NAME"))) as ndk,
  3  sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "TEST"."ABC" t where 1 is not null or "OBJECT_NAME" is not null;
select /*+ parallel_index(t,"ABC_IDX",2) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index_ffs(t,"ABC_IDX") */
*
ERROR at line 1:
ORA-03001: unimplemented feature

查看这一下这个索引的信息：

SQL> SELECT INDEX_NAME,INDEX_TYPE FROM USER_INDEXES WHERE INDEX_NAME='ABC_IDX';

INDEX_NAME           INDEX_TYPE
-------------------- --------------------
ABC_IDX              FUNCTION-BASED NORMAL

SQL> COL COLUMN_EXPRESSION FORMAT A20
SQL> SELECT INDEX_NAME,COLUMN_EXPRESSION,COLUMN_POSITION FROM USER_IND_EXPRESSIONS WHERE INDEX_NAME='ABC_IDX';

INDEX_NAME           COLUMN_EXPRESSION    COLUMN_POSITION
-------------------- -------------------- ---------------
ABC_IDX              1                                  1

SQL> SELECT COLUMN_NAME,COLUMN_POSITION FROM USER_IND_COLUMNS WHERE INDEX_NAME='ABC_IDX';

COLUMN_NAME          COLUMN_POSITION
-------------------- ---------------
SYS_NC00014$                       1
OBJECT_NAME                        2

这是我当时建立的一个含有常数的索引，第一个列是1，第二个列是OBJECT_NAME。ORACLE给列1起来一个SYS_NC00014$ 的列名。而且在USER_IND_EXPRESSIONS 的COLUMN_EXPRESSION 只记录了一个1。
怀疑ORACLE在处理这样的索引的时候出现问题了。
删掉这个索引，在收集一下看看：

SQL> DROP INDEX ABC_IDX;

Index dropped.

SQL>  exec dbms_stats.gather_schema_stats(user);

PL/SQL procedure successfully completed.

可以看到此成功执行。

在此尝试建立常数索引看看：

SQL> create index abc_idx on abc(1,object_name);

Index created.

SQL> SELECT INDEX_NAME,INDEX_TYPE FROM USER_INDEXES WHERE INDEX_NAME='ABC_IDX';

INDEX_NAME           INDEX_TYPE
-------------------- ---------------------
ABC_IDX              FUNCTION-BASED NORMAL

SQL> SELECT INDEX_NAME,COLUMN_EXPRESSION,COLUMN_POSITION FROM USER_IND_EXPRESSIONS WHERE INDEX_NAME='ABC_IDX';

INDEX_NAME           COLUMN_EXPRESSION    COLUMN_POSITION
-------------------- -------------------- ---------------
ABC_IDX              1                                  1

SQL> SELECT COLUMN_NAME,COLUMN_POSITION FROM USER_IND_COLUMNS WHERE INDEX_NAME='ABC_IDX';

COLUMN_NAME          COLUMN_POSITION
-------------------- ---------------
SYS_NC00014$                       1
OBJECT_NAME                        2

SQL> exec dbms_stats.gather_schema_stats(user);
BEGIN dbms_stats.gather_schema_stats(user); END;

*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 13591
ORA-06512: at "SYS.DBMS_STATS", line 13937
ORA-06512: at "SYS.DBMS_STATS", line 14015
ORA-06512: at "SYS.DBMS_STATS", line 13974
ORA-06512: at line 1

看了就是这个索引的问题了。单独收集这个索引也是有问题的：

SQL> exec dbms_stats.gather_index_stats(user,'ABC_IDX');
BEGIN dbms_stats.gather_index_stats(user,'ABC_IDX'); END;

*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 10872
ORA-06512: at "SYS.DBMS_STATS", line 10896
ORA-06512: at line 1

确实ORACLE在收集常数索引会有问题，不知道是不是个BUG。
SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production