mysql hextoraw_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]