今天,邮箱收到生产库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]