ORA-03001: unimplemented feature 错误处理

ORA-03001: unimplemented feature错误处理

今天，邮箱收到生产库alert错误日志，再次证明我的alert日志监控脚本还是很好用的。

环境：
oracle Database 11g Enterprise Edition Release 11.1.0.6.0
System name:    Linux
Release:        2.6.18-238.19.1.el5xen


-----------------------alert log-----------------------------
Sun Dec 02 22:01:41 2012
GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file /var/u01/app/oracle/diag/rdbms/ora001/xxxx/trace/down_j000_17028.trc:
ORA-03001: unimplemented feature
------------------------------------------------------------------

vim  /var/u01/app/oracle/diag/rdbms/ora001/xxxx/trace/down_j000_17028.trc:
--------------------------------trace file-----------------------------------
.......省略无用信息..........
ORA-03001: unimplemented feature

*** 2012-12-02 22:01:41.172
GATHER_STATS_JOB: GATHER_TABLE_STATS('"LOAD"','"ANONYMOUSUSER_GAME_PHONE"','""', ...)
ORA-03001: unimplemented feature
------------------------------------------------------------------

1.通过alert日志和trace文件，基本断定是自动收集统计信息时出现的错误。

手动收集ANONYMOUSUSER_GAME_PHONE表的统计信息，报同样的错误。
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('"LOAD"','"ANONYMOUSUSER_GAME_PHONE"');
BEGIN DBMS_STATS.GATHER_TABLE_STATS('"LOAD"','"ANONYMOUSUSER_GAME_PHONE"'); END;

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


2.收集ANONYMOUSUSER_GAME_PHONE的统计信息过程中，是什么造成出错呢？
利用3001 event获得错误换因
SQL> alter session set events '3001 trace name ERRORSTACK level 3';  --errorstack追踪出问题的SQL语句。

Session altered.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('"LOAD"','"ANONYMOUSUSER_GAME_PHONE"');
BEGIN DBMS_STATS.GATHER_TABLE_STATS('"LOAD"','"ANONYMOUSUSER_GAME_PHONE"'); END;

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


3.找出trace文件
SQL> SELECT      d.VALUE
         || '/'
         || LOWER (RTRIM (i.INSTANCE, CHR (0)))
         || '_ora_'
         || p.spid
         || '.trc'
            AS "trace_file_name"
  FROM   (SELECT   p.spid
            FROM   v$mystat m, v$session s, v$process p
           WHERE   m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
         (SELECT   t.INSTANCE
            FROM   v$thread t, v$parameter v
           WHERE   v.NAME = 'thread'
             AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
         (SELECT   VALUE FROM   v$parameter WHERE   NAME = 'user_dump_dest') d;  2    3    4    5    6    7    8    9   10   11   12   13   14   15  

trace_file_name
--------------------------------------------------------------------------------
/var/u01/app/oracle/diag/rdbms/ora001/xxxx/trace/down_ora_29468.trc



vim /var/u01/app/oracle/diag/rdbms/ora001/xxxx/trace/down_ora_29468.trc
---------------------------------------
----- Error Stack Dump -----
ORA-03001: unimplemented feature
----- Current SQL Statement for this session (sql_id=8hrxz5wm16svn) -----
select /*+ no_parallel_index(t, "IND_ANON_GAME_PHONE_MD5ID") dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index_ffs(t,"IND_ANON_GAME_PHONE_MD5ID") */ count(*) as nrw,count(distinct sys_op_lbid(228868,'L',t.rowid)) as nlb,count(distinct hextoraw(sys_op_descend("MD5ID")||sys_op_descend(1))) as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "LOAD"."ANONYMOUSUSER_GAME_PHONE" sample block (  2.9579513853,1)  t where "MD5ID" is not null or 1 is not null
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x3ee81c648     12114  package body SYS.DBMS_STATS
-------------------------------------------------------------------------------------------------


单独运行问题SQL，报同样的错误。
SQL> select /*+ no_parallel_index(t, "IND_ANON_GAME_PHONE_MD5ID") dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index_ffs(t,"IND_ANON_GAME_PHONE_MD5ID") */ count(*) as nrw,count(distinct sys_op_lbid(228868,'L',t.rowid)) as nlb,count(distinct hextoraw(sys_op_descend("MD5ID")||sys_op_descend(1))) as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "LOAD"."ANONYMOUSUSER_GAME_PHONE" sample block (  2.9579513853,1)  t where "MD5ID" is not null or 1 is not null;
select /*+ no_parallel_index(t, "IND_ANON_GAME_PHONE_MD5ID") dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index_ffs(t,"IND_ANON_GAME_PHONE_MD5ID") */ count(*) as nrw,count(distinct sys_op_lbid(228868,'L',t.rowid)) as nlb,count(distinct hextoraw(sys_op_descend("MD5ID")||sys_op_descend(1))) as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "LOAD"."ANONYMOUSUSER_GAME_PHONE" sample block (  2.9579513853,1)  t where "MD5ID" is not null or 1 is not null
*
ERROR at line 1:
ORA-03001: unimplemented feature

查看IND_ANON_GAME_PHONE_MD5ID 创建语句，它是个函数索引，md5id列有空值，为了避免查null值不走索引，所以在("MD5ID",1)上建成函数索引
CREATE INDEX "LOAD"."IND_ANON_GAME_PHONE_MD5ID" ON "LOAD"."ANONYMOUSUSER_GAME_PHONE"
  (
    "MD5ID",
    1
  )

4.删除索引IND_ANON_GAME_PHONE_MD5ID，再次收集统计信息
SQL> drop index IND_ANON_GAME_PHONE_MD5ID;

收集统计信息，不报错，确定问题出在函数索引IND_ANON_GAME_PHONE_MD5ID上
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('"LOAD"','"ANONYMOUSUSER_GAME_PHONE"');

PL/SQL procedure successfully completed.


解决办法：
SQL> CREATE INDEX "LOAD"."IND_ANON_GAME_PHONE_MD5ID" ON "LOAD"."ANONYMOUSUSER_GAME_PHONE"(MD5ID,'1' );

Index created.

再次收集统计信息，不再报错
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('"LOAD"','"ANONYMOUSUSER_GAME_PHONE"');

PL/SQL procedure successfully completed.


参考:
MOS |ORA-03001: Unimplemented Feature when Running DBMS_STATS.GATHER_INDEX_STATS [ID 559389.1]