ORA-03001,GATHER_TABLE_STATS数据库自动收集统计信息报错
1.根据Alert报错信息,查询Trace日志
/oracle/app/oracle/admin/fgsquery/bdump/fgsquery_j001_11111.trc Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /oracle/app/oracle/product/10.2.0/db_1 System name: Linux Node name: pquerydb01 Release: 2.6.18-348.el5 Version: #1 SMP Wed Nov 28 21:22:00 EST 2012 Machine: x86_64 Instance name: fgsquery Redo thread mounted by this instance: 1 Oracle process number: 117 Unix process pid: 11111, image: oracle@pquerydb01 (J001) *** 2018-10-30 22:04:43.786 *** ACTION NAME:(GATHER_STATS_JOB) 2018-10-30 22:04:43.781 *** MODULE NAME:(DBMS_SCHEDULER) 2018-10-30 22:04:43.781 *** SERVICE NAME:(SYS$USERS) 2018-10-30 22:04:43.781 *** SESSION ID:(1172.38268) 2018-10-30 22:04:43.781 ORA-03001: unimplemented feature *** 2018-10-30 22:04:43.786 GATHER_STATS_JOB: GATHER_TABLE_STATS('"LISPRDD"','"LCCONT"','""', ...) ORA-03001: unimplemented feature
数据库自动收集统计信息也会报错?
表是否有异常,表相关的索引是否有异常???
2.文档搜索
由于在生产环境,并未直接手工收集统计信息进行测试
根据报错,匹配到了一个Mos文档
ORA-03001: Unimplemented Feature when Running DBMS_STATS.GATHER_INDEX_STATS (文档 ID 559389.1)
3.问题分析
测试环境进行测试 --导入测试对象 impdp \'/ as sysdba\' directory=dump dumpfile=LISPRDD_LCCONT.dmp logfile=LISPRDD_LCCONT.log remap_schema=LISPRDD:hr REMAP_TABLESPACE=LISPRD1:users --收集统计信息,未报错~ EXEC dbms_stats.gather_table_stats(ownname=>'HR',TABNAME=>'LCCONT',CASCADE=>TRUE,DEGREE=>1,ESTIMATE_PERCENT=>0.01); --根据sqlfile查询创建索引语句,发现存在两个特殊的索引,一个为复合索引且第二列为数字类型,复合MOS报错信息,另一个为函数索引,都列举出来 impdp \'/ as sysdba\' directory=dump dumpfile=LISPRDD_LCCONT.dmp logfile=LISPRDD_LCCONT.log remap_schema=LISPRDD:hr REMAP_TABLESPACE=LISPRD1:users sqlfile=create_table_index.sql -- new object type path is: SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX -- CONNECT HR CREATE INDEX "HR"."LCCONT_INDEX_VDATE1" ON "HR"."LCCONT" ("VISITDATE", 1) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" PARALLEL 1 ; ALTER INDEX "HR"."LCCONT_INDEX_VDATE1" NOPARALLEL;
--如果vis= null
--visitdate is null -> 全表扫描
CREATE INDEX "HR"."IDX_LCCONT_SUBSTATE" ON "HR"."LCCONT" (SUBSTR("STATE",1,4))
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" PARALLEL 1 ;
ALTER INDEX "HR"."IDX_LCCONT_SUBSTATE" NOPARALLEL;
4.测试验证
--insert 测试记录 禁用所有约束 SQL> select 'alter table '||owner||'.'||TABLE_NAME||' drop constraints '||CONSTRAINT_NAME ||';' from dba_constraints where owner='HR' and TABLE_NAME='LCCONT'; 'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'DROPCONSTRAINTS'||CONSTRAINT_NAME||';' -------------------------------------------------------------------------------- alter table HR.LCCONT drop constraints SYS_C005407; insert into HR.LCCONT(GRPCONTNO,VISITDATE,STATE) values(1,sysdate,1); SQL> EXEC dbms_stats.gather_table_stats(ownname=>'HR',TABNAME=>'LCCONT',CASCADE=>TRUE,DEGREE=>1,ESTIMATE_PERCENT=>0.01); PL/SQL procedure successfully completed. GATHER_INDEX_STATS EXEC DBMS_STATS.gather_index_stats('HR','LCCONT_INDEX_VDATE1'); EXEC DBMS_STATS.gather_index_stats('HR','IDX_LCCONT_SUBSTATE'); 10205 环境没有任何问题,MOS 文档介绍该报错影响范围10201-10204,但是往往有时候版本不一定准确,但是本次是准确的,如果有相同服务器版本测试尽量严格保持一致 10204环境再次进行测试,无数据,收集统计信息未报错,存在数据后,收集统计信息报错
在10204测试环境,重演上述操作,禁用约束
--插入测试数据
SQL> insert into HR.LCCONT(GRPCONTNO,VISITDATE,STATE) select rownum,sysdate-rownum,mod(rownum,3) from dba_objects;
50664 rows created.
SQL> insert into HR.LCCONT(GRPCONTNO,VISITDATE,STATE) values(966,'','abc');
SQL> insert into HR.LCCONT(GRPCONTNO,VISITDATE,STATE) values(889,'','efg');
SQL> commit;
--收集表的统计信息报错如下 SQL> EXEC DBMS_STATS.gather_index_stats('HR','LCCONT_INDEX_VDATE1'); BEGIN DBMS_STATS.gather_index_stats('HR','LCCONT_INDEX_VDATE1'); 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 --收集函数索引,无报错,排除函数索引的问题 SQL> EXEC DBMS_STATS.gather_index_stats('HR','IDX_LCCONT_SUBSTATE'); PL/SQL procedure successfully completed.
--收集复合索引统计信息,报错相同 SQL> EXEC dbms_stats.gather_table_stats(ownname=>'HR',TABNAME=>'LCCONT',CASCADE=>TRUE,DEGREE=>1,ESTIMATE_PERCENT=>0.01); BEGIN dbms_stats.gather_table_stats(ownname=>'HR',TABNAME=>'LCCONT',CASCADE=>TRUE,DEGREE=>1,ESTIMATE_PERCENT=>0.01); END; * ERROR at line 1: ORA-03001: unimplemented feature ORA-06512: at "SYS.DBMS_STATS", line 13437 ORA-06512: at "SYS.DBMS_STATS", line 13457 ORA-06512: at line 1 --查询会话进程号 SQL> select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat group by sid)); SPID ------------ 25471 --查询进程对应的trace文件 ksedmp: internal or fatal error ORA-03001: unimplemented feature Current SQL statement for this session: select /*+ no_parallel_index(t,"LCCONT_INDEX_VDATE1") dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index(t,"LCCONT_INDEX_VDATE1")
*/ count(*) as nrw,count(distinct sys_op_lbid(53035,'L',t.rowid)) as nlb,
count(distinct hextoraw(sys_op_descend("VISITDATE")||sys_op_descend(1))) as
ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "HR"."LCCONT" t where "VISITDATE" is not null or 1 is not null ----- PL/SQL Call Stack ----- object line object handle number name 0xbe7c11b8 9598 package body SYS.DBMS_STATS 0xbe7c11b8 10157 package body SYS.DBMS_STATS 0xbe7c11b8 10792 package body SYS.DBMS_STATS 0xbe7c11b8 10896 package body SYS.DBMS_STATS 0xb3da0860 1 anonymous block --再次对会话进行追踪,本次只收集索引的统计信息,报错相同,无异常 SQL> alter session set events '3001 trace name ERRORSTACK level 3'; Session altered. SQL> EXEC dbms_stats.gather_table_stats(ownname=>'HR',TABNAME=>'LCCONT',CASCADE=>TRUE,DEGREE=>1,ESTIMATE_PERCENT=>0.01); BEGIN dbms_stats.gather_table_stats(ownname=>'HR',TABNAME=>'LCCONT',CASCADE=>TRUE,DEGREE=>1,ESTIMATE_PERCENT=>0.01); END; * ERROR at line 1: ORA-03001: unimplemented feature ORA-06512: at "SYS.DBMS_STATS", line 13437 ORA-06512: at "SYS.DBMS_STATS", line 13457 ORA-06512: at line 1 SQL> select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat group by sid)); SPID ------------ 25676
5.问题处理
按照MOS的文档,对该索引删除后,新建的索引第二列修改为字符格式,本次测试验证,修改为字符格式后,该索引的使用是否与修改前相同,不会造成性能问题。
此索引的创建意义,在于如果查询is null,通过索引能够取得结果 SQL> select count(*) from HR.LCCONT; COUNT(*) ---------- 50667 Execution Plan ---------------------------------------------------------- Plan hash value: 4114441950 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 0 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FULL SCAN| LCCONT_INDEX_VDATE1 | 1 | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------- --查询is null 是否走索引 SQL> select count(*) from HR.LCCONT where VISITDATE is null; COUNT(*) ---------- 2 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 0 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | INDEX RANGE SCAN| LCCONT_INDEX_VDATE1 | 1 | 8 | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------- SQL> select * from HR.LCCONT where VISITDATE is null -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 36 |0 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| LCCONT | 1 | 36 |0 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | LCCONT_INDEX_VDATE1 | 1 | |0 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("VISITDATE" IS NULL) SQL>select * from HR.LCCONT where VISITDATE =sysdate; -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 36 |0 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| LCCONT | 1 | 36 |0 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | LCCONT_INDEX_VDATE1 | 1 | |0 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("VISITDATE"=SYSDATE@!)
*******************************索引修改前,上述SQL均使用了索引***********************************
--对索引进行重建 SQL> drop index hr.LCCONT_INDEX_VDATE1; CREATE INDEX "HR"."LCCONT_INDEX_VDATE1" ON "HR"."LCCONT" ("VISITDATE",'1') PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" PARALLEL 1 ; -- 再次收集统计信息: -- SQL> EXEC DBMS_STATS.gather_index_stats('HR','LCCONT_INDEX_VDATE1'); PL/SQL procedure successfully completed. SQL> EXEC dbms_stats.gather_table_stats(ownname=>'HR',TABNAME=>'LCCONT',CASCADE=>TRUE,DEGREE=>1,ESTIMATE_PERCENT=>0.01); PL/SQL procedure successfully completed.
--报错可以解决
--校验重建后的索引,是否正常使用
SQL> select count(*) from HR.LCCONT
COUNT(*)
----------
50667
Execution Plan
----------------------------------------------------------
Plan hash value: 2339219198
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| LCCONT_INDEX_VDATE1 | 50550 | 34 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
SQL> select count(*) from HR.LCCONT where VISITDATE is null;
COUNT(*)
----------
2
Execution Plan
----------------------------------------------------------
Plan hash value: 2244137702
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX RANGE SCAN| LCCONT_INDEX_VDATE1 | 1 | 8 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("VISITDATE" IS NULL)
SQL> select * from HR.LCCONT where VISITDATE is null
Execution Plan
----------------------------------------------------------
Plan hash value: 3258936949
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LCCONT | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LCCONT_INDEX_VDATE1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("VISITDATE" IS NULL)
SQL> select * from HR.LCCONT where VISITDATE =sysdate;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3258936949
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LCCONT | 1 | 38 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LCCONT_INDEX_VDATE1 | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("VISITDATE"=SYSDATE@!)
结论:1.对索引drop 后,重建索引,
CREATE INDEX "HR"."LCCONT_INDEX_VDATE1" ON "HR"."LCCONT" ("VISITDATE",'1')
2.重建索引并不会影像sql的执行选择,从原理上说,此类索引的目的是让索引能存储单列的Null值,使得业务Is null能够使用此索引,至于第二列的数值类型并不关心